Query: lightsail_instance_ssh_restricted_ip

with open_ports as (
  select
    name,
    jsonb_array_elements(networking -> 'Ports') as port
  from
    aws_lightsail_instance
),
port_cidrs as (
  select
    op.name,
    (op.port ->> 'FromPort')::int as from_port,
    (op.port ->> 'ToPort')::int as to_port,
    op.port ->> 'Protocol' as protocol,
    jsonb_array_elements_text(op.port -> 'Cidrs') as cidr
  from
    open_ports op
),
unrestricted_ssh_ports as (
  select
    name
  from
    port_cidrs
  where
    from_port = 22
    and to_port = 22
    and protocol = 'tcp'
    and cidr = '0.0.0.0/0'
)
select
  i.name as resource,
  case
    when usp.name is null then 'ok'
    else 'alarm'
  end as status,
  case
    when usp.name is null then i.name || ' has SSH (22) restricted to specific IP addresses.'
    else i.name || ' has SSH (22) open to the world (0.0.0.0/0).'
  end as reason,
  i.tags
from
  aws_lightsail_instance i
  left join unrestricted_ssh_ports usp on i.name = usp.name;