312 words, 2 min read
When managing databases across environments or rotating credentials, it’s common to need to transfer ownership of all objects in a PostgreSQL database — for example, after creating a new user like app_user. Doing this manually for each table or sequence is tedious, but PostgreSQL provides a clean and efficient way to handle it.
In PostgreSQL, only the owner of an object (or a superuser) can modify or drop it. This means if a migration runs as a different role than the one that owns the tables, you’ll see errors such as:
ERROR: must be owner of table users
To fix this permanently, you can transfer ownership of all objects to the correct role.
Starting from PostgreSQL 15, the easiest way to transfer all database objects from one role to another is…
312 words, 2 min read
When managing databases across environments or rotating credentials, it’s common to need to transfer ownership of all objects in a PostgreSQL database — for example, after creating a new user like app_user. Doing this manually for each table or sequence is tedious, but PostgreSQL provides a clean and efficient way to handle it.
In PostgreSQL, only the owner of an object (or a superuser) can modify or drop it. This means if a migration runs as a different role than the one that owns the tables, you’ll see errors such as:
ERROR: must be owner of table users
To fix this permanently, you can transfer ownership of all objects to the correct role.
Starting from PostgreSQL 15, the easiest way to transfer all database objects from one role to another is using the REASSIGN OWNED command.
First, connect to the target database:
psql -U postgres -d my_database
Then run the following SQL commands:
-- Change database ownership itself
ALTER DATABASE my_database OWNER TO app_user;
-- Reassign all contained objects owned by the old role
REASSIGN OWNED BY old_owner TO app_user;
This changes ownership of all tables, views, sequences, materialized views, and functions that were owned by old_owner within that database.
If you’re unsure who the old owner is, you can quickly check:
SELECT tableowner, tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
Notes and caveats
- You need to be either a superuser or the database owner to reassign ownership.
- The
REASSIGN OWNEDcommand only affects objects in the current database, not globally across multiple databases. - It’s good practice to ensure the new role exists before reassigning:
CREATE ROLE app_user LOGIN PASSWORD 'secure_password';
When ownership mismatches cause migration or schema modification errors, the REASSIGN OWNED command is the clean, supported way to fix them. With just two commands, you can ensure consistent ownership across your PostgreSQL database without manually touching every table.
If this post was enjoyable or useful for you, please share it! If you have comments, questions, or feedback, you can email my personal email. To get new posts, subscribe use the RSS feed.