Because roles can own database objects and can hold privileges to access other objects, dropping a role is often not just a matter of a quick DROP ROLE. Any objects owned by the role must first be dropped or reassigned to other owners; and any permissions granted to the role must be revoked.
Ownership of objects can be transferred one at a time using
ALTER commands, for example:
ALTER TABLE bobs_table OWNER TO alice;
Alternatively, the REASSIGN OWNED command can be used to
reassign ownership of all objects owned by the role-to-be-dropped
to a single other role. Because
OWNED cannot access objects in other databases, it is
necessary to run it in each database that contains objects owned
by the role. (Note that the first such
REASSIGN OWNED will change the ownership of any
shared-across-databases objects, that is databases or
tablespaces, that are owned by the role-to-be-dropped.)
Once any valuable objects have been transferred to new owners,
any remaining objects owned by the role-to-be-dropped can be
dropped with the DROP
OWNED command. Again, this command cannot access
objects in other databases, so it is necessary to run it in each
database that contains objects owned by the role. Also,
DROP OWNED will not drop entire
databases or tablespaces, so it is necessary to do that manually
if the role owns any databases or tablespaces that have not been
transferred to new owners.
DROP OWNED also takes care of
removing any privileges granted to the target role for objects
that do not belong to it. Because
OWNED does not touch such objects, it's typically
necessary to run both
DROP OWNED (in that order!) to
fully remove the dependencies of a role to be dropped.
In short then, the most general recipe for removing a role that has been used to own objects is:
REASSIGN OWNED BY doomed_role TO successor_role; DROP OWNED BY doomed_role; -- repeat the above commands in each database of the cluster DROP ROLE doomed_role;
When not all owned objects are to be transferred to the same successor owner, it's best to handle the exceptions manually and then perform the above steps to mop up.
DROP ROLE is attempted while
dependent objects still remain, it will issue messages
identifying which objects need to be reassigned or dropped.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.