Create and manage Postgres roles for Azure Databricks identities

2025-07-07

Important

This feature is in Public Preview in the following regions: westus, westus2, eastus, eastus2, northeurope, westeurope, australiaeast, brazilsouth, canadacentral, centralindia, centralus, southcentralus, southeastasia, uksouth.

A Postgres role for the Lakebase database instance owner’s Azure Databricks identity is created automatically. Initially, only the owner of the instance can log in and access the instance through Postgres. To allow other Azure Databricks identities to log in to the database instance, the owner needs to create corresponding Postgres roles.

This page describes how to add and manage additional Azure Databricks identity-based roles in PostgreSQL.

Create Postgres roles based on Azure Databricks identities

Before creating new Postgres roles, verify that you meet the following requirements:

You must have the CREATE and CREATE ROLE permissions on the database.
You must authenticate and log in as a Azure Databricks identity (user, service principal, or group). Native Postgres authenticated sessions cannot create Azure Databricks roles.
Your authentication token must be valid and not expired at the time of role creation.

Use the databricks_create_role function to add and create Azure Databricks identity-based PostgreSQL roles. The custom PostgreSQL extension databricks_auth provides the databricks_create_role function.

Create the databricks_auth extension. Each Postgres database must have its own extension.
```
CREATE EXTENSION IF NOT EXISTS databricks_auth;
```
Use the databricks_create_role function to add and create new Postgres roles for Azure Databricks identities. The role must not already exist. If a role with the same name exists, drop it before creating the Azure Databricks identity-based role.
```
SELECT databricks_create_role('identity_name', 'identity_type');
```
The identity_name and identity_type parameters depend on the Azure Databricks identity type:
- Azure Databricks User:
  - identity_name: Email of the user e.g. myuser@databricks.com
  - identity_type: USER
```
SELECT databricks_create_role('myuser@databricks.com','USER');
```
- Azure Databricks Service Principal:
  - identity_name: Application ID of Service Principal e.g. 8c01cfb1-62c9-4a09-88a8-e195f4b01b08
  - identity_type: SERVICE_PRINCIPAL
```
SELECT databricks_create_role('8c01cfb1-62c9-4a09-88a8-e195f4b01b08','SERVICE_PRINCIPAL');
```
- Azure Databricks Group:
  - identity_name: Name of the group (case sensitive): My Group 123
  - identity_type: GROUP
```
SELECT databricks_create_role('My Group 123','GROUP');
```

Grant Postgres roles privileges

A role created using databricks_create_role only has privileges granted to PUBLIC after creation. To grant or revoke additional privileges, use the standard Postgres privilege management commands GRANT and REVOKE.

Give the role read permission to access a table.

GRANT SELECT ON "my_schema"."my_table" TO <role-name>;

Remove write access to a table from the role.

REVOKE INSERT, UPDATE, DELETE ON TABLE "my_schema"."my_table" FROM <role-name>;

Revoke all access to a database from the role.

REVOKE CONNECT ON DATABASE "example_database" FROM <role-name>;

View Azure Databricks identity roles

To list all the Azure Databricks identity roles created by default and from using the databricks_create_role function, use the databricks_list_roles function in the databricks_auth extension. This lists all Azure Databricks users, service principals, and groups added to authenticate as Postgres roles.

CREATE EXTENSION IF NOT EXISTS databricks_auth;

SELECT * from databricks_list_roles;

Authenticate as Azure Databricks identity-based Postgres roles

After a Postgres role is created for a Azure Databricks identity following the above steps, it can be used to login to Postgres based on the identity type:

Authenticate as Azure Databricks user or service principal

Authenticating to Postgres with a Azure Databricks user or service principal is the same as authenticating to Postgres with an OAuth token.

Authenticate as Azure Databricks group

Groups and group memberships are not synced from Azure Databricks into Postgres and neither are Unity Catalog permissions. However, after a Azure Databricks group is added into Postgres, any Azure Databricks user in the group can log in as the group using the user's password. This allows you to manage permissions at the group level in Postgres. Any direct or indirect member (user or service principal) of the Azure Databricks group identity can authenticate to Postgres and log in as the Azure Databricks group Postgres role.

When authenticating as a group identity using a user or service principal token, the group membership is only validated at the time of authentication. Any previously open connection with a group member token will not be closed if the member is removed from the group after authentication. Any new connection request from a removed group member will be rejected during authentication.

export PGPASSWORD='<OAuth token of a group member>'
export GROUPROLENAME = <pg-case-sensitive-group-role-name>

psql -h $HOSTNAME -p 5432 -d databricks_postgres -U $GROUPROLENAME

Only groups assigned to the Azure Databricks workspace of the database instance are supported for group-based Postgres login. To learn how to assign a group to a workspace, see Assign a group to a workspace.

Drop a Azure Databricks identity-based Postgres role

A Azure Databricks identity-based Postgres role can be dropped and removed the same as any other Postgres role. For more details, see the PostgreSQL documentation on dropping roles. After a Azure Databricks identity-based role is dropped, it cannot be used for token-based authentication and accessing Postgres.