Azure audit postgresql enable storage auto growth remediation

Using Console

To remediate the misconfiguration of not having storage auto-growth enabled in Azure, you can follow these steps:

Log in to the Azure portal (https://portal.azure.com/).
Navigate to the storage account that you want to remediate.
Click on the “Configuration” tab in the left-hand menu.
Under the “Data Protection” section, click on “Blob Service”.
Scroll down to the “Auto-grow” section and toggle the switch to “Enabled”.
Specify the maximum size that you want the storage account to grow to. You can either choose a fixed size or enable automatic growth by percentage.
Click “Save” to apply the changes.

Once you have completed these steps, your storage account will automatically grow as needed to accommodate additional data. This will help prevent any potential data loss due to insufficient storage capacity.

Using CLI

To enable storage auto-growth for Azure using Azure CLI, follow the below steps:

Open the Azure CLI on your local machine or on the Azure portal.
Login to your Azure account using the command:
az login
Select the subscription in which you want to enable storage auto-growth using the command:
az account set --subscription <subscription-id>
Once you have selected the subscription, enable storage auto-growth for the Azure SQL Database using the command:
az sql db update --name <database-name> --resource-group <resource-group-name> --service-objective S0 --max-size 250GB --auto-grow true
In the above command, replace the <database-name> with the name of the database for which you want to enable storage auto-growth, <resource-group-name> with the name of the resource group in which the database is present. The --max-size parameter specifies the maximum size of the database and the --auto-grow parameter enables the storage auto-growth for the database.

Once the command is executed successfully, you will receive a confirmation message.

{
  "autoPauseDelay": null,
  "catalogCollation": "SQL_Latin1_General_CP1_CI_AS",
  "collation": "SQL_Latin1_General_CP1_CI_AS",
  "createMode": null,
  "creationDate": "2021-09-23T11:53:25.06Z",
  "currentServiceObjectiveName": "S0",
  "databaseId": "1f3a2a2a-7f36-4e7b-9d9d-4c6b6d5f9e3c",
  "defaultSecondaryLocation": null,
  "earliestRestoreDate": null,
  "edition": "Standard",
  "elasticPoolId": null,
  "failoverGroupId": null,
  "id": "/subscriptions/xxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/xxxxx/providers/Microsoft.Sql/servers/xxxxx/databases/xxxxx",
  "kind": "v12.0,user",
  "location": "eastus",
  "longTermRetentionBackupResourceId": null,
  "maxLogSizeBytes": null,
  "maxSizeBytes": "268435456000",
  "name": "xxxxx",
  "readScale": null,
  "recoverableDatabaseId": null,
  "recoveryServicesRecoveryPointResourceId": null,
  "requestedServiceObjectiveName": null,
  "resourceGroup": "xxxxx",
  "restorableDroppedDatabaseId": null,
  "restorePointInTime": null,
  "sampleName": null,
  "serviceLevelObjective": "S0",
  "sourceDatabaseDeletionDate": null,
  "sourceDatabaseId": null,
  "status": "Online",
  "tags": {},
  "type": "Microsoft.Sql/servers/databases",
  "zoneRedundant": false
}

This confirms that storage auto-growth has been enabled for the Azure SQL Database.

Using Python

To enable storage auto-growth in Azure using Python, you can use the Azure SDK for Python. Here are the steps to remediate this issue:

Install the Azure SDK for Python:

pip install azure-mgmt-storage

Import the necessary modules:

from azure.common.credentials import ServicePrincipalCredentials
from azure.mgmt.storage import StorageManagementClient

Authenticate with Azure using Service Principal credentials:

TENANT_ID = '<your tenant id>'
CLIENT_ID = '<your client id>'
CLIENT_SECRET = '<your client secret>'
SUBSCRIPTION_ID = '<your subscription id>'

credentials = ServicePrincipalCredentials(
    client_id=CLIENT_ID,
    secret=CLIENT_SECRET,
    tenant=TENANT_ID
)

Create a StorageManagementClient object:

storage_client = StorageManagementClient(
    credentials=credentials,
    subscription_id=SUBSCRIPTION_ID
)

Get the storage account that needs to be remediated:

RESOURCE_GROUP_NAME = '<your resource group name>'
ACCOUNT_NAME = '<your storage account name>'

storage_account = storage_client.storage_accounts.get_properties(
    RESOURCE_GROUP_NAME,
    ACCOUNT_NAME
)

Enable storage auto-growth by updating the storage account:

storage_account.auto_grow_enabled = True

storage_client.storage_accounts.update(
    RESOURCE_GROUP_NAME,
    ACCOUNT_NAME,
    storage_account
)

This will enable storage auto-growth for the specified storage account in Azure.

Using Terraform

resource "azurerm_postgresql_server" "POSTGRESQL_SERVER" {
  name                = "POSTGRESQL_SERVER_NAME"
  location            = azurerm_resource_group.RG.name
  resource_group_name = azurerm_resource_group.RG.name

  sku_name = "GP_Gen5_4" # CHANGE to your SKU

  administrator_login          = "ADMIN_USERNAME"
  administrator_login_password = "ADMIN_PASSWORD"

  version                          = "11"
  ssl_enforcement_enabled          = true
  public_network_access_enabled    = true
  auto_grow_enabled                = true  # Enable Storage Auto-Growth
  storage_mb                       = 51200 # CHANGE to your current/desired storage size in MB
  backup_retention_days            = 7
  geo_redundant_backup_enabled     = false

  tags = {
    Environment = "production"
  }
}

Note: Enabling auto_grow_enabled = true is an in-place update and should not force replacement of the PostgreSQL server.To verify, terraform plan should show an update to azurerm_postgresql_server.POSTGRESQL_SERVER with auto_grow_enabled changing from false (or null) to true.

Azure Introduction

Azure Pricing

Azure Threats

Azure Misconfigurations

Azure audit postgresql enable storage auto growth remediation

Triage and Remediation

Remediation

​Triage and Remediation

​Remediation

​

Triage and Remediation

Remediation