turbot/docker_compliance

Query: exec_ownership_root_root_tls_ca_certificate

Usage

powerpipe query docker_compliance.query.exec_ownership_root_root_tls_ca_certificate

Steampipe Tables

SQL

with os_output as (
select
btrim(stdout_output, E ' \n\r\t') as os,
_ctx ->> 'connection_name' as os_conn
from
exec_command
where
command = 'uname -s'
),
hostname as (
select
btrim(stdout_output, E ' \n\r\t') as host,
_ctx ->> 'connection_name' as host_conn,
_ctx
from
exec_command
where
command = 'hostname'
),
linux_output as (
with json_value_cte as (
select
-- Use the colon as a "true" command that returns an empty string
case
when stdout_output = ''
or (stdout_output :: jsonb ->> 'tlscacert') is null then ':'
else 'stat -c %U:%G ' || (stdout_output :: jsonb ->> 'tlscacert') || ' | grep root:root'
end as key_value,
_ctx ->> 'connection_name' as os_conn
from
exec_command,
os_output
where
os_conn = _ctx ->> 'connection_name'
and command = 'cat /etc/docker/daemon.json'
order by
key_value
)
select
stdout_output,
_ctx ->> 'connection_name' as conn
from
json_value_cte as a
join exec_command on command = a.key_value
where
os_conn = _ctx ->> 'connection_name'
)
select
host as resource,
case
when os.os ilike '%Darwin%' then 'skip'
when o.stdout_output = '' then 'skip'
when btrim(o.stdout_output, E '\r\n\t') = 'root:root' then 'ok'
else 'alarm'
end as status,
case
when os.os ilike '%Darwin%' then host || ' /etc/docker/daemon.json does not exist on ' || os.os || ' OS.'
when o.stdout_output = '' then host || ' TLS CA certificate file does not exist.'
when btrim(o.stdout_output, E '\r\n\t') = 'root:root' then host || ' TLS CA certificate file ownership is set to root:root.'
else host || ' TLS CA certificate file ownership is set to ' || (btrim(o.stdout_output, E ' \n\r\t')) || '.'
end as reason,
h._ctx ->> 'connection_name' as connection_name
from
hostname as h,
os_output as os,
linux_output as o
where
os.os_conn = h.host_conn
and h.host_conn = o.conn;

Controls

The query is being used by the following controls: