Query: postgres_db_server_allow_access_to_azure_services_disabled

with postgres_db_with_allow_access_to_azure_services as (
  select
    id
  from
    azure_postgresql_server,
    jsonb_array_elements(firewall_rules) as r
  where
    r -> 'FirewallRuleProperties' ->> 'endIpAddress' = '0.0.0.0'
    and r -> 'FirewallRuleProperties' ->> 'startIpAddress' = '0.0.0.0'
)
select
  s.id as resource,
  case
    when a.id is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when a.id is not null then s.title || ' does not restrict access to azure services.'
    else s.title || ' restricts access to azure services.'
  end as reason,
  s.resource_group as resource_group,
  sub.display_name as subscription
from
  azure_postgresql_server as s
  left join postgres_db_with_allow_access_to_azure_services as a on a.id = s.id,
  azure_subscription as sub
where
  sub.subscription_id = s.subscription_id;