Profile Applicability:

  • Level 1

Description:
 The log_disconnections parameter in PostgreSQL controls whether the server logs every disconnection event, including the duration of each session. This parameter is essential for auditing and troubleshooting connection issues and provides insight into session behaviors and any unexpected disconnections.

Rationale:
 Enabling the log_disconnections parameter allows the database to log the end of each session, which includes valuable information such as the session duration. This helps with troubleshooting, identifying potential unauthorized activities, and understanding the overall usage pattern of database resources.

Impact:

  • Pros:

    • Helps monitor session activities, providing insights into potential security incidents and performance issues.

    • Useful for auditing and detecting any unusual or malicious disconnections.

    • Assists in diagnosing session or connection-related issues.

  • Cons:

    • Generates additional log data, which may increase storage requirements.

    • Can lead to log overload on high-traffic systems, potentially causing performance degradation if not managed properly.

Default Value:

By default, log_disconnections is set to OFF.

Pre-requisites:
 Ensure that PostgreSQL logging is enabled and log management is properly configured to handle the logs generated.

Remediation

Test Plan:

Using Azure Portal:

  1. Log in to Azure Portal: https://portal.azure.com.

  2. Navigate to Azure Database for PostgreSQL Servers.

  3. Select the relevant PostgreSQL server.

  4. Under Settings, click on Server parameters.

  5. Ensure that the log_disconnections parameter is set to ON.

Using Azure CLI:
1. Run the following command to check the log_disconnections setting:

az postgres server show --resource-group <resource-group-name> --name <server-name> --query "properties.parameters.log_disconnections"

  • The output should show ON.

Implementation Plan:

Using Azure Portal:

  1. Go to Azure Database for PostgreSQL Servers.

  2. Select the server and go to Server parameters.

  3. Set the log_disconnections parameter to ON and click Save.

Using Azure CLI:
1.  To enable log_disconnections, run the following command:

az postgres server configuration set --resource-group <resource-group-name> --server-name <server-name> --name log_disconnections --value ON


Backout Plan:

Using Azure Portal:

  1. Go to Azure Database for PostgreSQL Servers.

  2. Under Settings, click on Server parameters.

  3. Set the log_disconnections parameter back to OFF if necessary.

Using Azure CLI:
1.  To disable log_disconnections, run the following command:

az postgres server configuration set --resource-group <resource-group-name> --server-name <server-name> --name log_disconnections --value OFF


References: