Query: ecr_repository_prohibit_public_access

with open_access_ecr_repo as(
  select
    distinct arn
  from
    aws_ecr_repository,
    jsonb_array_elements(policy_std -> 'Statement') as s,
    jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p,
    string_to_array(p, ':') as pa,
    jsonb_array_elements_text(s -> 'Action') as a
  where
    s ->> 'Effect' = 'Allow'
    and (p = '*')
)
select
  r.arn as resource,
  case
    when o.arn is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when o.arn is not null then r.title || ' allows public access.'
    else r.title || ' does not allow public access.'
  end as reason,
  r.region,
  r.account_id
from
  aws_ecr_repository as r
  left join open_access_ecr_repo as o on r.arn = o.arn
group by
  resource,
  status,
  reason,
  r.region,
  r.account_id,
  r.tags,
  r._ctx;