Control: GCP SQL PostgreSQL instance should have pgaudit database flag set to 'on'

Description

This control checks whether the pgaudit database flag for Cloud SQL PostgreSQL instance is set to 'on'.

Usage

Run the control in your terminal:

powerpipe control run terraform_gcp_compliance.control.sql_instance_postgresql_pgaudit_database_flag_on

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run terraform_gcp_compliance.control.sql_instance_postgresql_pgaudit_database_flag_on --share

SQL

This control uses a named query:

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%';

Control: GCP SQL PostgreSQL instance should have pgaudit database flag set to 'on'

Description

Usage

SQL

Tags