Enable LOG_CONNECTIONS Parameter for PostgreSQL Servers

More Info:

Ensure that log_connections server parameter is enabled for all PostgreSQL database servers available in your Microsoft Azure cloud account. The log_connections parameter allows each attempted connection to the database server to be logged, including successful client authentication requests. Only Azure users with administrative privileges can change this parameter at session start, and it cannot be changed during an access 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 enable the "LOG_CONNECTIONS" parameter for PostgreSQL servers in Azure, please follow the below steps:

Login to Azure portal (https://portal.azure.com/)
Navigate to the PostgreSQL server for which you want to enable the parameter.
In the left-hand menu, click on "Settings".
Under the "Settings" tab, click on the "Configuration" option.
Under the "Parameters" tab, search for the "logging_collector" parameter.
Set the value of "logging_collector" parameter to "on".
Search for the "log_connections" parameter.
Set the value of "log_connections" parameter to "on".
Click on the "Save" button to save the changes.

Once you have followed these steps, the "LOG_CONNECTIONS" parameter will be enabled for your PostgreSQL server in Azure.

Using CLI

To enable the "LOG_CONNECTIONS" parameter for PostgreSQL servers in Azure using Azure CLI, follow these steps:

Open the Azure CLI in your terminal or command prompt.
Log in to your Azure account using the command:
```
az login
```
Once logged in, select the Azure subscription where your PostgreSQL server is located using the command:
```
az account set --subscription <subscription-id>
```
Next, retrieve the resource ID of your PostgreSQL server using the command:
```
az postgres server show --resource-group <resource-group-name> --name <server-name> --query id --output tsv
```
Replace <resource-group-name> with the name of the resource group where your PostgreSQL server is located, and <server-name> with the name of your PostgreSQL server.
Once you have the resource ID, use the following command to enable the "LOG_CONNECTIONS" parameter:
```
az postgres server configuration set --resource-group <resource-group-name> --server-name <server-name> --name log_connections --value on
```
Replace <resource-group-name> with the name of the resource group where your PostgreSQL server is located, and <server-name> with the name of your PostgreSQL server.
After executing the command, the "LOG_CONNECTIONS" parameter will be enabled for your PostgreSQL server. You can verify this by checking the server configuration using the command:
```
az postgres server configuration show --resource-group <resource-group-name> --server-name <server-name> --name log_connections
```
Replace <resource-group-name> with the name of the resource group where your PostgreSQL server is located, and <server-name> with the name of your PostgreSQL server.

Using Python

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

First, you need to authenticate and create a PostgreSQL server object using the Azure Python SDK. You can use the following code snippet to achieve this:

from azure.common.credentials import ServicePrincipalCredentials
from azure.mgmt.rdbms.postgresql import PostgreSQLManagementClient
from azure.mgmt.rdbms.postgresql.models import ServerUpdateParameters

# Replace the values in <> with your Azure credentials and PostgreSQL server details
credentials = ServicePrincipalCredentials(
    client_id='<client_id>',
    secret='<client_secret>',
    tenant='<tenant_id>'
)

client = PostgreSQLManagementClient(
    credentials,
    '<subscription_id>'
)

server_name = '<server_name>'
resource_group_name = '<resource_group_name>'

server = client.servers.get(resource_group_name, server_name)

Once you have the server object, you can update the server parameters to enable the "log_connections" parameter. You can use the following code snippet to achieve this:

# Update the server parameters to enable log_connections
parameters = ServerUpdateParameters(
    parameters={
        'log_connections': 'on'
    }
)

client.servers.update(
    resource_group_name,
    server_name,
    parameters
)

Finally, you can verify that the parameter has been updated by checking the server parameters. You can use the following code snippet to achieve this:

# Get the server parameters and check if log_connections is enabled
server_parameters = client.servers.list_by_resource_group(resource_group_name)

for parameter in server_parameters:
    if parameter.name == server_name:
        print(parameter.parameters.log_connections)

This code will print "on" if the "log_connections" parameter has been successfully enabled.

Using Terraform

resource "azurerm_postgresql_server" "this" {
  name                = "POSTGRESQL_SERVER_NAME"          # replace with your server name
  resource_group_name = "RESOURCE_GROUP_NAME"             # replace with your RG name
  location            = "AZURE_REGION"                    # e.g. "eastus"

  sku_name   = "B_Gen5_1"                                 # replace with desired SKU
  version    = "11"                                       # replace with your engine version
  storage_mb = 51200

  administrator_login          = "ADMIN_USERNAME"         # replace
  administrator_login_password = "ADMIN_PASSWORD"         # replace (use sensitive input)

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

  ssl_enforcement_enabled = true
}

resource "azurerm_postgresql_configuration" "log_connections" {
  name                = "log_connections"
  resource_group_name = azurerm_postgresql_server.this.resource_group_name
  server_name         = azurerm_postgresql_server.this.name
  value               = "on"
}

This change does not force replacement of the PostgreSQL server; it updates the log_connections configuration in place on the existing server.

For verification, terraform plan should show creation (or update) of azurerm_postgresql_configuration.log_connections with name = "log_connections" and value = "on" and no destroy/replace of azurerm_postgresql_server.this.

More Info:​

Risk Level​

Address​

Compliance Standards​

Triage and Remediation​

Remediation​