How to fix ERROR 42501: must be owner of table in PostgreSQL

The most straightforward solution is to restore the dump without attempting to set ownership. This tells pg_restore to skip all ownership-related commands:

pg_restore --no-owner -d target_database backup.dump

For additional safety, also skip privilege statements:

pg_restore --no-owner --no-acl -d target_database backup.dump

The restored objects will be owned by the user executing pg_restore, which is typically the correct behavior for migrations.

If you are creating a new dump (not working with an existing one), exclude ownership and privilege information from the start:

pg_dump --no-owner --no-privileges -Fc -f backup_clean.dump source_database

Then restore normally:

pg_restore -d target_database backup_clean.dump

This approach avoids permission issues entirely by excluding the problematic statements during export.

If you need specific table ownership, alter it after a successful restore:

First, restore with --no-owner:
pg_restore --no-owner -d target_database backup.dump

Then change ownership using psql:
psql -d target_database -c "ALTER TABLE table_name OWNER TO new_owner;"

For all tables in a schema:
psql -d target_database -c "REASSIGN OWNED BY old_owner TO new_owner;"

You can generate a script to change ownership for all tables:

psql -d target_database -t -c "SELECT 'ALTER TABLE ' || tablename || ' OWNER TO app_user;' FROM pg_tables WHERE schemaname = 'public'"

Run these ALTER commands after restore completes.

Some versions of PostgreSQL support the --role flag to specify which role should execute the restore:

pg_restore --role=superuser_role -d target_database backup.dump

Note: This only works if the specified role exists and the connecting user has permission to assume it. This is less commonly used than --no-owner, but can be useful in specific scenarios.

If restoring to AWS RDS, Azure Database, or Google Cloud SQL, the original roles may not exist:

Connect as admin to target database and create the role if it does not exist:

CREATE ROLE original_owner WITH LOGIN PASSWORD 'temporary_password';
GRANT ALL PRIVILEGES ON DATABASE target_database TO original_owner;

However, most cloud providers restrict superuser access. In this case, use --no-owner and reassign ownership after restore:

pg_restore --no-owner -d target_database backup.dump

Then reassign to application user:

psql -d target_database -c "REASSIGN OWNED BY restore_user TO app_user;"

After restore completes, verify that tables are owned correctly:

Check table ownership:
SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename;

Check schema ownership:
SELECT nspname, nspowner::regrole::text FROM pg_namespace WHERE nspname = 'public';

Check if user can access tables:
SELECT count(*) FROM table_name;

If permissions are still incorrect, use ALTER TABLE statements to fix ownership:

ALTER TABLE problematic_table OWNER TO correct_owner;