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
Start Steampipe in query mode using the following command
.tablesto get a list of tables for different connections. The connection name will be visible at the top of the output
To obtain column names for the tables, you can run
For example, to inspect the columns of the table “digitalocean_droplet”, run .inspect digitalocean_droplet
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