PostgreSQL instances should have the 'log_connections' database flag set to 'on'
Description
By enabling the log_connections setting, every attempted server connection is logged along with the successful completion of client authentication. Once the session starts, you cannot change this parameter.
Rationale
By default, PostgreSQL does not log attempted connections. By enabling the log_connections setting, you can create log entries for every attempted connection as well as the successful completion of client authentication. This can be useful in troubleshooting issues and determining any unusual connection attempts to the server. This recommendation is applicable to PostgreSQL database instances.
Impact
By turning on logging, the required storage increaess over time. Mismanaged logs may cause your storage costs to increase.
Setting custom flags through the command line on certain instances can cause all omitted flags to reset to defaults. This may cause you to lose
custom flags and can result in unforeseen complications or instance restarts. Because of this, Datadog recommends applying these flag changes during a period of low usage.
From the console
- In the Google Cloud Console, navigate to the Cloud SQL Instances page.
- Select the PostgreSQL instance that you want to enable the database flag for.
- 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 log_connections flag from the dropdown menu, and set the value as on.
- Click Save.
- Confirm the changes under Flags on the Overview page.
From the command line
Configure the log_connections database flag for every Cloud SQL PosgreSQL database instance using the following command:
gcloud sql instances patch <INSTANCE_NAME> --database-flags
log_connections=on
This command overwrites all previously set database flags. To keep these flags and add new ones, include all flag values to be set on the instance. Otherwise, flags that are not specifically included are set to its default value. For flags that do not take a value, specify the flag name followed by an equals sign, for example: =.
You do not need to restart the Cloud SQL instance.
Default Value
By default, log_connections is off.
References
- https://cloud.google.com/sql/docs/postgres/flags
- https://www.postgresql.org/docs/9.6/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
This patch modifies database flag values, which may require you to restart your instance. Check the list of supported flags to see if your instance will restart when this patch is submitted.
Some database flag settings can affect instance availability or stability, and may remove the instance from the Cloud SQL SLA.
For information about these flags, see the Operational Guidelines.
CIS Controls
Version 8, 8.5 - Collect Detailed Audit Logs
- Configure detailed audit logging for enterprise assets containing sensitive data. Include event source, date, username, timestamp, source addresses, destination addresses, and other useful elements that could assist in a forensic investigation.
