Query: exec_ownership_root_root_docker_server_certificate

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->>'tlscert') || ' | grep -v 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 || ' server certificate file ownership is set to root:root.'
    else host || ' server 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;