Skip to content

Use Entra ID authentication with PostgreSQL databases#

Security · Azure Database for PostgreSQL · Rule · 2023_06 · Critical

Use Entra ID authentication with Azure Database for PostgreSQL databases.

Description#

Azure Database for PostgreSQL offer two authentication models, Entra ID (previously knows as Azure AD) and PostgreSQL logins. Entra ID authentication supports centralized identity management in addition to modern password protections. Some of the benefits of Entra ID 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 Entra ID 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 Entra ID administrator user, group, or application.
  • Set the properties.principalType to the principal type used to represent the type of Entra ID administrator.
  • Set the properties.tenantId to the tenant ID of the Entra ID 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 Entra ID administrator login object name.
  • Set the properties.sid to the object ID GUID of the Entra ID administrator user, group, or application.
  • Set the properties.tenantId to the tenant ID of the Entra ID 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 Entra ID administrator user, group, or application.
  • Set the properties.principalType to the principal type used to represent the type of Entra ID administrator.
  • Set the properties.tenantId to the tenant ID of the Entra ID 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 Entra ID administrator login object name.
  • Set the properties.sid to the object ID GUID of the Entra ID administrator user, group, or application.
  • Set the properties.tenantId to the tenant ID of the Entra ID 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.
  • Does not support enforcing Entra ID authentication only.

Comments