Query: sql_instance_postgresql_pgaudit_database_flag_on

select
  address as resource,
  case
    when jsonb_typeof(attributes_std -> 'settings' -> 'database_flags') = 'object'
    and (
      attributes_std -> 'settings' -> 'database_flags' ->> 'name'
    ) = 'cloudsql.enable_pgaudit'
    and (
      attributes_std -> 'settings' -> 'database_flags' ->> 'value'
    ) = 'on' then 'ok'
    when jsonb_typeof(attributes_std -> 'settings' -> 'database_flags') = 'array'
    and exists(
      select
        1
      from
        jsonb_array_elements(attributes_std -> 'settings' -> 'database_flags') as flags
      where
        (flags ->> 'name') = 'cloudsql.enable_pgaudit'
        and (flags ->> 'value') = 'on'
    ) then 'ok'
    when jsonb_typeof(attributes_std -> 'settings' -> 'database_flags') = 'object'
    and (
      attributes_std -> 'settings' -> 'database_flags' ->> 'name'
    ) = 'cloudsql.enable_pgaudit'
    and (
      attributes_std -> 'settings' -> 'database_flags' ->> 'value'
    ) = 'off' then 'alarm'
    when jsonb_typeof(attributes_std -> 'settings' -> 'database_flags') = 'array'
    and exists(
      select
        1
      from
        jsonb_array_elements(attributes_std -> 'settings' -> 'database_flags') as flags
      where
        (flags ->> 'name') = 'cloudsql.enable_pgaudit'
        and (flags ->> 'value') = 'off'
    ) then 'alarm'
    else 'alarm'
  end as status,
  split_part(address, '.', 2) || case
    when jsonb_typeof(attributes_std -> 'settings' -> 'database_flags') = 'object'
    and (
      attributes_std -> 'settings' -> 'database_flags' ->> 'name'
    ) = 'cloudsql.enable_pgaudit'
    and (
      attributes_std -> 'settings' -> 'database_flags' ->> 'value'
    ) = 'on' then ' pgaudit database flag set to on'
    when jsonb_typeof(attributes_std -> 'settings' -> 'database_flags') = 'array'
    and exists(
      select
        1
      from
        jsonb_array_elements(attributes_std -> 'settings' -> 'database_flags') as flags
      where
        (flags ->> 'name') = 'cloudsql.enable_pgaudit'
        and (flags ->> 'value') = 'on'
    ) then ' pgaudit database flag set to on'
    when jsonb_typeof(attributes_std -> 'settings' -> 'database_flags') = 'object'
    and (
      attributes_std -> 'settings' -> 'database_flags' ->> 'name'
    ) = 'cloudsql.enable_pgaudit'
    and (
      attributes_std -> 'settings' -> 'database_flags' ->> 'value'
    ) = 'off' then ' pgaudit database flag set to off'
    when jsonb_typeof(attributes_std -> 'settings' -> 'database_flags') = 'array'
    and exists(
      select
        1
      from
        jsonb_array_elements(attributes_std -> 'settings' -> 'database_flags') as flags
      where
        (flags ->> 'name') = 'cloudsql.enable_pgaudit'
        and (flags ->> 'value') = 'off'
    ) then ' pgaudit database flag set to off'
    else ' pgaudit database flag not set'
  end || '.' reason,
  path || ':' || start_line
from
  terraform_resource
where
  type = 'google_sql_database_instance'
  and (attributes_std ->> 'database_version') like 'POSTGRES%';