Control: 6.2.3 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.

PostgreSQL does not log session details such as duration and session end by default. Enabling the log_disconnections setting will create log entries at the end of each session which can be useful in troubleshooting issues and determine any unusual activity across a time period. The log_disconnections and log_connections work hand in hand and generally, the pair would be enabled/disabled together. This recommendation is applicable to PostgreSQL database instances.

Remediation

From Console:

Go to the Cloud SQL Instances page in the Google Cloud Console by visiting https://console.cloud.google.com/sql/instances.
Select the PostgreSQL instance where the database flag needs to be enabled.
Click Edit.
Scroll down to the Flags section.
To set a flag that has not been set on the instance before, click Add item, choose the flag log_disconnections from the drop-down menu and set the value as on.
Click Save.
Confirm the changes under Flags on the Overview page.

From Command Line:

Configure the log_disconnections database flag for every Cloud SQL PosgreSQL database instance using the below command:

gcloud sql instances patch <INSTANCE_NAME> --database-flags log_disconnections=on

Note: This command will overwrite all previously setdatabase flags. To keep those and add new ones, include the values for all flags to be set on the instance; any flag not specifically included is set to its default value. For flags that do not take a value, specify the flag name followed by an equals sign ("=").

Usage

Run the control in your terminal:

powerpipe control run gcp_compliance.control.cis_v130_6_2_3

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run gcp_compliance.control.cis_v130_6_2_3 --share

SQL

This control uses a named query:

select
  self_link resource,
  case
    when database_version not like 'POSTGRES%' then 'skip'
    when database_flags @> '[{"name":"log_disconnections","value":"on"}]' then 'ok'
    else 'alarm'
  end as status,
  case
    when database_version not like 'POSTGRES%'
      then title || ' not a PostgreSQL database.'
    when database_flags is null or not (database_flags @> '[{"name":"log_disconnections"}]')
      then title || ' ''log_disconnections'' database flag not set.'
    when database_flags @> '[{"name":"log_disconnections","value":"on"}]'
      then title || ' ''log_disconnections'' database flag set to ''on''.'
    else title || ' ''log_disconnections'' database flag set to ''off''.'
  end as reason
  
  , location as location, project as project
from
  gcp_sql_database_instance;