Control: 6.2.8 Ensure 'log_hostname' database flag for Cloud SQL PostgreSQL instance is set appropriately

Description

PostgreSQL logs only the IP address of the connecting hosts. The log_hostname flag controls the logging of hostnames in addition to the IP addresses logged. The performance hit is dependent on the configuration of the environment and the host name resolution setup. This parameter can only be set in the postgresql.conf file or on the server command line. Default value for log_hostname flag is off.

Logging hostnames can incur overhead on server performance as for each statement logged, DNS resolution will be required to convert IP address to hostname. Depending on the setup, this may be non-negligible. Additionally, the IP addresses that are logged can be resolved to their DNS names later when reviewing the logs excluding the cases where dynamic hostnames are used. This recommendation is applicable to PostgreSQL database instances.

Remediation

From Console:

Login to GCP console and navigate to Cloud SQL Instances.
Select the PostgreSQL instance for which the database flag needs to be enabled.
Click EDIT.
Scroll down to the Flags section.
Click ADD FLAG, choose the log_hostname from the drop-down menu, and set to an appropriate value.
Click SAVE.
Confirm the changes under Flags on the Overview page.

From Command Line:

List all Cloud SQL database instances using the following command

gcloud sql instances list

Configure the log_hostname database flag for every Cloud SQL PostgreSQL database instance using the below command

cloud sql instances patch INSTANCE_NAME --database-flags log_hostname=<off|on>

Note

This command will overwrite all previously set database 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 ("=").
Configuring the above flag does not require restarting the Cloud PostgreSQL instance.

Usage

Run the control in your terminal:

powerpipe control run gcp_compliance.control.cis_v120_6_2_8

Snapshot and share results via Turbot Pipes:

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

SQL

This control uses a named query:

sql_instance_postgresql_log_hostname_database_flag_configured