Transfer Postgres object ownership

info

Lakebase Autoscaling is the latest version of Lakebase, with autoscaling compute, scale-to-zero, branching, and instant restore. For supported regions, see Region availability. If you are a Lakebase Provisioned user, see Lakebase Provisioned.

In Lakebase, use a temporary shared role as an intermediate step to transfer Postgres object ownership between roles. You can't do this directly with a standard ALTER TABLE ... OWNER TO command.

note

This page covers transferring ownership to a Databricks group role. To grant access to objects without changing ownership, see Manage database permissions.

Prerequisites

The target Databricks group exists in your workspace. To create a group: In Lakehouse, go to Settings > Identity and access > Groups > Add Group.
The current object owner is a member of the target group. To add a member: In Lakehouse, go to Settings > Identity and access > Groups > your group > Add member.
A Postgres role exists for the Databricks group. See Create an OAuth role for Databricks identities to add a Postgres role for the group.

Transfer ownership of a single object

Connect as the <current_owner> role (the role that currently owns the table), then create a temporary shared ownership role:
SQL
```
CREATE ROLE temp_table_owners NOLOGIN;
```

Grant schema permissions to the temporary role:

SQL
GRANT USAGE, CREATE ON SCHEMA public TO temp_table_owners;

Grant membership in the temporary role to the current owner and the target group:

SQL
GRANT temp_table_owners TO "<current_owner>";
GRANT temp_table_owners TO "<databricks_group_name>";

Transfer ownership of the table to the temporary role:

SQL
ALTER TABLE <target_table> OWNER TO temp_table_owners;

In your Lakebase project, click Connect, select the <databricks_group_name> role in the Roles drop-down, and click Copy OAuth Token.

Connect as the <databricks_group_name> role:

Bash
export PGPASSWORD='<oauth_token>'
psql -h <lakebase_hostname> -p 5432 \
  -d <database_name> \
  -U "<databricks_group_name>" \
  --set=sslmode=require

Transfer the table to the Databricks group role:

SQL
ALTER TABLE <target_table> OWNER TO "<databricks_group_name>";

Verify the transfer:
Text
```
\dt
```
The Owner column for <target_table> shows <databricks_group_name>.

Transfer ownership of multiple objects

REASSIGN OWNED transfers all objects owned by a role, including tables, views, materialized views, sequences, functions, schemas, and types.

Connect as the <current_owner> role, then create a temporary shared ownership role:
SQL
```
CREATE ROLE temp_table_owners NOLOGIN;
```

Grant schema permissions to the temporary role:

SQL
GRANT USAGE, CREATE ON SCHEMA public TO temp_table_owners;

Grant membership in the temporary role to the current owner and the target group:

SQL
GRANT temp_table_owners TO "<current_owner>";
GRANT temp_table_owners TO "<databricks_group_name>";

Reassign all objects owned by the current owner to the temporary role:
SQL
```
REASSIGN OWNED BY "<current_owner>" TO temp_table_owners;
```
In your Lakebase project, click Connect, select the <databricks_group_name> role in the Roles drop-down, and click Copy OAuth Token.

Connect as the <databricks_group_name> role:

Bash
export PGPASSWORD='<oauth_token>'
psql -h <lakebase_hostname> -p 5432 \
  -d <database_name> \
  -U "<databricks_group_name>" \
  --set=sslmode=require

Reassign all objects from the temporary role to the Databricks group:
SQL
```
REASSIGN OWNED BY temp_table_owners TO "<databricks_group_name>";
```

note

REASSIGN OWNED has the following behavior:

Runs in the current database context. Run it in each database where you must transfer ownership.
Reassigns ownership only. It does not change existing GRANT permissions or default privileges.

Prerequisites​

Transfer ownership of a single object​

Transfer ownership of multiple objects​

Next steps​

Prerequisites

Transfer ownership of a single object

Transfer ownership of multiple objects

Next steps