Control: EC2 AMIs should only be shared with trusted accounts

powerpipe control run aws_perimeter.control.ec2_ami_shared_with_trusted_accounts

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

with all_amis as (
  select
    title,
    public,
    launch_permissions,
    region,
    _ctx,
    tags,
    account_id
  from
    aws_ec2_ami
  order by
    account_id,
    _ctx,
    region,
    tags,
    title
),
ami_data as (
  select
    title,
    public,
    string_agg(lp ->> 'Group', ',') as public_access,
    to_jsonb(
      string_to_array(string_agg(lp ->> 'UserId', ','), ',')
    ) as shared_accounts,
    to_jsonb(
      string_to_array(string_agg(lp ->> 'UserId', ','), ',')
    ) - ($1) :: text [ ] as untrusted_accounts,
    region,
    _ctx,
    tags,
    account_id
  from
    all_amis,
    jsonb_array_elements(launch_permissions) lp
  group by
    title,
    public,
    region,
    _ctx,
    account_id,
    tags
),
evaluated_amis as (
  select
    all_amis.*,
    public_access,
    untrusted_accounts,
    shared_accounts
  from
    all_amis
    left join ami_data on all_amis.account_id = ami_data.account_id
    and all_amis.region = ami_data.region
    and all_amis.title = ami_data.title
)
select
  title as resource,
  case
    when public then 'info'
    when shared_accounts is null then 'ok'
    when untrusted_accounts is not null then 'info'
    else 'ok'
  end as status,
  case
    when public then title || ' is public.'
    when shared_accounts is null then title || ' is not shared.'
    when untrusted_accounts is not null 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 title || ' shared with trusted account(s).'
  end as reason,
  region,
  account_id
from
  evaluated_amis;