Query: vpc_peering_connection_route_table_least_privilege

with vpc_peering_routing_tables as (
  select
    r ->> 'VpcPeeringConnectionId' as peering_connection_id
  from
    aws_vpc_route_table,
    jsonb_array_elements(routes) as r
    inner join aws_vpc_peering_connection as c on r ->> 'VpcPeeringConnectionId' = c.id
  where
    ( r ->> 'DestinationCidrBlock' = '0.0.0.0/0'
      or r ->> 'DestinationCidrBlock' = '::/0'
      or (r ->> 'DestinationCidrBlock')::cidr = c.accepter_cidr_block
      or (r ->> 'DestinationCidrBlock')::cidr = c.requester_cidr_block
    )
  group by
  r ->> 'VpcPeeringConnectionId'
)
select
  c.id as resource,
  case
    when t.peering_connection_id is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when t.peering_connection_id is not null then c.title || ' does not have least privilege access.'
    else c.title || ' have least privilege access.'
  end as reason
  
  , region, account_id
from
  aws_vpc_peering_connection as c
  left join vpc_peering_routing_tables as t on t.peering_connection_id = c.id;