Control: RDS DB snapshots should only be shared with trusted accounts

powerpipe control run aws_perimeter.control.rds_db_snapshot_shared_with_trusted_accounts

powerpipe login
powerpipe control run aws_perimeter.control.rds_db_snapshot_shared_with_trusted_accounts --share

(with shared_cluster_snapshot_data as (
  select
    arn,
    title,
    (cluster_snapshot ->> 'AttributeValues')::jsonb as shared_accounts,
    (cluster_snapshot ->> 'AttributeValues')::jsonb - ($1)::text[] as untrusted_accounts,
    region,
    _ctx,
    tags,
    account_id
  from
    aws_rds_db_cluster_snapshot,
    jsonb_array_elements(db_cluster_snapshot_attributes) as cluster_snapshot
)
select
  arn as resource,
  case
    when jsonb_array_length(untrusted_accounts) > 0 then 'info'
    else 'ok'
  end status,
  case
    when untrusted_accounts #>> '{0}' = 'all' then title || ' publicly restorable.'
    when jsonb_array_length(untrusted_accounts) > 0 and untrusted_accounts #>> '{0}' != 'all'
    then title ||
  case
    when jsonb_array_length(untrusted_accounts) > 2
    then concat(' shared with untrusted accounts ', untrusted_accounts #>> '{0}', ', ', untrusted_accounts #>> '{1}', ' and ' || (jsonb_array_length(untrusted_accounts) - 2)::text || ' more.' )
    when jsonb_array_length(untrusted_accounts) = 2
    then concat(' shared with untrusted accounts ', untrusted_accounts #>> '{0}', ' and ', untrusted_accounts #>> '{1}', '.')
    else concat(' shared with untrusted account ', untrusted_accounts #>> '{0}', '.')
  end
    else
      case
        when shared_accounts is null then title || ' is not shared.'
        else title || ' shared with trusted account(s).'
      end
  end reason
  
  , region, account_id
from
  shared_cluster_snapshot_data)

union

( with shared_db_snapshot_data as (
  select
    arn,
    title,
    (database_snapshot ->> 'AttributeValues')::jsonb as shared_accounts,
    (database_snapshot ->> 'AttributeValues')::jsonb - ($1)::text[] as untrusted_accounts,
    region,
    _ctx,
    tags,
    account_id
  from
    aws_rds_db_snapshot,
    jsonb_array_elements(db_snapshot_attributes) as database_snapshot
)
select
  arn as resource,
  case
    when jsonb_array_length(untrusted_accounts) > 0 then 'info'
    else 'ok'
  end status,
  case
    when untrusted_accounts #>> '{0}' = 'all'
    then title || ' publicly restorable.'
    when jsonb_array_length(untrusted_accounts) > 0 and untrusted_accounts #>> '{0}' != 'all'
    then title ||
  case
    when jsonb_array_length(untrusted_accounts) > 2
    then concat(' shared with untrusted accounts ', untrusted_accounts #>> '{0}', ', ', untrusted_accounts #>> '{1}', ' and ' || (jsonb_array_length(untrusted_accounts) - 2)::text || ' more.' )
    when jsonb_array_length(untrusted_accounts) = 2
    then concat(' shared with untrusted accounts ', untrusted_accounts #>> '{0}', ' and ', untrusted_accounts #>> '{1}')
    else concat(' shared with untrusted account ', untrusted_accounts #>> '{0}', '.')
  end
    else
      case
        when shared_accounts is null then title || ' is not shared.'
        else title || ' shared with trusted account(s).'
      end
  end reason
  
  , region, account_id
from
  shared_db_snapshot_data);