Enable LOG_DISCONNECTIONS Parameter for PostgreSQL Servers

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

CIS AZURE
Cloudanix Best Practice
HITRUST CSF
NIST CSF
PCI
SOC2

Triage and Remediation

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:

Login to Azure Portal (https://portal.azure.com/).
Go to the Azure Database for PostgreSQL servers.
Select the PostgreSQL server for which you want to enable "LOG_DISCONNECTIONS" parameter.
Click on the "Connection security" option in the left-hand side menu.
Scroll down to the "Firewall and virtual networks" section and click on the "Configure firewall" button.
In the "Firewall rules" section, click on the "Add client IP" button to add your IP address to the firewall rules.
Click on the "Save" button to save the changes.
Go back to the PostgreSQL server overview page and click on the "Connection strings" option in the left-hand side menu.
Copy the connection string for the PostgreSQL server.
Open the PostgreSQL client tool (e.g. pgAdmin) and connect to the PostgreSQL server using the connection string.
Once connected, execute the following SQL command to enable "LOG_DISCONNECTIONS" parameter:

ALTER SYSTEM SET log_disconnections = on;

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.

Using CLI

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.

Using Python

To remediate the misconfiguration "Enable 'LOG_DISCONNECTIONS' Parameter for PostgreSQL Servers" in Azure using Python, you can follow the below steps:

First, you need to install the Azure SDK for Python using the following command:
```
pip install azure-mgmt-resource
```

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.

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.
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.
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.

Using Terraform

resource "azurerm_postgresql_server" "POSTGRES_SERVER" {
  name                = "POSTGRES_SERVER_NAME"
  location            = azurerm_resource_group.RG.location
  resource_group_name = azurerm_resource_group.RG.name

  sku_name   = "B_Gen5_1"
  version    = "11"
  storage_mb = 51200

  administrator_login          = "ADMIN_USERNAME"
  administrator_login_password = "ADMIN_PASSWORD"

  auto_grow_enabled             = true
  backup_retention_days         = 7
  geo_redundant_backup_enabled  = false
  public_network_access_enabled = true
  ssl_enforcement_enabled       = true
}

resource "azurerm_postgresql_configuration" "log_disconnections" {
  name                = "log_disconnections"
  resource_group_name = azurerm_postgresql_server.POSTGRES_SERVER.resource_group_name
  server_name         = azurerm_postgresql_server.POSTGRES_SERVER.name

  value = "ON"
}

Replace:

POSTGRES_SERVER_NAME with your PostgreSQL server name.
RG with your existing azurerm_resource_group resource name.
ADMIN_USERNAME / ADMIN_PASSWORD with appropriate credentials.

This change does not force replacement of the server; it adds/updates a configuration resource only.

Verification: terraform plan should show azurerm_postgresql_configuration.log_disconnections being created or updated with value: "OFF" => "ON" (or similar), and no azurerm_postgresql_server replacement.

More Info:​

Risk Level​

Address​

Compliance Standards​

Triage and Remediation​

Remediation​