Query: postgresql_server_encrypted_at_rest_using_cmk

with postgresql_server as (
  select
    '${azurerm_postgresql_server.' || name || '.id}' as pg_id,
    *
  from
    terraform_resource
  where
    type = 'azurerm_postgresql_server'
), server_keys as (
  select
    *
  from
    terraform_resource
  where
    type = 'azurerm_postgresql_server_key'
    and (attributes_std -> 'key_vault_key_id') is not null
)
select
  a.address as resource,
  case
    when (s.attributes_std ->> 'server_id') is not null then 'ok'
    else 'alarm'
  end as status,
  split_part(a.address, '.', 2) || case
    when (s.attributes_std ->> 'server_id') is not null then ' encrypted with CMK'
    else ' not encrypted with CMK'
  end || '.' reason
  
  , a.path || ':' || a.start_line
from
  postgresql_server as a
  left join server_keys as s on a.pg_id = ( s.attributes_std ->> 'server_id');