PostgreSQL: ERROR: permission denied to reassign objects
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