Manage permissions

This page describes when and how to grant Azure Databricks users and identities permissions to a database instance.

To allow other users to use PostgreSQL to access the database instance, the databricks_superuser must create corresponding Postgres roles for them. For details on how to create Postgres roles, see Manage Postgres roles.

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:

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

Database instance permissions

A user must have specific permissions on the database instance to manage the instance and perform table operations from the Azure Databricks UI, API, or SDK. 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 OLTP Database.
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 permissions 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.
9. Click Save.

Postgres permissions

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

• CONNECT on the database
• USAGE on the schema (or CREATE to create new tables)
• SELECT, INSERT, UPDATE, or DELETE on the table

For the full list of Postgres privileges, see the PostgreSQL documentation.

Check user permissions on a specific table in Postgres

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

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, use the following query:

SELECT * FROM pg_temp.check_permissions('<your_user>', '<your_database>', '<your_schema>', '<your_table>');

-- Example:
SELECT * FROM pg_temp.check_permissions('joe@acme.org', 'databricks_postgres', 'public', 'table1');

View all permissions for database, schema, and table in Postgres

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:

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, use the following query:

SELECT * FROM pg_temp.get_all_permissions('<your_database>', '<your_schema>', '<your_table>');

-- Example:
SELECT * FROM pg_temp.get_all_permissions('databricks_postgres', 'public', 'table1');

Check role inheritance hierarchy in Postgres

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:

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 Azure Databricks username for <your_user>:

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

Find role administrators in Postgres

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

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;