Control: Ensure that the 'log_disconnections' database flag for Cloud SQL PostgreSQL instance is set to 'on'
Description
Enabling the log_disconnections setting logs the end of each session, including the session duration.
Usage
Run the control in your terminal:
powerpipe control run terraform_gcp_compliance.control.sql_instance_postgresql_log_disconnections_database_flag_on
Snapshot and share results via Turbot Pipes:
powerpipe loginpowerpipe control run terraform_gcp_compliance.control.sql_instance_postgresql_log_disconnections_database_flag_on --share
SQL
This control uses a named query:
select address as resource, case when coalesce(trim((attributes_std ->> 'database_version')), '') = '' then 'alarm' when (attributes_std ->> 'database_version') not like 'POSTGRES%' then 'skip' when ( attributes_std -> 'settings' -> 'database_flags' ->> 'name' ) = 'log_disconnections' and ( attributes_std -> 'settings' -> 'database_flags' ->> 'value' ) = 'on' then 'ok' else 'alarm' end as status, split_part(address, '.', 2) || case when coalesce(trim((attributes_std ->> 'database_version')), '') = '' then ' ''database_version'' is not defined' when (attributes_std ->> 'database_version') not like 'POSTGRES%' then ' not a PostgreSQL database' when (attributes_std -> 'settings') is null then ' ''settings'' is not defined' when (attributes_std -> 'settings' -> 'database_flags') is null then ' ''settings.database_flags'' is not defined' when coalesce( trim( ( attributes_std -> 'settings' -> 'database_flags' ->> 'name' ) ), '' ) = '' then ' ''settings.database_flags.name'' is not defined' when coalesce( trim( ( attributes_std -> 'settings' -> 'database_flags' ->> 'value' ) ), '' ) = '' then ' ''settings.database_flags.value'' is not defined' when ( attributes_std -> 'settings' -> 'database_flags' ->> 'name' ) <> 'log_disconnections' then ' ''log_disconnections'' database flag not set' when ( attributes_std -> 'settings' -> 'database_flags' ->> 'name' ) = 'log_disconnections' and ( attributes_std -> 'settings' -> 'database_flags' ->> 'value' ) = 'on' then ' ''log_disconnections'' database flag set to ''on''' else ' ''log_disconnections'' database flag set to ''off''' end || '.' reason, path || ':' || start_linefrom terraform_resourcewhere type = 'google_sql_database_instance';