Skip to main content

Manage permissions

Beta

Lakebase Postgres (Autoscaling Beta) is the next version of Lakebase, available for evaluation only. For production workloads, use Lakebase Public Preview. See choosing between versions to understand which version is right for you.

Learn how to grant database permissions to Postgres roles in your Lakebase database project.

Prerequisite: Before granting permissions, you must have created a Postgres role. You can create native Postgres password roles using the Lakebase UI, or create OAuth roles for Databricks identities using the databricks_auth extension. See Create Postgres roles.

Project-level access in Beta​

Lakebase Autoscaling Beta does not support ACLs (Access Control Lists) for database projects. Any Databricks identity with access to the workspace can access and administer database projects through the Lakebase App UI.

However, to connect through Postgres (using psql, JDBC, etc.), users need:

  1. A corresponding Postgres role
  2. Explicit database permissions granted to that role

Pre-created roles and default permissions​

When you create a database project, Lakebase automatically creates several Postgres roles with different levels of access. Understanding these default roles helps you decide when to grant additional permissions.

Pre-created roles:

Role

LOGIN

CREATEDB

CREATEROLE

REPLICATION

BYPASSRLS

<project_owner_role>

✅

✅

✅

✅

✅

databricks_superuser

—

✅

✅

—

✅

Default privileges:

  • <project_owner_role>: The Databricks identity of the project creator (for example, user@databricks.com). Owns the default databricks_postgres database. Member of databricks_superuser (inherits CREATEDB, CREATEROLE, and BYPASSRLS privileges). Additionally has LOGIN and REPLICATION permissions.
  • databricks_superuser: Administrative role with all privileges (with grant) on all databases, schemas, tables, and sequences. EXECUTE on pg_stat_statements_reset(). Has CREATEDB, CREATEROLE, and BYPASSRLS permissions. Cannot login (NOLOGIN). This role should not be used in automated applications.

Learn more: Pre-created roles

Grant permissions to Postgres roles​

After creating a Postgres role, you must grant database permissions to allow the role to access specific databases, schemas, and tables. By default, a newly created Postgres role has no database permissions.

Use standard Postgres GRANT commands to assign permissions:

Grant read access to a table:

SQL
GRANT SELECT ON TABLE schema_name.table_name TO role_name;

Grant write access to a table:

SQL
GRANT INSERT, UPDATE, DELETE ON TABLE schema_name.table_name TO role_name;

Grant all privileges on a database:

SQL
GRANT ALL PRIVILEGES ON DATABASE database_name TO role_name;

Grant schema-level permissions:

SQL
-- Allow creating objects in a schema
GRANT CREATE ON SCHEMA schema_name TO role_name;

-- Allow using a schema (required to access objects within it)
GRANT USAGE ON SCHEMA schema_name TO role_name;

Grant permissions on all tables in a schema:

SQL
-- Grant read access to all existing tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;

-- Grant write access to all existing tables in a schema
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO role_name;

-- Automatically grant permissions on future tables in a schema
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO role_name;

Revoke permissions:

SQL
REVOKE CONNECT ON DATABASE database_name FROM role_name;

Learn more: PostgreSQL privileges documentation

Database ownership​

In Postgres, the database owner is the role that created the database or was assigned ownership. Database ownership grants comprehensive privileges:

Privileges granted to database owners:

  • Full control: Complete control over the database, including the ability to drop it
  • All privileges: Automatic ALL PRIVILEGES on the database (CREATE, CONNECT, TEMPORARY)
  • Grant privileges: Ability to grant and revoke privileges on the database to other roles
  • Schema creation: Can create schemas within the database
  • Object ownership: Owns all objects created by default in the database
  • Alter database: Can modify database settings and properties

In Lakebase projects:

  • Your Databricks identity role (for example, user@databricks.com) owns the default databricks_postgres database
  • Database owners can delegate privileges to other roles using GRANT statements

Learn more: Privileges in the PostgreSQL documentation

Check Postgres permissions​

Use these SQL queries to check permissions and role memberships in your database project.

List all roles and their attributes​

View all roles in the database and their capabilities:

PostgreSQL
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

Check role memberships​

See what roles a specific user belongs to:

PostgreSQL
SELECT r.rolname as "Role name"
FROM pg_roles r
JOIN pg_auth_members m ON r.oid = m.roleid
WHERE m.member = (SELECT oid FROM pg_roles WHERE rolname = '<your_role>');

Check table permissions for a role​

View what permissions a role has on tables in the public schema:

PostgreSQL
SELECT
  schemaname,
  tablename,
  has_table_privilege('<your_role>', schemaname||'.'||tablename, 'SELECT') as can_select,
  has_table_privilege('<your_role>', schemaname||'.'||tablename, 'INSERT') as can_insert,
  has_table_privilege('<your_role>', schemaname||'.'||tablename, 'UPDATE') as can_update,
  has_table_privilege('<your_role>', schemaname||'.'||tablename, 'DELETE') as can_delete
FROM pg_tables
WHERE schemaname = 'public';

Check database connection permissions​

Verify if a role can connect to a database:

PostgreSQL
SELECT has_database_privilege('<your_role>', 'lakebase', 'CONNECT');

List members of a role​

See all roles that are members of databricks_superuser:

PostgreSQL
SELECT
  pg_get_userbyid(member) as member_role
FROM pg_auth_members
WHERE roleid = (SELECT oid FROM pg_roles WHERE rolname = 'databricks_superuser');

For additional advanced queries to check role inheritance hierarchies, view detailed permission matrices, and find role administrators, see Manage Postgres roles in the Lakebase Public Preview documentation.

Next steps​

Last updated on