Create Postgres roles

When you create a project, Lakebase creates several Postgres roles in the project:

• A Postgres role for the project owner's Azure Databricks identity (for example, user@databricks.com), which owns the default databricks_postgres database
• A databricks_superuser administrative role

Both of these roles are visible in the Roles & Databases tab when you first open your project.

The databricks_postgres database is created so you can connect and try out Lakebase immediately after project creation.

Several system-managed roles are also created. These are internal roles used by Azure Databricks services for management, monitoring, and data operations.

See Pre-created roles and System roles.

Create Postgres roles

Lakebase supports two types of Postgres roles for database access:

• OAuth roles for Azure Databricks identities: Create these using the Lakebase UI, the databricks_auth extension with SQL, or the Python SDK and REST API. Enables Azure Databricks identities (users, service principals, and groups) to connect using OAuth tokens.
• Native Postgres password roles: Create these using the Lakebase UI, SQL, or the Python SDK and REST API. Use any valid role name with password authentication.

For guidance on choosing the type of role to use, see Authentication overview. Each is designed for different use cases.

Create an OAuth role for Azure Databricks identities

To allow Azure Databricks identities (users, service principals, or groups) to connect using OAuth tokens, create an OAuth role using the Lakebase UI, the databricks_auth extension with SQL, or the REST API.

For detailed instructions on obtaining OAuth tokens, see Obtain an OAuth token in a user-to-machine flow and Obtain an OAuth token in a machine-to-machine flow.

UI

1. In Roles & Databases > Add role > OAuth tab, select the user, service principal, or group to grant database access to.
2. After creating the role, grant appropriate database privileges. Learn how: Manage permissions

SQL

Prerequisites:

• You must have CREATE and CREATE ROLE permissions on the database
• You must be authenticated as a Azure Databricks identity with a valid OAuth token
• Native Postgres authenticated sessions cannot create OAuth roles

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 create a Postgres role for the Azure Databricks identity:

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

   For a Azure Databricks user:

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

   For a Azure Databricks service principal:

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

   For a Azure Databricks group:

   SELECT databricks_create_role('My Group Name', 'GROUP');
   

   The group name is case-sensitive and must match exactly as it appears in your Azure Databricks workspace. When you create a Postgres role for a group, any direct or indirect member (user or service principal) of that Databricks group can authenticate to Postgres as the group role using their individual OAuth token. This allows you to manage permissions at the group level in Postgres instead of maintaining permissions for individual users.

3. Grant database permissions to the newly created role.

The databricks_create_role() function creates a Postgres role with LOGIN permission only. After creating the role, you must grant the appropriate database privileges and permissions on the specific databases, schemas, or tables the user needs to access. Learn how: Manage permissions

Python SDK

Set identity_type to USER, SERVICE_PRINCIPAL, or GROUP. Set postgres_role to the identity's email address, application ID (UUID), or group display name respectively. This value becomes the Postgres role name and is what you use in connection strings and GRANT statements.

from databricks.sdk import WorkspaceClient
from databricks.sdk.service.postgres import Role, RoleRoleSpec

w = WorkspaceClient()

operation = w.postgres.create_role(
    parent="projects/my-project/branches/production",
    role=Role(
        spec=RoleRoleSpec(
            identity_type="USER",
            postgres_role="user@example.com"
        )
    )
)
role = operation.wait()
print(f"Created role: {role.name}")

After creating the role, grant appropriate database privileges. Learn how: Manage permissions

curl

Set identity_type to USER, SERVICE_PRINCIPAL, or GROUP. Set postgres_role to the identity's email address, application ID (UUID), or group display name respectively. This value becomes the Postgres role name and is what you use in connection strings and GRANT statements.

curl -X POST "$WORKSPACE/api/2.0/postgres/projects/my-project/branches/production/roles" \
  -H "Authorization: Bearer ${DATABRICKS_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{
    "spec": {
      "identity_type": "USER",
      "postgres_role": "user@example.com"
    }
  }' | jq

The endpoint returns a long-running operation. Poll until done is true, then use the role's name field for subsequent API calls. See Long-running operations.

After creating the role, grant appropriate database privileges. Learn how: Manage permissions

Group-based authentication

When you create a Postgres role for a Azure Databricks group, you enable group-based authentication. This allows any member of the Azure Databricks group to authenticate to Postgres using the group's role, simplifying permission management.

How it works:

1. Create a Postgres role for a Databricks group.
2. Grant database permissions to the group role in Postgres. See Manage permissions.
3. Any direct or indirect member (user or service principal) of the Databricks group can connect to Postgres using their individual OAuth token.
4. When connecting, the member authenticates as the group role and inherits all permissions granted to that role.

Authentication flow:

When a group member connects, they specify the group's Postgres role name as the username and their own OAuth token as the password:

export PGPASSWORD='<OAuth token of a group member>'
export GROUP_ROLE_NAME='<pg-case-sensitive-group-role-name>'

psql -h $HOSTNAME -p 5432 -d databricks_postgres -U $GROUP_ROLE_NAME

Important considerations:

• Group membership validation: Group membership is validated only at authentication time. If a member is removed from the Azure Databricks group after establishing a connection, the connection remains active. New connection attempts from removed members are rejected.
• Workspace scoping: Only groups assigned to the same Azure Databricks workspace as the project are supported for group-based authentication. To learn how to assign groups to a workspace, see Manage groups.
• Case sensitivity: The group name used in databricks_create_role() must match the group name exactly as it appears in your Azure Databricks workspace, including case.
• Permission management: Managing permissions at the group level in Postgres is more efficient than managing individual user permissions. When you grant permissions to the group role, all current and future group members inherit those permissions automatically.
• Identity renaming: If a user's email or group display name changes in Azure Databricks, authentication and existing database grants will break. Drop the old role, create a new one with the updated name, and update connection strings and grants.

Create a native Postgres password role

Password connections can be disabled at the project or compute level. See Block password connections.

UI

1. In Roles & Databases > Add role > Password tab, enter a role name and optionally grant databricks_superuser or system attributes (CREATEDB, CREATEROLE, BYPASSRLS).
2. Copy the generated password and provide it securely to the user. It is not shown again.

SQL

CREATE ROLE role_name WITH LOGIN PASSWORD 'your_secure_password';

The password should have at least 12 characters with a mix of lowercase, uppercase, number, and symbol characters. User-defined passwords are validated at creation time to ensure 60-bit entropy.

Python SDK

Omit identity_type to create a password role. The API returns a generated password in the response.

from databricks.sdk import WorkspaceClient
from databricks.sdk.service.postgres import Role, RoleRoleSpec

w = WorkspaceClient()

operation = w.postgres.create_role(
    parent="projects/my-project/branches/production",
    role=Role(
        spec=RoleRoleSpec(
            postgres_role="my-app-role"
        )
    )
)
role = operation.wait()
print(f"Created role: {role.name}")

curl

Omit identity_type to create a password role. The endpoint returns a long-running operation. Poll until done is true.

curl -X POST "$WORKSPACE/api/2.0/postgres/projects/my-project/branches/production/roles" \
  -H "Authorization: Bearer ${DATABRICKS_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{
    "spec": {
      "postgres_role": "my-app-role"
    }
  }' | jq

View Postgres roles

UI

To view all Postgres roles in your project, navigate to your branch's Roles & Databases tab in the Lakebase App. All roles created in the branch, with the exception of System roles, are listed. The Auth type column indicates whether each role uses OAuth or Password authentication.

PostgreSQL

View all roles with \du command:

You can view all Postgres roles, including system roles, using the \du meta-command from any Postgres client (such as psql) or the Lakebase SQL editor:

\du
                                      List of roles
          Role name          |                         Attributes
-----------------------------+------------------------------------------------------------
 cloud_admin                 | Superuser, Create role, Create DB, Replication, Bypass RLS
 my.user@databricks.com      | Create role, Create DB, Bypass RLS
 databricks_control_plane    | Superuser
 databricks_gateway          |
 databricks_monitor          |
 databricks_reader_12345     | Create role, Create DB, Replication, Bypass RLS
 databricks_replicator       | Replication
 databricks_superuser        | Create role, Create DB, Cannot login, Bypass RLS
 databricks_writer_12345     | Create role, Create DB, Replication, Bypass RLS

Python SDK

List all roles:

from databricks.sdk import WorkspaceClient

w = WorkspaceClient()

roles = w.postgres.list_roles(parent="projects/my-project/branches/production")
for role in roles:
    print(f"{role.status.postgres_role} ({role.status.identity_type or 'PASSWORD'}): {role.name}")

Get a specific role:

role = w.postgres.get_role(
    name="projects/my-project/branches/production/roles/rol-xxxx-xxxxxxxxxx"
)
print(role)

curl

List all roles:

curl -X GET "$WORKSPACE/api/2.0/postgres/projects/my-project/branches/production/roles" \
  -H "Authorization: Bearer ${DATABRICKS_TOKEN}" | jq

Get a specific role:

curl -X GET "$WORKSPACE/api/2.0/postgres/projects/my-project/branches/production/roles/rol-xxxx-xxxxxxxxxx" \
  -H "Authorization: Bearer ${DATABRICKS_TOKEN}" | jq

The response includes the name field (for example, rol-xxxx-xxxxxxxxxx) required for update and delete calls.

Update a role

To update a role's attributes in the UI, select Edit role from the role menu in the Roles & Databases tab.

Use the API to update a role's system roles or attributes. Use update_mask as a query parameter to specify which fields to change; only the masked fields are modified.

curl -X PATCH "$WORKSPACE/api/2.0/postgres/projects/my-project/branches/production/roles/rol-xxxx-xxxxxxxxxx?update_mask=spec.membership_roles%2Cspec.attributes.createdb" \
  -H "Authorization: Bearer ${DATABRICKS_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "projects/my-project/branches/production/roles/rol-xxxx-xxxxxxxxxx",
    "spec": {
      "membership_roles": ["DATABRICKS_SUPERUSER"],
      "attributes": { "createdb": true }
    }
  }' | jq

To remove databricks_superuser, pass an empty array: "membership_roles": [].

Drop a Postgres role

You can drop both Databricks identity-based roles and native Postgres password roles.

UI

Deleting a role is a permanent action that cannot be undone. To delete a role that owns a database you must specify the role to reassign the owned objects to. Otherwise the database must be deleted manually before deleting the role that owns the database.

To delete any Postgres role using the UI:

1. Navigate to your branch's Roles & Databases tab in the Lakebase App.
2. Select Delete role from the role menu and confirm the deletion.

PostgreSQL

You can drop any Postgres role using standard Postgres commands. For details, see the PostgreSQL documentation on dropping roles.

Drop a role:

DROP ROLE role_name;

After a Azure Databricks identity-based role is dropped, that identity can no longer authenticate to Postgres using OAuth tokens until a new role is created.

Python SDK

from databricks.sdk import WorkspaceClient

w = WorkspaceClient()

operation = w.postgres.delete_role(
    name="projects/my-project/branches/production/roles/rol-xxxx-xxxxxxxxxx"
)
operation.wait()

curl

curl -X DELETE "$WORKSPACE/api/2.0/postgres/projects/my-project/branches/production/roles/rol-xxxx-xxxxxxxxxx" \
  -H "Authorization: Bearer ${DATABRICKS_TOKEN}" | jq

Pre-created roles

After a project is created, Azure Databricks automatically creates Postgres roles for project administration and getting started.

Learn more about these roles' specific capabilities and privileges: Pre-created role capabilities

System roles created by Azure Databricks

Azure Databricks creates the following system roles required for internal services. You can view these roles by issuing a \du command from psql or the Lakebase SQL Editor.

To learn how roles, privileges, and role memberships work in Postgres, use the following resources in the Postgres documentation:

• Database Roles
• Privileges

Next steps

• Manage permissions
• Connect to your project
• Learn about authentication