Control: RDS DB instances connections should be encrypted

powerpipe control run aws_compliance.control.rds_db_instance_connections_encryption_enabled

powerpipe login
powerpipe control run aws_compliance.control.rds_db_instance_connections_encryption_enabled --share

with instance_pg as (
  select
    g ->> 'DBParameterGroupName' as pg_name,
    i.engine,
    i.title,
    i.arn,
    i.tags,
    i.region,
    i.account_id,
    i._ctx
  from
    aws_rds_db_instance as i,
    jsonb_array_elements(db_parameter_groups) as g
), pg_with_ssl_enabled as (
  select
    g.name
  from
    instance_pg as i,
    aws_rds_db_parameter_group as g,
    jsonb_array_elements(parameters) as p
  where
    i.pg_name = g.name
    and g.account_id = i.account_id
    and g.region = i.region
    and p ->> 'ParameterName' = 'rds.force_ssl'
    and p ->> 'ParameterValue' = '1'
)
select
  i.arn as resource,
  i.engine,
  case
    when i.engine not in ('sqlserver', 'postgres') then 'skip'
    when p.name is not null then 'ok'
    else 'alarm'
  end as status,
  case
    when i.engine not in ('sqlserver', 'postgres') then title || ' has ' || engine || ' engine type.'
    when p.name is not null then title || ' connections are SSL encrypted.'
    else title || ' connections are not SSL encrypted.'
  end as reason
  
  , region, account_id
from
  instance_pg as i
  left join pg_with_ssl_enabled as p on p.name = i.pg_name