Manage Postgres roles

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 Azure Databricks identity must have a corresponding Postgres role.

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

Create Postgres roles and grant privileges for Azure Databricks identities

Create Postgres roles using the Azure Databricks UI or PostgreSQL queries.

UI

Users with CAN USE permission on the database instance can view the existing Postgres roles associated with Azure Databricks identities or add a role for their own identity to the instance.

Users with CAN MANAGE can additionally create roles for other Azure Databricks identities, including with additional privileges, and drop roles for any Azure Databricks identity.

You can assign additional permissions to any desired users, groups, or service principals in the Database instances overview page.

1. Click Compute in the workspace sidebar.

2. Click Database instances.

3. Click the Permissions tab.

4. Click Add PostgreSQL role in the upper-right side.

5. For Workspace identity, enter a user, group, or service principal and select the Azure Databricks identity. You must select a Azure Databricks identity that doesn’t already have a Postgres role in the instance.

6. Select a Role membership. If you have CAN MANAGE permission on the database instance, you can add membership to the databricks_superuser role and enable some role attributes.

7. Select which PostgreSQL attributes to grant to the new role.

   • CREATEDB: grants permission to create new databases
   • CREATEROLE: grants permission to create new roles
   • BYPASS RLS: grants permission to bypass all row-level security in the instance

8. Click Confirm.

PostgreSQL

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 to Postgres 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.

1. Create the databricks_auth extension. Each Postgres database must have its own extension.

   CREATE EXTENSION IF NOT EXISTS databricks_auth;
   

2. 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');
     

3. 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

UI

You can see which users, groups, and service principals has a corresponding Postgres role in the Database instances overview page.

1. Click Compute in the workspace sidebar.
2. Click Database instances.
3. Click the Permissions tab.

PostgreSQL

Use PostgreSQL queries 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;

Drop a Azure Databricks identity-based Postgres role

UI

Dropping a role cannot be undone. You can recreate a role, but any object ownership reassignment is non-reversible without dropping the new role that owns reassigned objects.

1. Click Compute in the workspace sidebar.
2. Click Database instances.
3. Click the Permissions tab.
4. For the role identity you want to drop, on the rightmost side, click .
5. Click Drop role.
6. If you need to drop a role that owns objects, turn on Reassign owned objects. This will reassign all reassignable owned objects (databases, schemas and tables) to the other role and then drop any non-reassignable owned objects.
7. Click Confirm.

PostgreSQL

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.