turbot/aws_compliance

Query: rds_db_instance_connections_encryption_enabled

Usage

powerpipe query aws_compliance.query.rds_db_instance_connections_encryption_enabled

SQL

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

Controls

The query is being used by the following controls: