Control: Resources shared through RAM should only be shared with trusted accounts

powerpipe control run aws_perimeter.control.ram_resource_shared_with_trusted_accounts

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

with ram_shared_resources as (
  select
    distinct rsa.associated_entity as "shared_resource",
    rpa.associated_entity as "shared_with_principal",
    rsa.status,
    rsa.region,
    rsa.account_id,
    rsa._ctx
  from
    aws_ram_resource_association as rsa
    inner join aws_ram_principal_association as rpa on rsa.resource_share_name = rpa.resource_share_name
  where
    rsa.status <> 'FAILED'
    and rpa.status <> 'FAILED'
    and rpa.associated_entity ~ '^[0-9]+$'
),
shared_data as (
  select
    (regexp_split_to_array(shared_resource, ':')) [ 6 ] as resource,
    to_jsonb(
      string_to_array(string_agg(shared_with_principal, ','), ',', '')
    ) - ($1) :: text [ ] as untrusted_accounts,
    region,
    account_id,
    _ctx
  FROM
    ram_shared_resources
  group by
    shared_resource,
    region,
    account_id,
    _ctx
)
select
  resource,
  case
    when jsonb_array_length(untrusted_accounts) > 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when jsonb_array_length(untrusted_accounts) > 0 then resource || 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 resource || ' shared with trusted account(s).'
  end as reason,
  region,
  account_id
from
  shared_data;