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

powerpipe control run aws_perimeter.control.ram_resource_shared_with_trusted_organizations

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

with ram_shared_resources as (
  select
    distinct rsa.associated_entity as "shared_resource",
    rpa.associated_entity as "shared_with_organization",
    rsa.status,
    rsa.region,
    rsa._ctx,
    rsa.account_id,
    split_part((rpa.associated_entity), '/', 1)
  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 split_part((rpa.associated_entity), '/', 1) like '%:organization'
),
shared_data as (
  select
    (regexp_split_to_array(shared_resource, ':')) [ 6 ] as resource,
    to_jsonb(
      string_to_array(
        string_agg(split_part(shared_with_organization, '/', 2), ','),
        ',',
        ''
      )
    ) - ($1) :: text [ ] as untrusted_organizations,
    region,
    _ctx,
    account_id
  FROM
    ram_shared_resources
  group by
    shared_resource,
    region,
    _ctx,
    account_id
)
select
  resource,
  case
    when jsonb_array_length(untrusted_organizations) > 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when jsonb_array_length(untrusted_organizations) > 0 then resource || case
      when jsonb_array_length(untrusted_organizations) > 2 then concat(
        ' shared with untrusted organizations ',
        untrusted_organizations #> > '{0}',
        ', ',
        untrusted_organizations #> > '{1}',
        ' and ',
        (jsonb_array_length(untrusted_organizations) - 2) :: text,
        ' more.'
      )
      when jsonb_array_length(untrusted_organizations) = 2 then concat(
        ' shared with untrusted organizations ',
        untrusted_organizations #> > '{0}',
        ' and ',
        untrusted_organizations #> > '{1}',
        '.'
      )
      else concat(
        ' shared with untrusted organization ',
        untrusted_organizations #> > '{0}',
        '.'
      )
    end
    else resource || ' shared with trusted organizationt(s).'
  end as reason,
  region,
  account_id
from
  shared_data;