Managing Object Ownership in PostgreSQL Migrations

This content originally appeared on DEV Community and was authored by whchi

Migrating a PostgreSQL database involves more than just copying data and schema—it also requires careful handling of ownership and privileges. Failure to properly manage these aspects can lead to permission errors, particularly when roles need to ALTER, TRUNCATE, or DROP objects. This guide outlines solutions to ensure a smooth migration process with proper object ownership.

“Owner” in PostgreSQL

In PostgreSQL, every database object—such as tables, views, sequences, functions, and even schemas—is owned by a role. The owner is the only role that can perform certain high-level operations on the object unless privileges are explicitly reassigned.

PostgreSQL roles are essentially accounts, and they can either be:

Superusers: These are roles with unrestricted access. They can bypass ownership rules but should be used cautiously.
Non-superuser roles: These require explicit ownership or privileges to modify objects.

For production environments, it’s a best practice to delegate ownership to a specific application role and avoid using the default superuser for schema management or migrations.

Why Ownership Matters in Migration

When you dump and restore a database (e.g., using pg_dump and pg_restore), all objects retain their original ownership unless explicitly changed. If the original owner doesn’t exist or isn’t appropriate in the target environment, you’ll encounter permission issues.

Common symptoms of incorrect ownership:

ERROR: must be owner of table
Inability to run schema-altering migrations (e.g., ALTER TABLE, DROP, etc.)
Unexpected behavior in CI/CD pipelines or database initialization scripts

Solutions For Handling Ownership in Migration

1. use `pg_dump` & `pg_restore` correctly

Recommended when migrating to a different environment or when role structures differ between source and target databases.

# Export the database without OWNER and GRANT statements
pg_dump --no-owner --no-privileges -Fc -f db.dump your_db
# Restore into a new database as the desired application role
pg_restore --role=your_app_role -d your_new_db db.dump

This ensures that all objects are created under your_app_role without retaining the original roles or grants from the source database.

2. Set Owner During Database Creation

When provisioning a new database, you can set the owner from the beginning:

CREATE DATABASE your_new_db OWNER your_owner;
\c your_new_db
SET ROLE your_owner;
CREATE SCHEMA my_schema;

This ensures all subsequently created objects inherit the correct owner.

3. Use a One-Time Ownership Migration Script

If ownership needs to be updated after initialization, use a PL/pgSQL block to update all object types in one go:

-- The following script automates ownership reassignment for all tables in a database, ensuring consistency across schemas."

DO $$
DECLARE
    rec RECORD;
BEGIN
    -- Alter tables
    FOR rec IN 
        SELECT n.nspname, c.relname 
        FROM pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        WHERE c.relkind = 'r'
          AND n.nspname NOT LIKE 'pg_%' 
          AND n.nspname != 'information_schema'
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.relname) || ' OWNER TO your_new_owner';
        RAISE NOTICE 'Changed owner of table %.% to your_new_owner', rec.nspname, rec.relname;
    END LOOP;

    -- Repeat for sequences, views, enums, domains, functions, schemas, and other objects you want
END $$;

This approach ensures all relevant objects are properly reassigned to the desired role, preventing future permission issues.

4. Use `GRANT` When Ownership Change Isn’t Required

You can delegate permissions via GRANT, but this does not allow operations like DROP, ALTER, or TRUNCATE. It’s a more limited but safer approach for read/write access

-- schema
GRANT ALL ON SCHEMA my_schema TO new_role;
-- table
GRANT ALL ON TABLE my_schema.my_table TO new_role;
GRANT ALL ON ALL TABLES IN SCHEMA my_schema TO new_role;
-- sequence
GRANT ALL ON SEQUENCE my_schema.my_sequence TO new_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA my_schema TO new_role;
-- view
GRANT ALL ON my_schema.my_view TO new_role;
-- enum
GRANT ALL ON TYPE my_schema.my_enum TO new_role;
-- function
GRANT ALL ON FUNCTION my_schema.my_function() TO new_role;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA my_schema TO new_role;

Use GRANT when you want multiple roles to collaborate without transferring ownership.

Final Advice

Plan ownership early: It’s easier to assign correct owners during initialization than to fix them later.
Avoid superuser for app logic: Delegate to application-specific roles with scoped permissions.
Automate ownership reassignment: Use PL/pgSQL to enforce consistency in your CI/CD pipeline or database bootstrap scripts.
Audit before migration: Run queries to list current owners and identify mismatches before migrating.

By handling ownership carefully, you ensure your PostgreSQL migrations remain predictable, secure, and maintainable.