Skip to main content
Last updated on

Manage database permissions

Grant database permissions to Postgres roles in your Lakebase project using standard SQL GRANT commands. Permissions control what databases, schemas, and tables a role can access.

note

This page covers database permissions (Postgres GRANT commands). For project permissions (infrastructure management), see Project permissions. For a tutorial on setting up both, see Tutorial: Grant project and database access to a new user.

Prerequisites​

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

Pre-created roles and default permissions​

When you create a 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

—

✅

✅

—

✅

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). Also 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 existing 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;

Grant permissions on future tables in a schema:

SQL
-- Grants permissions on future tables created by the current user.
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO role_name;

-- Grants permissions on future tables created by table_creator_role_name.
-- Note that only members of table_creator_role_name can run this command.
ALTER DEFAULT PRIVILEGES FOR ROLE table_creator_role_name 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

To transfer ownership of Postgres objects to a Databricks group role, see Transfer Postgres object ownership.

Check Postgres permissions​

Use these SQL queries to check permissions and role memberships in your 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>', 'databricks_postgres', '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 project permissions.

Additional resources​

On this page