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
);