Azure Introduction
Azure Pricing
Azure Threats
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
CISAZURE, CBP, HITRUST, SOC2, NISTCSF, PCIDSS
Triage and Remediation
Remediation
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.
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:
-
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_disconnectionsparameter. 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_disconnectionsparameter. -
Finally, you can verify that the
log_disconnectionsparameter 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.