How to use Steampipe to extract information for DigitalOcean

Introduction

In previous posts, we have seen how to set up Steampipe and configure it to work with DigitalOcean. In this post we will see how we can run specific SQL queries to fetch the information we need.

As we have mentioned earlier, due to its data collection features and the ability to query for specific information, Steampipe can be used to perform security posture evaluation of your cloud infrastructure. However, it is important to remember that Steampipe is not a security tool but a data aggregation software. Any security inferences about missing policies, user, cloud and resource misconfigurations, service privileges, RBAC, Internet exposure, transport security etc. all have to be deduced using custom queries that you need to write.

Let’s take a look at some examples of SQL queries to use to fetch information that we need to draw possible inferences from a security point of view.

Steampipe and DigitalOcean

Here are some examples of queries you can run to find metadata information about resources in the cloud and draw specific security conclusions from them.

Inspect command to understand table structure

1. Start Steampipe in query mode using the following command

   steampipe query

2. Run .tables to get a list of tables for different connections. The connection name will be visible at the top of the output

   

3. To obtain column names for the tables, you can run .inspect {connection}.{table}

4. For example, to inspect the columns of the table “digitalocean_droplet”, run .inspect digitalocean_droplet

   

5. The interface has autocomplete enabled that allows you to easily navigate around

   

Common queries to get you started

• Get a list of all droplets and show id, name of the droplet, public IPv4 address, public IPv6, IDs of any backups, snapshot IDs and enabled features.

  select id,name,public_ipv4,public_ipv6,backup_ids,snapshot_ids,features from digitalocean_droplet;

  

• Get a list of all kubernetes clusters, their names and node pools

  select id,name,endpoint,node_pools from digitalocean_kubernetes_cluster;

  

• Get a list of all load_balancers and their HTTPS/SSL redirect status

  select name,redirect_http_to_https from digitalocean_load_balancer

  

• Get a list of domain names and their respective zone files to identify if SPF or DKIM records have been created or not

  select name, ttl, zone_file from digitalocean_domain

  

• Get a list of all databases, their names and version numbers

  select name, version from digitalocean_database