Azure Audit Postgressql Enable Log Connections Remediation

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.

Triage and Remediation​

Remediation​

Triage and Remediation

Remediation