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.

Note

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

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>";

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.

Next steps

  • Last updated on