Enable LOG_CHECKPOINTS Parameter for PostgreSQL Servers

More Info:

Ensure that log_checkpoints server parameter is enabled for all PostgreSQL database servers available within your Microsoft Azure cloud account. The log_checkpoints parameter allows checkpoints and restart points to be logged in the Azure PostgreSQL server log.

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 of enabling "LOG_CHECKPOINTS" parameter for PostgreSQL servers in Azure, you can follow the below steps:

1. Open the Azure portal and navigate to the Azure Database for PostgreSQL service.

2. Select the PostgreSQL server for which you want to enable the "LOG_CHECKPOINTS" parameter.

3. Click on the "Configuration" option in the left-hand menu.

4. Under the "Settings" tab, scroll down to the "Custom" section and click on the "+ Add" button.

5. In the "Add Configuration Parameter" window, enter "log_checkpoints" in the "Name" field and "on" in the "Value" field.

6. Click on the "OK" button to save the configuration parameter.

7. Restart the PostgreSQL server for the changes to take effect.

After completing these steps, the "LOG_CHECKPOINTS" parameter will be enabled for the PostgreSQL server in Azure.

Using CLI

To remediate the "LOG_CHECKPOINTS" parameter misconfiguration for PostgreSQL servers in Azure using Azure CLI, you can follow these steps:

1. Open the Azure CLI on your local machine or through the Azure portal.

2. Run the following command to log in to your Azure account:

az login

3. Once you are logged in, run the following command to list all the available PostgreSQL servers in your Azure account:

az postgres server list

4. Choose the server that you want to remediate and run the following command to get the current configuration settings:

az postgres server configuration list --resource-group <resource-group-name> --server-name <server-name>

Make sure to replace <resource-group-name> and <server-name> with the actual names of your resource group and server.

5. Identify whether the "LOG_CHECKPOINTS" parameter is set to "off" or not in the output of the above command.

6. If the "LOG_CHECKPOINTS" parameter is set to "off", run the following command to update the configuration and enable it:

az postgres server configuration set --resource-group <resource-group-name> --server-name <server-name> --name log_checkpoints --value on

Again, replace <resource-group-name> and <server-name> with the actual names of your resource group and server.

7. Verify the updated configuration by running the command in step 4 again.

8. Repeat steps 5-7 for any other PostgreSQL servers that have the "LOG_CHECKPOINTS" parameter misconfiguration.

By following these steps, you can remediate the "LOG_CHECKPOINTS" parameter misconfiguration for PostgreSQL servers in Azure using Azure CLI.

Using Python

To remediate the misconfiguration of enabling the "LOG_CHECKPOINTS" parameter for PostgreSQL servers in Azure using Python, you can follow the below steps:

1. First, you need to connect to the Azure PostgreSQL server using the psycopg2 library in Python. You can install it using the following command:

   !pip install psycopg2-binary

2. Once you have installed the psycopg2 library, you can use the following code snippet to connect to the Azure PostgreSQL server:

   import psycopg2
   
   conn = psycopg2.connect(
       host="your_server_name.postgres.database.azure.com",
       database="your_database_name",
       user="your_username@your_server_name",
       password="your_password"
   )

   Replace the <your_server_name>, <your_database_name>, <your_username>, and <your_password> with your actual server details.

3. After connecting to the Azure PostgreSQL server, you can execute the following SQL query to enable the "LOG_CHECKPOINTS" parameter:

   with conn.cursor() as cur:
       cur.execute("ALTER SYSTEM SET log_checkpoints = on;")
       conn.commit()

   This query will enable the "LOG_CHECKPOINTS" parameter for the Azure PostgreSQL server.

4. Finally, you can close the connection to the Azure PostgreSQL server using the following code:

   conn.close()

   This will close the connection to the Azure PostgreSQL server.

By following the above steps, you can remediate the misconfiguration of enabling the "LOG_CHECKPOINTS" parameter for PostgreSQL servers in Azure using Python.

Using Terraform

# Existing PostgreSQL server
resource "azurerm_postgresql_server" "PG_SERVER" {
  name                = "PG_SERVER_NAME"          # replace with your server name
  resource_group_name = "RESOURCE_GROUP_NAME"     # replace with your resource group
  location            = "AZURE_REGION"            # replace with your region

  administrator_login          = "ADMIN_USERNAME" # replace with admin username
  administrator_login_password = "ADMIN_PASSWORD" # replace with admin password

  sku_name   = "B_Gen5_1"
  version    = "11"
  storage_mb = 5120

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

# Enable log_checkpoints parameter
resource "azurerm_postgresql_configuration" "log_checkpoints" {
  name                = "log_checkpoints"
  resource_group_name = azurerm_postgresql_server.PG_SERVER.resource_group_name
  server_name         = azurerm_postgresql_server.PG_SERVER.name
  value               = "on"
}

This change does not force replacement of the PostgreSQL server; it updates the configuration in place (Azure may require a restart of the service, which is handled by the platform).

Verification: terraform plan should show creation (or update) of azurerm_postgresql_configuration.log_checkpoints with value = "on" and no replace action for azurerm_postgresql_server.PG_SERVER.