© Laurenz Albe 2022
You might, at first glance, believe that DROP ROLE (or DROP USER, which is the same) is a simple matter. However, that’s not always the case. So I thought it might be a good idea to show you the problems involved and how to solve them.
Why is there no DROP ROLE ... CASCADE?
PostgreSQL tracks dependencies between objects in the system catalogs pg_depend and pg_shdepend. Sometimes, dropping an object automatically drops other associated objects: for example, dropping a table will drop all indexes and triggers associated with the table. In other cases, you get an error if you drop an object with dependencies: for example, you can’t drop a schema that still contains other objects.
The usual way to automatically drop all dependent objects is to use the CASCADE option. For example, “DROP SCHEMA schema_name CASCADE” will automatically try to drop all objects in the schema. So it comes as a surprise that there is no CASCADE option for DROP ROLE. Now what is the reason? Consider these facts:
- roles can own objects and have permissions on objects in several databases
- an SQL statement can only affect objects in the database to which you are connected
As a consequence, PostgreSQL cannot guarantee that a single statement will get rid of all objects that depend on a role. So we can’t have a CASCADE option for DROP ROLE that can drop all dependent objects.
How can you avoid trouble with DROP ROLE?
The best thing you can do is to never drop a role that owns objects or has permissions. That’s easy to accomplish, if you use “group roles” to carry permissions and ownership, and if you make sure that roles that might be dropped are members of these group roles. That way, the roles inherit all privileges, and you can still drop them at any time. For example, you could have a role “readonly” that has read permissions on everything, and if you temporarily need a role with read access, you just make that role a member of readonly.
Another advantage of using group roles is that you don’t have to grant the same permissions to several roles. Not only is that cumbersome and error-prone, but it also inflates the “access control lists” on the objects.
The big helpers: DROP OWNED BY and REASSIGN OWNED BY
PostgreSQL has a utility command DROP OWNED BY that drops all objects owned by a role in the current database. It also revokes all permissions granted to the role in the current database. So you can get rid of most objects with dependencies to a role by connecting to all databases in turn and running “DROP OWNED BY role_name” in each of them.
Note that this may still fail: some of the objects that DROP OWNED BY wants to drop might have dependencies to objects owned by other roles, which prevent PostgreSQL from dropping them. In that case, you can use “DROP OWNED BY role_name CASCADE” to have the DROP cascade to these dependent objects.
An alternative to dropping the objects is to change their ownership to a different role. You can do that in bulk with REASSIGN OWNED BY. Again, this only affects objects in the current database, so you have to issue REASSIGN OWNED BY in all databases with objects owned by the role you want to get rid of. Note: REASSIGN OWNED BY does not modify or remove permissions granted to the role, so you may want to follow it with DROP OWNED BY to revoke those permissions.
Objects left behind by DROP OWNED BY
There are only two kinds of objects that might be left behind by DROP OWNED BY, both of which are not part of any database:
- databases owned by the role to be dropped
- tablespaces owned by the role to be dropped
Getting rid of the databases is easy: you connect to database postgres and drop them or change their ownership.
Tablespaces are more difficult: you cannot drop a tablespace unless there are no more objects in that tablespace. This is a similar case to roles, since a tablespace can contain objects from different databases. Consequently, there is also no CASCADE option for DROP TABLESPACE. In practice, you will probably end up changing the ownership of the tablespace to a different role.
But I still get an error if I try to drop the role “postgres”!
There is one special role in each PostgreSQL cluster: the bootstrap superuser created during initdb. Usually that role is called “postgres”, but it always has the object ID 10. If you attempt to drop that role or use DROP OWNED BY and REASSIGN OWNED BY on it, you end up with these error messages:
cannot drop role postgres because it is required by the database system cannot drop objects owned by role postgres because they are required by the database system cannot reassign ownership of objects owned by role postgres because they are required by the database system
You always need the bootstrap superuser, so you can’t remove it. If you don’t like the name of the user, you can simply rename it with ALTER ROLE ... RENAME TO .... Object names are only tags in PostgreSQL and you can change them at any time.
Conclusion
With DROP OWNED BY and REASSIGN OWNED BY it is not difficult to get rid of a role (user), even if it has many dependencies. Still better is to avoid the problem from the start by not granting permissions to roles that might get deleted. Use group roles for that!
If you are interested in tracking permissions in a PostgreSQL database, read more about it here in Hans’ blog: pg_permissions.