select * from shell;

Steampipe blurs the boundaries between code and data, to powerful effect. The exec plugin continues that theme. Use it to write SQL queries that run commands on local or remote hosts, returning results as one or more rows. We'll look at a few basic examples, then show how we use the plugin to enable the new Docker Compliance mod.

From Steampipe to your local shell and back again

If you install the plugin and do nothing else, this becomes possible.

select line from exec_command_line where command = 'uname -a'
+-------------------------------------------------------------------------------------------------------------------+
| line                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------+
| Linux Indigo 5.15.90.1-microsoft-standard-WSL2 #1 SMP Fri Jan 27 02:56:13 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux |
+-------------------------------------------------------------------------------------------------------------------+

Multiple lines produce multiple rows.

select line from exec_command_line where command = 'ls -lr --time-style=+"%Y-%m"'
+-----------------------------------------------------------------------------------------+
| line                                                                                    |
+-----------------------------------------------------------------------------------------+
| drwxr-xr-x  6 jon jon     4096 2023-01 steampipe-mod-azure-insights                     |
| drwxr-xr-x  7 jon jon     4096 2023-07 steampipe-plugin-salesforce                      |
| drwxr-xr-x 15 jon jon     4096 2023-05 steampipe-plugin-sdk                             |

You can also use the exec_command table to bring an entire file into a column.

select stdout_output from exec_command where command = 'cat /etc/resolv.conf'

+-------------------------------------------------------------------------------------+
| output                                                                              |
+-------------------------------------------------------------------------------------+
| # This is /run/systemd/resolve/stub-resolv.conf managed by man:systemd-resolved(8). |
| # Do not edit.                                                                      |
| ...                                                                                 |

| nameserver 127.0.0.53                                                               |
| options edns0 trust-ad                                                              |
| search .                                                                            |
+-------------------------------------------------------------------------------------+

Connect to remote Linux or Windows hosts, traversing bastions and proxies

The first example uses the default exec_local connection. Now let's edit exec.spc to add a connection to a remote host.

  connection "exec_local" {
  plugin = "exec"
}

connection "exec_remote_linux" {
  plugin = "exec"
  protocol = "ssh"
  host = "my.host.net"
  user = "jon"
  private_key = "~/.ssh/my.host.net.id_rsa"
}

Now we can query the local and remote connections individually.

select line from exec_local.exec_command_line where command = 'uname -a'
+-------------------------------------------------------------------------------------------------------------------+
| line                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------+
| Linux Indigo 5.15.90.1-microsoft-standard-WSL2 #1 SMP Fri Jan 27 02:56:13 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux |
+-------------------------------------------------------------------------------------------------------------------+

 select line from exec_remote_linux.exec_command_line where command = 'uname -a'
+-------------------------------------------------------------------------------------------------------+
| line                                                                                                  |
+-------------------------------------------------------------------------------------------------------+
| Linux hn 5.15.0-50-generic #56-Ubuntu SMP Tue Sep 20 13:23:26 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux |
+-------------------------------------------------------------------------------------------------------+

Query multiple hosts with aggregators

You could use a SQL JOIN to combine those queries. But there's a much easier way! First create an aggregator.

connection "exec_all" {
  plugin = "exec"
  type = "aggregator"
  connections = ["exec_local", "exec_remote_linux"]
}

Then query it.

select
  _ctx->>'connection_name' as connection, 
  line 
from
  exec_all.exec_command_line
where
  command = 'uname -a'

+-------------------+-------------------------------------------------------------------------------------------------------------------+
| connection        | line                                                                                                              |
+-------------------+-------------------------------------------------------------------------------------------------------------------+
| exec_remote_linux | Linux hn 5.15.0-50-generic #56-Ubuntu SMP Tue Sep 20 13:23:26 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux             |
| exec_local        | Linux Indigo 5.15.90.1-microsoft-standard-WSL2 #1 SMP Fri Jan 27 02:56:13 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux |
+-------------------+-------------------------------------------------------------------------------------------------------------------+

Here are the supported connection modes:

• Remote Linux via SSH

• Remote Windows via WinRM

• Remote Windows connection through WinRM ignoring certificate validation

• Remote Linux connection through SSH with in-file private key

• Remote Linux connection through SSH with password

• Remote Linux connection through SSH via bastion host

• Remote Linux connection through SSH via bastion host over proxy

Analyze results with SQL

Of course SQL is also a great way to aggregate data. Let's ask and answer the question: "How many packages installed per day?"

select 
  _ctx->>'connection_name' as connection, 
  line as package_install_date, 
  count(*)
from
  exec_all.exec_command_line 
where 
  command = 'cat /var/log/dpkg.log | cut -d'' '' -f1'
group by 
  connection, line
order by
  connection, package_install_date

+-------------------+----------------------+-------+
| connection        | package_install_date | count |
+-------------------+----------------------+-------+
| exec_local        | 2023-09-05           | 11    |
| exec_local        | 2023-09-11           | 87    |
| exec_local        | 2023-09-13           | 67    |
| exec_remote_linux | 2023-09-05           | 107   |
| exec_remote_linux | 2023-09-08           | 35    |
| exec_remote_linux | 2023-09-13           | 52    |
+-------------------+----------------------+-------+

Bringing it all together for Docker CIS scanning

Here's the CIS 1.6 benchmark running as a dashboard.

The benchmark uses the Docker plugin for queries against the Docker layer (using tables like docker_container), and also uses the exec plugin to query the OS layer. These queries support 33 controls that use the Docker plugin, and 56 that use the exec plugin.

Let's explore one of the exec-based controls. CIS Docker 1.6 advises that that the Docker sock file permissions should be set to 660 or more restrictively. Here's the definition of a control that checks for that condition.

control "cis_v160_3_16" {
  title         = "3.16 Ensure that the Docker sock file permissions are set to 660 or more restrictively"
  description   = "You should verify that the Docker socket file has permissions of 660 or are configured more restrictively."
  query         = query.docker_sock_file_restrictive_permission

And here is the referenced query.

query "docker_sock_file_restrictive_permission" {
  sql = <<-EOQ
    ${local.os_hostname_sql}
     linux_output as (
      select
        stdout_output,
        _ctx ->> 'connection_name' as conn
      from
        exec_command,
        os_output
      where
        os_conn = _ctx ->> 'connection_name'
        and command = 'stat -c %a /var/run/docker.sock'
    ),
    darwin_output as (
      select
        stdout_output,
        _ctx ->> 'connection_name' as conn
      from
        exec_command,
        os_output
      where
        os_conn = _ctx ->> 'connection_name'
        and os_output.os = 'Darwin'
        and command = 'stat -f %Op /var/run/docker.sock'
    ),
    command_output as (
      select * from darwin_output
      union all
      select * from linux_output
    )
    select
      host as resource,
      case
        when o.stdout_output like '%No such file or directory%' then 'skip'
        when o.stdout_output like '%660%' then 'ok'
        else 'alarm'
      end as status,
      case
        when o.stdout_output like host || '%No such file or directory%' then ' recommendation is not applicable as the file is unavailable.'
        else host || ' docker.socket file permission set to ' || (btrim(o.stdout_output, E' \n\r\t')) || '.'
      end as reason
      ${replace(local.common_dimensions_qualifier_sql, "__QUALIFIER__", "h.")}
    from
      hostname as h,
      os_output as os,
      command_output as o
    where
      os.os_conn = h.host_conn
      and h.host_conn = o.conn;
  EOQ
}

Once you figure out which shell commands to use, it's just the standard Steampipe pattern for a control that returns the required columns resource, status, and reason.

Like all other mods, Docker Compliance is available to reuse and remix. Are there other conditions you'd like to check? If there's a shell command that returns the necessary data, you can easily wrap it in a Steampipe control.

See it in action

What is an API anyway?

Steampipe nominally maps APIs to database tables. When you select * from aws_sns_topic, Steampipe winds up calling the AWS ListTopics API. Many Steampipe plugins work like that: a table corresponds to a specific API call. But some plugins work in a more general way. The CSV plugin opens the door to a universe of dynamic data. The net_http_request table of the net plugin turns Steampipe into an HTTP client. The Terraform and AWS CloudFormation plugins create a SQL facade over IaC config files.

In that tradition, the exec plugin creates a SQL facade over the universe of shell commands. It's a wide-open capability, we know you'll find creative uses for it, and we look forward to hearing about them.