PostgreSQL – REASSIGN OWNED
The REASSIGN OWNED command is used to reassign ownership of all objects owned by a specific role (user or role) to another role. This can be useful when you need to transfer ownership of many objects from one user to another or from one role to another.
The basic syntax for REASSIGN OWNED is as follows:
REASSIGN OWNED BY old_role TO new_role;old_role: The role that currently owns the objects you want to reassign.new_role: The role to which you want to transfer ownership of the objects.
For example, if you want to transfer ownership of all objects owned by the user old_owner to the user new_owner, you would execute:
REASSIGN OWNED BY old_owner TO new_owner;This command will transfer ownership of all objects owned by old_owner to new_owner, including tables, sequences, views, functions, etc.
The old_role should have the necessary privileges to execute REASSIGN OWNED, which typically includes being a superuser or having the GRANT OPTION on the objects.
The new_role should have the appropriate privileges and access to the objects after the ownership transfer. You may need to grant permissions to the new_role if necessary.
REASSIGN OWNED only transfers ownership of objects directly owned by the specified old_role. It doesn’t transfer ownership of objects within schemas owned by old_role. You need to handle schema ownership separately if needed.
Be cautious when using this command, especially in a production environment, as it can have significant implications for the security and management of your database objects. Always make sure you have backups and have thoroughly tested the ownership transfer process in a non-production environment before applying it in production.