Query: lightsail_instance_rdp_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_rdp_ports as (
  select
    name
  from
    port_cidrs
  where
    from_port = 3389
    and to_port = 3389
    and protocol = 'tcp'
    and cidr = '0.0.0.0/0'
)
select
  i.name as resource,
  case
    when urp.name is null then 'ok'
    else 'alarm'
  end as status,
  case
    when urp.name is null then i.name || ' has RDP (3389) restricted to specific IP addresses.'
    else i.name || ' has RDP (3389) open to the world (0.0.0.0/0).'
  end as reason,
  i.tags
from
  aws_lightsail_instance i
  left join unrestricted_rdp_ports urp on i.name = urp.name;