How do you preserve PostgreSQL table ownership when an app resource is recreated?

[A_Motia]

Hi Community members,

I'm looking into a potentially tricky deployment scenario involving Databricks Apps, Lakebase, and Databricks Asset Bundles (DABs).

Consider an application that uses Lakebase as its PostgreSQL backend. The app's service principal owns some application tables, while a separate service principal running a synchronization job owns other tables.

If a deployment or configuration change removes and recreates the app's Lakebase resource, what is the recommended way to preserve database roles, grants, and table ownership?

A few specific questions:

• Is there a supported way to restore ownership of existing tables to the original service principal if its PostgreSQL role is removed and recreated?
• What is the recommended pattern for managing application schema migrations with Alembic when different service principals own application tables and synchronized tables?
• Should database roles, grants, and ownership be managed separately from the app's DAB resource lifecycle to prevent deployment changes from affecting existing data?
• What recovery strategy would you recommend if the app reconnects successfully but subsequently loses the privileges needed to alter or drop existing tables?

I'm particularly interested in production-safe approaches that avoid manual database repair after deployments.

Thanks!

[anna78smith]

To safely manage Lakebase resources in Databricks Apps via DABs, decouple database roles, grants, and table ownership from the ephemeral app resource lifecycle by managing them through external tools like Terraform or dedicated bootstrap scripts. For Alembic migrations involving multiple service principals, configure PostgreSQL group roles and ALTER DEFAULT PRIVILEGES so that objects created by either principal automatically inherit necessary access rights, and implement an automated post-deployment reconciliation script run by an admin role to instantly restore lost ownership or privileges if a resource recreation occurs.

[SumeshKashyap · Accepted solution]

Adding a concrete version of the pattern above.

Postgres ties ownership to the role's OID, not its name, so a recreated service principal role never gets its old tables back automatically. The fix is not to let individual service principals own anything.

Create a NOLOGIN owner role, have Alembic SET ROLE to it so every table it creates is owned by that role, and grant membership to whichever SP is current. Recovery after a recreate then takes one GRANT, with no ownership repair.

One-time bootstrap (run as an admin role, idempotent)

CREATE EXTENSION IF NOT EXISTS databricks_auth;

SELECT databricks_create_role('<app-sp-application-id>', 'SERVICE_PRINCIPAL');  -- skip if the app resource already created it

DO $$ BEGIN

 IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_owner') THEN

    CREATE ROLE app_owner NOLOGIN;

  END IF;

END $$;

CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION app_owner;

GRANT app_owner TO "<app-sp-application-id>";

-- one-off: move anything the SP already owns

REASSIGN OWNED BY "<app-sp-application-id>" TO app_owner;

Keep the synced tables in their respective schema, owned by the sync SP. If the app only reads them, give it GRANT USAGE + SELECT there. Add ALTER DEFAULT PRIVILEGES FOR ROLE "<sync-sp>" IN SCHEMA synced GRANT SELECT ON TABLES TO app_owner; so tables the sync job creates later are readable too.

Alembic env.py

from sqlalchemy import text

with connectable.connect() as connection:

    connection.execute(text("SET ROLE app_owner"))       # objects created are owned by app_owner

    connection.execute(text("SET search_path TO app"))

    context.configure(connection=connection, target_metadata=target_metadata,

                      version_table_schema="app")       # autogenerate stays out of the synced schema

    with context.begin_transaction():

        context.run_migrations()

Keeping it outside the DAB lifecycle

Put the bootstrap SQL in a job task (or a step in your CD pipeline) that runs after databricks bundle deploy, connecting as a Lakebase admin rather than the app SP. Because every statement is idempotent, running it on every deploy is safe. If the app or its resource is recreated with a new SP, the only change is GRANT app_owner TO "<new-sp-id>". To detect drift, the same job can fail on:

SELECT schemaname, tablename, tableowner FROM pg_tables

WHERE schemaname = 'app' AND tableowner <> 'app_owner';

Docs:

Lakebase Postgres roles https://docs.databricks.com/aws/en/oltp/instances/pg-roles ,

Apps with Lakebase https://docs.databricks.com/aws/en/dev-tools/databricks-apps/lakebase ,

and REASSIGN OWNED https://www.postgresql.org/docs/current/sql-reassign-owned.html