When reassigning all database objects to a new owner in PostgreSQL you might get this error:

mydb=> REASSIGN OWNED BY admin TO new_admin;
ERROR:  permission denied to reassign objects

This happens because the role used to perform the above command is not a superuser.

The easiest way to successfully execute the reassignment is run the reassignment command with a user with “superuser” attribute.

In case you don’t have access to a user with “superuser” attribute, you can do the following:

1. Create a new role

 mydb=> CREATE ROLE change_owner LOGIN;
CREATE ROLE

2. Make both the old owner and the new owner roles members of the newly created role

mydb=> GRANT admin TO change_owner;
GRANT ROLE
mydb=> GRANT new_admin TO change_owner;
GRANT ROLE

3. Logout from psql and login using the new role

$ psql -U change_owner mydb
mydb=>

4. Execute the reassignment

mydb=> REASSIGN OWNED BY admin TO new_admin
REASSIGN OWNED
Share!