Can you change a postgresql flexserver parameter using an Azure policy / remediation ?

Question

Can you change a postgresql flexserver parameter using an Azure policy / remediation ?

Burket, Joseph 20

Would like to be able to use an Azure policy to audit and change the connection throttling from off to on (for security) for a large number of PostgreSQL flex servers.

Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2024-04-01T22:32:06.6766667+00:00

@Burket, Joseph

Thank you for reaching out.

To audit and enable connection throttling for a large number of Azure PostgreSQL Flexible Servers, you have two options: creating a custom policy definition or using built-in policy definitions.

For more details review https://learn.microsoft.com/en-us/azure/postgresql/single-server/policy-reference

Regards,

Oury
Burket, Joseph 20 Reputation points

2024-04-02T12:25:00.6466667+00:00

Thanks, I am able to do the audit using the built in policy. The issue is the 2nd part to change the parameter to connection throttling to ON is where the my problem is. I am not able to turn it on??
Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2024-04-02T15:49:25.89+00:00

Burket, Joseph

Thank you for getting back to me.

What errors messages are you getting.?

You will need server-level permissions to change configuration settings.

Regards,

Oury
Burket, Joseph 20 Reputation points

2024-04-02T16:20:55.4133333+00:00

Getting this error Unable to evaluate policy with definition '/subscriptions/048b6bde-2d39-4258-xxxxxxxxxxx/providers/Microsoft.Authorization/policyDefinitions/0b7cc50f-aa15-4981-8cd7-947dd99a9882' and assignment '/subscriptions/048b6bde-2d39-4258-b3f4-xxxxxxxxx/resourceGroups/security-poc-rg/providers/Microsoft.Authorization/policyAssignments/9d311936879xxxxxxxx'. Policy evaluation exceeded the maximum allowed time.

"properties": {

"displayName": "Custom postgreSQL connection throttling deploy if not set to ON",

"policyType": "Custom",

"mode": "Indexed",

"metadata": {

"createdBy": ",

"createdOn": "2024-03-13T14:45:12.2874858Z",

"updatedBy": "",

"updatedOn": "2024-03-26T21:16:50.8529423Z"

},

"version": "1.0.0",

"parameters": {},

"policyRule": {

"if": {

"field": "type",

"equals": "Microsoft.DBforPostgreSQL/flexibleservers"

},

"then": {

"effect": "deployIfNotExists",

"details": {

"type": "Microsoft.DBforPostgreSQL/flexibleservers/configurations",

"name": "connection_throttling",

"existenceCondition": {

"field": "Microsoft.DBforPostgreSQL/flexibleservers/configurations/value",

"equals": "Off"

},

"roleDefinitionIds": [

"/providers/Microsoft.Authorization/roleDefinitions/b24988ac-6180-42a0-ab88-20f7382dd24c"

],

"deployment": {

"properties": {

"mode": "incremental",

"template": {

"$schema": "https://schema.management.azure.com/schemas/2018-05-01/subscriptionDeploymentTemplate.json#",

"contentVersion": "https://url.usb.m.mimecastprotect.com/s/lMhHCDwK7lh3rpzZQpjI5LKPH?domain=1.0.0.0",

"resources": [

{

"type": "Microsoft.DBforPostgreSQL/flexibleservers/configurations",

"apiVersion": "2021-06-01",

"name": "[concat(parameters('serverName'), '/connectionThrottling')]",

"properties": {

"value": "ON"

}

}

]

}

},

"parameters": {

"serverName": {

"value": "[field('name')]"

}

}

}

}

}

},

"versions": [

"1.0.0"

]

},

}

}
Burket, Joseph 20 Reputation points

2024-04-03T13:49:16.7733333+00:00

I installed this on the subscription and it is showing compliant with connection throttling is set to off? The policy seems to be looking at the server parameter require_secure_transport for compliance. Should it be looking for connection throttling? Thanks again for your assistance
Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2024-04-03T16:32:22.4666667+00:00

@Burket, Joseph

The policy you mentioned might be looking at the require_secure_transport parameter for compliance. However, for connection throttling, it should be looking at the connection throttling parameter. Ensure that the policy checks the correct parameter to enforce the desired behavior.

Remember to verify that pgAudit is correctly installed and configured, and that the connection throttling policy aligns with your security requirements.

Audit logging in Azure Database for PostgreSQL - Flexible Server

Regards,

Oury

Answer accepted by question author

0 additional answers

Your answer

Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2024-04-01T22:32:06.6766667+00:00

@Burket, Joseph

Thank you for reaching out.

To audit and enable connection throttling for a large number of Azure PostgreSQL Flexible Servers, you have two options: creating a custom policy definition or using built-in policy definitions.

For more details review https://learn.microsoft.com/en-us/azure/postgresql/single-server/policy-reference

Regards,

Oury
Burket, Joseph 20 Reputation points

2024-04-02T12:25:00.6466667+00:00

Thanks, I am able to do the audit using the built in policy. The issue is the 2nd part to change the parameter to connection throttling to ON is where the my problem is. I am not able to turn it on??
Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2024-04-02T15:49:25.89+00:00

Burket, Joseph

Thank you for getting back to me.

What errors messages are you getting.?

You will need server-level permissions to change configuration settings.

Regards,

Oury
Burket, Joseph 20 Reputation points

2024-04-02T16:20:55.4133333+00:00

Getting this error Unable to evaluate policy with definition '/subscriptions/048b6bde-2d39-4258-xxxxxxxxxxx/providers/Microsoft.Authorization/policyDefinitions/0b7cc50f-aa15-4981-8cd7-947dd99a9882' and assignment '/subscriptions/048b6bde-2d39-4258-b3f4-xxxxxxxxx/resourceGroups/security-poc-rg/providers/Microsoft.Authorization/policyAssignments/9d311936879xxxxxxxx'. Policy evaluation exceeded the maximum allowed time.

"properties": {

"displayName": "Custom postgreSQL connection throttling deploy if not set to ON",

"policyType": "Custom",

"mode": "Indexed",

"metadata": {

"createdBy": ",

"createdOn": "2024-03-13T14:45:12.2874858Z",

"updatedBy": "",

"updatedOn": "2024-03-26T21:16:50.8529423Z"

},

"version": "1.0.0",

"parameters": {},

"policyRule": {

"if": {

"field": "type",

"equals": "Microsoft.DBforPostgreSQL/flexibleservers"

},

"then": {

"effect": "deployIfNotExists",

"details": {

"type": "Microsoft.DBforPostgreSQL/flexibleservers/configurations",

"name": "connection_throttling",

"existenceCondition": {

"field": "Microsoft.DBforPostgreSQL/flexibleservers/configurations/value",

"equals": "Off"

},

"roleDefinitionIds": [

"/providers/Microsoft.Authorization/roleDefinitions/b24988ac-6180-42a0-ab88-20f7382dd24c"

],

"deployment": {

"properties": {

"mode": "incremental",

"template": {

"$schema": "https://schema.management.azure.com/schemas/2018-05-01/subscriptionDeploymentTemplate.json#",

"contentVersion": "https://url.usb.m.mimecastprotect.com/s/lMhHCDwK7lh3rpzZQpjI5LKPH?domain=1.0.0.0",

"resources": [

{

"type": "Microsoft.DBforPostgreSQL/flexibleservers/configurations",

"apiVersion": "2021-06-01",

"name": "[concat(parameters('serverName'), '/connectionThrottling')]",

"properties": {

"value": "ON"

}

}

]

}

},

"parameters": {

"serverName": {

"value": "[field('name')]"

}

}

}

}

}

},

"versions": [

"1.0.0"

]

},

}

}
Burket, Joseph 20 Reputation points

2024-04-03T13:49:16.7733333+00:00

I installed this on the subscription and it is showing compliant with connection throttling is set to off? The policy seems to be looking at the server parameter require_secure_transport for compliance. Should it be looking for connection throttling? Thanks again for your assistance
Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2024-04-03T16:32:22.4666667+00:00

@Burket, Joseph

The policy you mentioned might be looking at the require_secure_transport parameter for compliance. However, for connection throttling, it should be looking at the connection throttling parameter. Ensure that the policy checks the correct parameter to enforce the desired behavior.

Remember to verify that pgAudit is correctly installed and configured, and that the connection throttling policy aligns with your security requirements.

Audit logging in Azure Database for PostgreSQL - Flexible Server

Regards,

Oury

Answer 1

@Burket, Joseph

Use the below sample:

{

"mode": "All",

"policyRule": {

"if": {

  "allOf": [

    {

      "field": "type",

      "equals": "Microsoft.DBforPostgreSQL/flexibleServers"

    }

  ]

},

"then": {

  "effect": "[parameters('effect')]",

  "details": {

    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",

    "name": "require_secure_transport",

    "existenceCondition": {

      "field": "Microsoft.DBforPostgreSQL/flexibleServers/configurations/value",

      "equals": "ON"

    },

    "roleDefinitionIds": [

      "/providers/microsoft.authorization/roleDefinitions/b24988ac-6180-42a0-ab88-20f7382dd24c"

    ],

    "deployment": {

      "properties": {

        "mode": "incremental",

        "template": {

          "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",

          "contentVersion": "1.0.0.0",

          "parameters": {

            "serverName": {

              "type": "string"

            }

          },

          "variables": {},

          "resources": [

            {

              "name": "[concat(parameters('serverName'), '/connection_throttle.enable)]",

              "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",

              "apiVersion": "2022-01-20-preview",

              "properties": {

                "value": "ON",

                "source": "user-override"

              }

            }

          ]

        },

        "parameters": {

          "serverName": {

            "value": "[field('name')]"

          }

        }

      }

    }

  }

}

},

"parameters": {

"effect": {

  "type": "String",

  "metadata": {

    "displayName": "Effect",

    "description": "Enable or disable the execution of the policy"

  },

  "allowedValues": [

    "DeployIfNotExists",

    "Disabled"

  ],

  "defaultValue": "DeployIfNotExists"

}

}

Regards,

Oury

Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2024-04-03T17:00:06.78+00:00

@Burket, Joseph

Thank you for accepting the answer and for using Microsoft Q&A forum.

Share via

Can you change a postgresql flexserver parameter using an Azure policy / remediation ?

0 additional answers

Your answer