Database roles, access, and privileges

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.

This page describes how identities, roles and permissions work when using Lakebase.

When you create a database instance, a Postgres role is automatically set up for the creator of the database instance. The creator is given the databricks_superuser role, which has a high level of access to all data in the database instance. For more information about this role and its privileges, see Database role types and permissions.

As the databricks_superuser, you can allow other users to access the database instance through PostgreSQL by creating a corresponding Postgres role for them. For more on how to create Postgres roles, see Create and manage Postgres roles for Databricks identities.

When and how permissions are checked

When you use Postgres syntax or connect through a PostgreSQL interface, Lakebase enforces PostgreSQL-specific access controls by using the following:

• Postgres roles
• Role memberships
• Postgres-granted permissions

In all other scenarios, Lakebase enforces Databricks-specific access controls, such as:

• Databricks identities (users, groups and service principals)
• Databricks group memberships
• Workspace access control lists (ACLs)
• Unity Catalog privileges

note

There is no automatic sync between Databricks identities and memberships, and Postgres roles and memberships.

Use case / Permission or identity	Manage database instances	Create synced tables	Manage synced table pipeline	Query Postgres tables from a SQL warehouse	Query online features in feature and model serving	Query Postgres tables in PostgreSQL
Databricks identities	yes	yes	yes	yes	yes	requires a corresponding Postgres role
Databricks group memberships	yes	yes	yes	yes	yes	only checked on login when logging in as a group
Instance ACLs	yes	yes
Pipeline ACLs		yes	yes
UC permissions		yes		yes	yes
Postgres roles						yes
Postgres role memberships						yes
Postgres permissions						yes

Check Postgres permissions

To read from or write to a table in Postgres, users require the following permissions:

• CONNECT on the database
• USAGE on the schema
• USAGE on the table

Run the following SQL statement to create a function named pg_temp.check_permissions that checks a user's permissions, including inherited permissions.

Psql

CREATE OR REPLACE FUNCTION pg_temp.check_permissions(TEXT, TEXT, TEXT, TEXT)
RETURNS TABLE(database_connect BOOLEAN, schema_usage BOOLEAN, table_select BOOLEAN,
              table_insert BOOLEAN, table_update BOOLEAN, table_delete BOOLEAN) AS $$
SELECT
  has_database_privilege($1, $2, 'CONNECT'),
  has_schema_privilege($1, $3, 'USAGE'),
  has_table_privilege($1, $4, 'SELECT'),
  has_table_privilege($1, $4, 'INSERT'),
  has_table_privilege($1, $4, 'UPDATE'),
  has_table_privilege($1, $4, 'DELETE')
$$ LANGUAGE sql
;

To use the function, substitute a Databricks username for <your_user>, your instance name for databricks_postgres, and your table name for <my_table> in the following query:

Psql

SELECT * FROM pg_temp.check_permissions('<your_user>', 'databricks_postgres', 'public', '<my_table>');

The following SQL statement creates a function named pg_temp.make_owner_acl that returns a detailed view of all granted permissions, excluding inherited permissions, for all users:

Psql

CREATE OR REPLACE FUNCTION pg_temp.make_owner_acl(owner_oid OID)
RETURNS TABLE(grantor OID, grantee OID, privilege_type TEXT, is_grantable BOOLEAN) AS $$
SELECT owner_oid, owner_oid,'OWNER'::TEXT, True
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION pg_temp.get_all_permissions(TEXT, TEXT, TEXT)
RETURNS TABLE(type TEXT, name TEXT, role TEXT, acl TEXT, can_grant TEXT) AS $$
SELECT type, name,
  CASE WHEN grantee = 0 THEN 'PUBLIC' ELSE pg_get_userbyid(grantee) END AS role, privilege_type,
  CASE WHEN privilege_type = 'OWNER' THEN 'YES, ALL' WHEN is_grantable THEN 'YES' ELSE 'NO' END AS can_grant
FROM (
  SELECT 'DATABASE' type, datname as name, (pg_temp.make_owner_acl(datdba)).*
    FROM pg_database
    WHERE datname = $1
  UNION ALL
  SELECT 'DATABASE' type, datname as name, (aclexplode(datacl)).*
    FROM pg_database
    WHERE datname = $1
  UNION ALL
  SELECT 'SCHEMA' type, nspname as name, (pg_temp.make_owner_acl(nspowner)).*
    FROM pg_namespace
    WHERE nspname = $2
  UNION ALL
  SELECT 'SCHEMA' type, nspname as name, (aclexplode(nspacl)).*
    FROM pg_namespace
    WHERE nspname = $2
  UNION ALL
  SELECT 'TABLE' type, relname as name, (pg_temp.make_owner_acl(relowner)).*
    FROM pg_class
    WHERE relname = $3
  UNION ALL
  SELECT 'TABLE' type, relname as name, (aclexplode(relacl)).*
    FROM pg_class
    WHERE relname = $3
  )
$$ LANGUAGE SQL
;

To use the function, substitute your instance name for databricks_postgres and your table name for <my_table> in the following query:

Psql

SELECT * FROM pg_temp.get_all_permissions('databricks_postgres', 'public', '<mytable>');

In Postgres, a role can be a member of another role, and the membership specifies whether the permissions are inherited from the parent role. To see all the roles that a certain role is part of, use the following SQL statement to create the SQL function pg_temp.get_inherited_roles:

Psql

CREATE OR REPLACE FUNCTION pg_temp.get_inherited_roles(
  role_name TEXT
)
RETURNS TABLE(inherited_roles TEXT, member_via TEXT, inherits_permissions TEXT) AS $$
  WITH RECURSIVE role_tree AS (
    SELECT
        m.roleid,
        pg_get_userbyid(m.roleid) rolname,
         'DIRECT' COLLATE "C" as member_via,
        m.inherit_option as inherits_permissions
    FROM pg_auth_members m
    WHERE m.member = (SELECT oid FROM pg_roles WHERE rolname = $1)
    UNION ALL
    SELECT
        m.roleid,
        pg_get_userbyid(m.roleid) rolname,
        rt.rolname::text as member_via,
        (rt.inherits_permissions AND m.inherit_option) as inherits_permissions
    FROM pg_auth_members m
    JOIN role_tree rt ON m.member = rt.roleid
  )
  SELECT
      rolname AS inherited_roles,
      member_via,
      CASE WHEN inherits_permissions THEN 'YES' ELSE 'NO' END as inherits_permissions
  FROM role_tree
  GROUP BY inherited_roles, member_via, inherits_permissions
  ORDER BY inherits_permissions DESC
$$ LANGUAGE sql
;

To use the function, substitute a Databricks username for <your_user>:

Psql

SELECT * FROM pg_temp.get_inherited_roles('<your role>');

To see the admin of a role, use the following SQL query and substitute the role name for <target_role>:

Psql

SELECT pg_get_userbyid(m.member) admin
FROM pg_auth_members m
WHERE m.roleid = (SELECT oid FROM pg_roles WHERE rolname = '<target_role>')
  AND m.admin_option = true;

Databricks identities and Postgres roles

You can connect to a database instance using Databricks user, group, or service principal identities with OAuth credentials using a Postgres client.

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.

Add a Postgres role for a Databricks identity

When you create a new Postgres role, you can add membership to the databricks_superuser role and enable some role attributes.

databricks_superuser has broad access to read and write all data in the instance and view system metrics. Specifically, databricks_superuser is a member of the standard Postgres roles pg_read_all_data, pg_write_all_data, and pg_monitor. Configure more granular access permissions directly in Postgres when connected as a role with sufficient privileges to issue the necessary grants.

You can configure the following role attributes:

• 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

To create a new Postgres role, click Add PostgreSQL Role and use the model to add privileges. You must select a Databricks identity that doesn’t already have a Postgres role in the instance. You can optionally add membership to a standard role or specific role attributes, if you have CAN MANAGE permission on the database instance.

Manage Postgres roles

Users with CAN_MANAGE permission on a database instance can perform the following actions:

• Create Postgres roles for other Databricks identities, including assigning additional privileges as needed.
• Remove Postgres roles for any Databricks identity using the UI or API.

Users with CAN_USE permission on a database instance can perform the following actions:

• Add a role for their own identity, without additional privileges.

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.

Grant instance privileges to Databricks identities

A user must have specific permissions on the database instance to manage the instance and perform table operations. Workspace admins and the instance creator 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 Manage instance permissions in the upper-right.
5. Enter a user, group, or service principal to grant additional privileges to.
6. Select the permission you want to grant to the identity. See Database instance ACLs.
7. Click + Add.
8. Any workspace user can view or list database instances. Database catalog and synced table permissions are further governed by Unity Catalog metastore, catalog, schema, and table permissions. For more details, see Manage privileges in Unity Catalog.