Skip to main content

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.

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.

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.

View Databricks identity roles​

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.

Drop a Databricks identity-based Postgres role​

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 Kebab menu icon..
  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.

Use PostgreSQL queries to 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

Check user permissions on a specific table​

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

PostgreSQL
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:

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

View all permissions for database, schema, and 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:

PostgreSQL
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:

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

Check role inheritance hierarchy​

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:

PostgreSQL
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>:

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

Find role administrators​

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

PostgreSQL
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;
Last updated on