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 Apps in the top right corner and select Lakebase Postgres.
2. Click Provisioned to open the Provisioned instances page.
3. Select your database instance.
4. Select the Roles page in the Lakebase App sidebar.
5. Click Add role in the top right corner.
6. From the Principal drop-down menu, select a user, group, or service principal that you want to grant database access. You must select a Azure Databricks identity that doesn't already have a Postgres role in the instance.
7. (Optional) Select System Roles to grant:
   • databricks_superuser: Grants read and write access to all data. This is an administrative role with broad privileges that inherits from pg_read_all_data, pg_write_all_data, and pg_monitor. See Pre-created roles and permissions.
8. (Optional) Select System Attributes to grant specific capabilities:
   • CREATEDB: Grants permission to create new databases.
   • CREATEROLE: Grants permission to create new roles.
   • BYPASSRLS: Grants permission to bypass row-level security (RLS) policies.
9. Click Add.

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 Apps in the top right corner and select Lakebase Postgres.
2. Click Provisioned to open the Provisioned instances page.
3. Select your database instance.
4. Select the Roles page in the Lakebase App sidebar.

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 Apps in the top right corner and select Lakebase Postgres.
2. Click Provisioned to open the Provisioned instances page.
3. Select your database instance.
4. Select the Roles page in the Lakebase App sidebar.
5. For the role you want to drop, click on the right side of the row.
6. Click Drop.
7. (Optional) If the role owns objects, turn on Reassign owned objects to enable dropping the role. This option reassigns owned objects to another role before dropping. Some objects (such as grants to the role being dropped) aren't reassignable and are dropped after reassignment of all reassignable objects.
8. 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.