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 Preview uses provisioned compute that you manually scale. For the new version of Lakebase, which supports autoscaling compute, branching, instant restore, and other advanced features, see Lakebase Postgres (Autoscaling Preview). See choosing between versions to understand which version is right for you.

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 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 Databricks identity. You must select a 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 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 Compute in the workspace sidebar.
2. Click Database instances.
3. Click the Permissions tab.

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