Skip to content

Use AAD authentication with PostgreSQL databases#

Security · Azure Database for PostgreSQL · 2023_06

Use Azure Active Directory (AAD) authentication with Azure Database for PostgreSQL databases.

Description#

Azure Database for PostgreSQL offer two authentication models, Azure Active Directory (AAD) and PostgreSQL logins. AAD authentication supports centialized identity management in addition to modern password protections. Some of the benefits of AAD authentication over PostgreSQL authentication including:

  • Support for Azure Multi-Factor Authentication (MFA).
  • Conditional-based access with Conditional Access.

It is also possible to disable PostgreSQL authentication entirely for the flexible server deployment model.

Recommendation#

Consider using Azure Active Directory (AAD) authentication with Azure Database for PostgreSQL databases. Additionally, consider disabling PostgreSQL authentication.

Examples#

Configure with Azure template#

To deploy Azure Database for PostgreSQL flexible servers that pass this rule:

  • Configure the Microsoft.DBforPostgreSQL/flexibleServers/administrators sub-resource.
  • Set the properties.principalName to the user principal name of the AAD administrator user, group, or application.
  • Set the properties.principalType to the principal type used to represent the type of AAD administrator.
  • Set the properties.tenantId to the tenant ID of the AAD administrator user, group, or application.

For example:

Azure Template snippet
{
  "type": "Microsoft.DBforPostgreSQL/flexibleServers/administrators",
  "apiVersion": "2022-12-01",
  "name": "[format('{0}/{1}', parameters('serverName'), parameters('name'))]",
  "properties": {
    "principalName": "[parameters('principalName')]",
    "principalType": "[parameters('principalType')]",
    "tenantId": "[parameters('tenantId')]"
  },
  "dependsOn": [
    "postgreSqlFlexibleServer"
  ]
}

To deploy Azure Database for PostgreSQL single servers that pass this rule:

  • Configure the Microsoft.DBforPostgreSQL/servers/administrators sub-resource.
  • Set the properties.administratorType to ActiveDirectory.
  • Set the properties.login to the AAD administrator login object name.
  • Set the properties.sid to the object ID GUID of the AAD administrator user, group, or application.
  • Set the properties.tenantId to the tenant ID of the AAD administrator user, group, or application.

For example:

Azure Template snippet
{
  "type": "Microsoft.DBforPostgreSQL/servers/administrators",
  "apiVersion": "2017-12-01",
  "name": "[format('{0}/{1}', parameters('serverName'), 'activeDirectory')]",
  "properties": {
    "administratorType": "ActiveDirectory",
    "login": "[parameters('login')]",
    "sid": "[parameters('sid')]",
    "tenantId": "[parameters('tenantId')]"
  },
  "dependsOn": [
    "postgreSqlSingleServer"
  ]
}

Configure with Bicep#

To deploy Azure Database for PostgreSQL flexible servers that pass this rule:

  • Configure the Microsoft.DBforPostgreSQL/flexibleServers/administrators sub-resource.
  • Set the properties.principalName to the user principal name of the AAD administrator user, group, or application.
  • Set the properties.principalType to the principal type used to represent the type of AAD administrator.
  • Set the properties.tenantId to the tenant ID of the AAD administrator user, group, or application.

For example:

Azure Bicep snippet
resource aadAdmin 'Microsoft.DBforPostgreSQL/flexibleServers/administrators@2022-12-01' = {
  name: name
  parent: postgreSqlFlexibleServer
  properties: {
    principalName: principalName
    principalType: principalType
    tenantId: tenantId
  }
}

To deploy Azure Database for PostgreSQL single servers that pass this rule:

  • Configure the Microsoft.DBforPostgreSQL/servers/administrators sub-resource.
  • Set the properties.administratorType to ActiveDirectory.
  • Set the properties.login to the AAD administrator login object name.
  • Set the properties.sid to the object ID GUID of the AAD administrator user, group, or application.
  • Set the properties.tenantId to the tenant ID of the AAD administrator user, group, or application.

For example:

Azure Bicep snippet
resource aadAdmin 'Microsoft.DBforPostgreSQL/servers/administrators@2017-12-01' = {
  name: 'activeDirectory'
  parent: postgreSqlSingleServer
  properties: {
    administratorType: 'ActiveDirectory'
    login: login
    sid: sid
    tenantId: tenantId
  }
}

Notes#

The single server deployment model is limited to only one Azure AD admin at a time and does not support enforcing AAD-authentication only.

Last update: 2023-06-03

Comments