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

powerpipe control run aws_perimeter.control.ram_resource_shared_with_trusted_organization_units

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

with ram_shared_resources as (
  select distinct
    rsa.associated_entity as "shared_resource",
    rpa.associated_entity as "shared_with_organization_unit",
    rsa.status,
    rsa.region,
    rsa.account_id,
    rsa._ctx,
    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 '%:ou'
),
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_unit, '/', 3), ','), ',', '')) - ($1)::text[] as untrusted_organizations_unit,
    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_unit) > 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when jsonb_array_length(untrusted_organizations_unit) > 0 then
      resource ||
      case
        when jsonb_array_length(untrusted_organizations_unit) > 2 then
          concat( ' shared with untrusted OUs ' ,untrusted_organizations_unit #>> '{0}', ', ', untrusted_organizations_unit #>> '{1}', ' and ', (jsonb_array_length(untrusted_organizations_unit) - 2)::text, ' more.')
        when jsonb_array_length(untrusted_organizations_unit) = 2 then concat(' shared with untrusted OUs ', untrusted_organizations_unit #>> '{0}', ', ', untrusted_organizations_unit #>> '{1}', '.')
        else concat(' shared with untrusted OU ', untrusted_organizations_unit #>> '{0}', '.')
      end
    else resource || ' shared with trusted OU(s).'
  end as reason
  , region, account_id
from
  shared_data;