More Info:

Ensure that the “log_disconnections” server parameter is enabled for all PostgreSQL database servers provisioned in your Microsoft Azure cloud account. The “log_disconnections” parameter enables the logging of session termination. The log output provides information similar to the one generated by the “log_connections” parameter, plus the duration of the session. Only Azure account admins can change this parameter at the session start, and it cannot be changed at all during a session.

Risk Level

Medium

Address

Security

Compliance Standards

CISAZURE, CBP, HITRUST, SOC2, NISTCSF, PCIDSS

Triage and Remediation

Remediation

Using Console

To remediate the misconfiguration “Enable ‘LOG_DISCONNECTIONS’ Parameter for PostgreSQL Servers” in Azure using the Azure console, follow the below steps:
  1. Login to Azure Portal (https://portal.azure.com/).
  2. Go to the Azure Database for PostgreSQL servers.
  3. Select the PostgreSQL server for which you want to enable “LOG_DISCONNECTIONS” parameter.
  4. Click on the “Connection security” option in the left-hand side menu.
  5. Scroll down to the “Firewall and virtual networks” section and click on the “Configure firewall” button.
  6. In the “Firewall rules” section, click on the “Add client IP” button to add your IP address to the firewall rules.
  7. Click on the “Save” button to save the changes.
  8. Go back to the PostgreSQL server overview page and click on the “Connection strings” option in the left-hand side menu.
  9. Copy the connection string for the PostgreSQL server.
  10. Open the PostgreSQL client tool (e.g. pgAdmin) and connect to the PostgreSQL server using the connection string.
  11. Once connected, execute the following SQL command to enable “LOG_DISCONNECTIONS” parameter:
ALTER SYSTEM SET log_disconnections = on;
  1. Restart the PostgreSQL server to apply the changes.
With these steps, you have successfully remediated the misconfiguration “Enable ‘LOG_DISCONNECTIONS’ Parameter for PostgreSQL Servers” in Azure using the Azure console.

To remediate the misconfiguration “Enable ‘LOG_DISCONNECTIONS’ Parameter for PostgreSQL Servers” for Azure using Azure CLI, follow the below steps:Step 1: Open the Azure CLI on your system.Step 2: Login to your Azure account using the below command:
az login
Step 3: Once you are logged in, set the default subscription to the one you want to work with using the below command:
az account set --subscription <SubscriptionID>
Step 4: Now, to enable the “LOG_DISCONNECTIONS” parameter for PostgreSQL servers, you need to update the PostgreSQL server configuration. For this, you need to get the resource ID of the PostgreSQL server using the below command:
az postgres server list --resource-group <ResourceGroupName> --query "[].id"
Note: Replace <ResourceGroupName> with the name of the resource group where the PostgreSQL server is located.Step 5: Once you have the resource ID of the PostgreSQL server, use the below command to update the server configuration and enable the “LOG_DISCONNECTIONS” parameter:
az postgres server configuration set --resource-group <ResourceGroupName> --server-name <PostgreSQLServerName> --name log_disconnections --value on
Note: Replace <ResourceGroupName> with the name of the resource group where the PostgreSQL server is located and <PostgreSQLServerName> with the name of the PostgreSQL server.Step 6: After executing the above command, the “LOG_DISCONNECTIONS” parameter will be enabled for the PostgreSQL server.That’s it! You have successfully remediated the misconfiguration “Enable ‘LOG_DISCONNECTIONS’ Parameter for PostgreSQL Servers” for Azure using Azure CLI.
To remediate the misconfiguration “Enable ‘LOG_DISCONNECTIONS’ Parameter for PostgreSQL Servers” in Azure using Python, you can follow the below steps:
  1. First, you need to install the Azure SDK for Python using the following command:
    pip install azure-mgmt-resource
    
  2. After installing the Azure SDK, you need to authenticate to your Azure account using the following code:
    from azure.common.credentials import ServicePrincipalCredentials
    from azure.mgmt.resource import ResourceManagementClient
    from azure.mgmt.postgresql import PostgreSQLManagementClient
    
    credentials = ServicePrincipalCredentials(
        client_id='<client-id>',
        secret='<client-secret>',
        tenant='<tenant-id>'
    )
    
    resource_client = ResourceManagementClient(credentials, '<subscription-id>')
    postgresql_client = PostgreSQLManagementClient(credentials, '<subscription-id>')
    
    Replace the <client-id>, <client-secret>, <tenant-id>, <subscription-id> with your own values.
  3. Once you are authenticated, you can get the list of PostgreSQL servers using the following code:
    servers = postgresql_client.servers.list_by_resource_group('<resource-group-name>')
    
    Replace the <resource-group-name> with the name of the resource group where your PostgreSQL servers are located.
  4. Next, you need to update the configuration of each PostgreSQL server to enable the log_disconnections parameter. You can do this using the following code:
    for server in servers:
        parameters = postgresql_client.configurations.get('<resource-group-name>', server.name, 'default')
        parameters.log_disconnections = True
        postgresql_client.configurations.create_or_update('<resource-group-name>', server.name, 'default', parameters)
    
    This code will update the configuration of each PostgreSQL server in the specified resource group to enable the log_disconnections parameter.
  5. Finally, you can verify that the log_disconnections parameter is enabled by connecting to your PostgreSQL server and checking the PostgreSQL logs.
    psql -h <server-name>.postgres.database.azure.com -U <username>@<server-name> -d postgres -c "SELECT * FROM pg_settings WHERE name = 'log_disconnections'"
    
    Replace the <server-name>, <username> with your own values.
That’s it! This will remediate the misconfiguration “Enable ‘LOG_DISCONNECTIONS’ Parameter for PostgreSQL Servers” in Azure using Python.