Manage Postgres roles

Preview

This feature is in Public Preview in the following regions: us-east-1, us-west-2, eu-west-1, ap-southeast-1, ap-southeast-2, eu-central-1, us-east-2, ap-south-1.

Lakebase Provisioned uses provisioned compute that you manually scale. For feature comparison with the new Lakebase Autoscaling, see choosing between versions.

A Postgres role for the Lakebase database instance owner’s Databricks identity is created automatically.

Initially, only the owner of the instance can log in and access the instance through Postgres. To allow other Databricks identities to log in to the database instance, the Databricks identity must have a corresponding Postgres role.

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

Create Postgres roles and grant privileges for Databricks identities

Create Postgres roles using the Databricks UI or PostgreSQL queries.

note

Role management actions are governed by the permissions granted on the database instance. Ensure you have the appropriate level of access before attempting to manage roles.

UI

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

Users with CAN MANAGE can additionally create roles for other Databricks identities, including with additional privileges, and drop roles for any 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 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 Databricks identity (user, service principal, or group). Native Postgres authenticated sessions cannot create 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 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.

   PostgreSQL

   CREATE EXTENSION IF NOT EXISTS databricks_auth;

2. Use the databricks_create_role function to add and create new Postgres roles for Databricks identities. The role must not already exist. If a role with the same name exists, drop it before creating the Databricks identity-based role.

   PostgreSQL

   SELECT databricks_create_role('identity_name', 'identity_type');

   The identity_name and identity_type parameters depend on the Databricks identity type:

   • Databricks User:

     • identity_name: Email of the user e.g. myuser@databricks.com
     • identity_type: USER
     PostgreSQL

     SELECT databricks_create_role('myuser@databricks.com','USER');

   • Databricks Service Principal:

     • identity_name: Application ID of Service Principal e.g. 8c01cfb1-62c9-4a09-88a8-e195f4b01b08
     • identity_type: SERVICE_PRINCIPAL
     PostgreSQL

     SELECT databricks_create_role('8c01cfb1-62c9-4a09-88a8-e195f4b01b08','SERVICE_PRINCIPAL');

   • Databricks Group:

     • identity_name: Name of the group (case sensitive): My Group 123
     • identity_type: GROUP
     PostgreSQL

     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.

   PostgreSQL

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

   Remove write access to a table from the role.

   PostgreSQL

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

   Revoke all access to a database from the role.

   PostgreSQL

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

View 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 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 Databricks users, service principals, and groups added to authenticate as Postgres roles.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS databricks_auth;

PostgreSQL

SELECT * from databricks_list_roles;

Drop a 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 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 Databricks identity-based role is dropped, it cannot be used for token-based authentication and accessing Postgres.