Query: vpc_gateway_endpoint_restrict_public_access

with wildcard_action_policies as (
  select
    vpc_endpoint_id,
    count(*) as statements_num
  from
    aws_vpc_endpoint,
    jsonb_array_elements(policy_std -> 'Statement') as s
  where
    s ->> 'Effect' = 'Allow'
    and s -> 'Condition' is null
    and (
      (s -> 'Principal' -> 'AWS') = '["*"]'
      or s ->> 'Principal' = '*'
    )
    and s ->> 'Action' = '["*"]'
  group by
    vpc_endpoint_id
)
select
  e.vpc_endpoint_id as resource,
  case
    when e.vpc_endpoint_type <> 'Gateway' then 'skip'
    when p.vpc_endpoint_id is null then 'ok'
    else 'alarm'
  end as status,
  case
    when vpc_endpoint_type <> 'Gateway' then e.title || ' is of ' || e.vpc_endpoint_type || ' endpoint type.'
    when p.vpc_endpoint_id is null then e.title || ' does not allow public access.'
    else title || ' contains ' || coalesce(p.statements_num, 0) || ' statements that allows public access.'
  end as reason,
  e.region,
  e.account_id
from
  aws_vpc_endpoint as e
  left join wildcard_action_policies as p on p.vpc_endpoint_id = e.vpc_endpoint_id;