​
More Info:

PostgreSQL does not log attempted connections by default. Enabling the log_connections setting will create log entries for each attempted connection as well as successful completion of client authentication which can be useful in troubleshooting issues and to determine any unusual connection attempts to the server. This recommendation is applicable to PostgreSQL database instances.

​
Risk Level

Medium

​
Address

Security

​
Compliance Standards

CISGCP, CBP, SOC2, PCIDSS, NISTCSF

​
Triage and Remediation

​
Remediation

Using Console

To remediate the “PostgreSQL Log Connections Flag Should Be On” misconfiguration on GCP using the GCP console, follow these steps:
  1. Go to the GCP Console and select the project that contains the PostgreSQL instance that needs to be remediated.
  2. In the left navigation pane, select “SQL” under the “Storage” section.
  3. Select the PostgreSQL instance that needs to be remediated.
  4. Click on the “Edit” button at the top of the page.
  5. Scroll down to the “Flags” section and click on the “Add Flag” button.
  6. In the “Name” field, enter “log_connections”.
  7. In the “Value” field, enter “on”.
  8. Click on the “Save” button at the bottom of the page to apply the changes.
  9. Wait for a few minutes for the changes to take effect.
After completing these steps, the “PostgreSQL Log Connections Flag Should Be On” misconfiguration will be remediated for the PostgreSQL instance on GCP.

​

To remediate the PostgreSQL Log Connections Flag Should Be On misconfiguration for GCP using GCP CLI, follow these steps:
  1. Open the Cloud Shell in the GCP Console.
  2. Run the following command to list the available PostgreSQL instances in your project: 
    gcloud sql instances list
  3. Note down the instance name of the PostgreSQL instance you want to remediate.
  4. Run the following command to enable the log_connections flag for the PostgreSQL instance: 
    gcloud sql instances patch [INSTANCE_NAME] --database-flags log_connections=on
    Replace [INSTANCE_NAME] with the name of your PostgreSQL instance.
  5. Confirm that the log_connections flag has been enabled by running the following command: 
    gcloud sql instances describe [INSTANCE_NAME] | grep log_connections
    Replace [INSTANCE_NAME] with the name of your PostgreSQL instance. The output should show that the log_connections flag is set to “on”. 
    log_connections: on
  6. Your PostgreSQL instance is now remediated with the log_connections flag enabled.
To remediate the PostgreSQL log connections flag misconfiguration in GCP using Python, follow these steps:
  1. Install the google-cloud-secret-manager and google-auth Python libraries using pip: 
    pip install google-cloud-secret-manager google-auth
  2. Import the necessary libraries and authenticate to the GCP project: 
    from google.cloud import secretmanager
from google.oauth2 import service_account

credentials = service_account.Credentials.from_service_account_file('/path/to/key.json')
client = secretmanager.SecretManagerServiceClient(credentials=credentials)
    Replace /path/to/key.json with the path to your GCP service account key file.
  3. Retrieve the value of the postgres-config secret: 
    secret_name = "postgres-config"
project_id = "my-project-id"
version = "latest"

name = f"projects/{project_id}/secrets/{secret_name}/versions/{version}"
response = client.access_secret_version(request={"name": name})
config = response.payload.data.decode("UTF-8")
  4. Update the postgresql.conf file to enable logging of connections: 
    config_lines = config.split("\n")
new_config_lines = []

for line in config_lines:
    if line.startswith("#log_connections = off"):
        new_config_lines.append("log_connections = on")
    else:
        new_config_lines.append(line)

new_config = "\n".join(new_config_lines)
  5. Write the updated configuration back to the secret: 
    parent = f"projects/{project_id}"
payload = {"data": new_config.encode("UTF-8")}
response = client.update_secret(request={"secret": {"name": secret_name, "payload": payload}, "update_mask": {"paths": ["payload"]}, "parent": parent})
    This will update the postgres-config secret with the new configuration that enables logging of connections.
  6. Verify that the configuration was updated successfully by checking the postgresql.conf file on the PostgreSQL server. 
    # Connect to the PostgreSQL server
psql -h <hostname> -U <username> -d <database>

# Check the value of the log_connections parameter
SHOW log_connections;
    The output should be on.

​
Additional Reading: