Transfer Postgres object ownership

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.

Prerequisites

• The target Azure 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 Azure Databricks group. See Create an OAuth role for Azure Databricks identities to add a Postgres role for the group.

Transfer ownership of a single object

1. Connect as the <current_owner> role (the role that currently owns the table), then create a temporary shared ownership role:

   CREATE ROLE temp_table_owners NOLOGIN;
   

2. Grant schema permissions to the temporary role:

   GRANT USAGE, CREATE ON SCHEMA public TO temp_table_owners;
   

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

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

4. Transfer ownership of the table to the temporary role:

   ALTER TABLE <target_table> OWNER TO temp_table_owners;
   

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

6. Connect as the <databricks_group_name> role:

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

7. Transfer the table to the Azure Databricks group role:

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

8. Verify the transfer:

   \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.

1. Connect as the <current_owner> role, then create a temporary shared ownership role:

   CREATE ROLE temp_table_owners NOLOGIN;
   

2. Grant schema permissions to the temporary role:

   GRANT USAGE, CREATE ON SCHEMA public TO temp_table_owners;
   

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

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

4. Reassign all objects owned by the current owner to the temporary role:

   REASSIGN OWNED BY "<current_owner>" TO temp_table_owners;
   

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

6. Connect as the <databricks_group_name> role:

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

7. Reassign all objects from the temporary role to the Azure Databricks group:

   REASSIGN OWNED BY temp_table_owners TO "<databricks_group_name>";
   

Next steps

• Manage database permissions
• Create Postgres roles
• About authentication