Skip to main content

Create and manage Postgres roles for Databricks identities

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.

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 owner needs to create corresponding Postgres roles.

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

Create Postgres roles based on 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 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.

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

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

    Psql
    -- Give the role read permission to access a table
    GRANT SELECT ON "my_schema"."my_table" TO <role-name>;
    Psql
    -- Remove write access to a table from the role
    REVOKE INSERT, UPDATE, DELETE ON TABLE "my_schema"."my_table" FROM <role-name>;
    Psql
    -- Revoke all access to a database from the role
    REVOKE CONNECT ON DATABASE "example_database" FROM <role-name>;

View Databricks identity roles​

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.

Psql
CREATE EXTENSION IF NOT EXISTS databricks_auth;
Psql
SELECT * from databricks_list_roles;

Authenticate as Databricks identity-based Postgres roles​

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

Authenticate as Databricks user or service principal​

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

Authenticate as Databricks group​

Any direct or indirect member (user or service principal) of the Databricks group identity can authenticate to Postgres and login as the 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.

Bash
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 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 Databricks identity-based Postgres role​

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.

Last updated on