IN POSTGRESSQL, HOW TO DELETE A USER OR ROLE 

Preamble

At first sight, you might think that DROP ROLE (or DROP USER, which is the same) is a straightforward issue. But it’s not always the case. As a result, I thought it might be a good idea to show you the problems and solutions.

Why is there no DROP ROLE … CASCADE?

PostgreSQL keeps track of object dependencies in the system catalogs pg depend and pg shdepend.When one item is deleted, other related objects are occasionally also deleted automatically. For instance, deleting a table also deletes all of its triggers and indexes. In some situations, dropping an item with dependencies results in an error. For instance, you cannot remove a schema that still has other objects in it.

The CASCADE option is typically used to automatically discard all dependant items. For instance, the command “DROP SCHEMA schema name CASCADE” will attempt to delete every item in the schema. Therefore, the lack of a CASCADE option for DROP ROLE is unexpected. What is the cause right now? Think on these details:

• While a SQL statement can only effect the objects in the database to which you are connected, roles can own objects and have permission on objects in several databases.
• Therefore, PostgreSQL cannot ensure that a single query will delete all objects that depend on a role. Therefore, a CASCADE option for DROP ROLE that can remove all dependant items is not possible.

How can you avoid trouble with DROP ROLE?

Never dropping a role that has rights or owns objects is the best course of action. If you utilize “group roles” to convey ownership and permissions, and if you make sure that roles that might be discarded are members of these group roles, achieving this goal is simple. In this manner, all privileges are inherited by the roles, but you may always remove them. For instance, you might have a position called “readonly” that has read access to everything. If you momentarily require a role with read access, you just add it to readonly.

You can avoid giving the same rights to several roles when utilizing group roles because of this. That not only takes too much time and is prone to mistakes, but it also makes the objects’ “access control lists” longer.

The big helpers: DROP OWNED BY and REASSIGN OWNED BY

PostgreSQL’s DROP OWNED BY utility command deletes everything in the active database that is owned by a role. Also, it takes away all of the permissions that role had in the current database. Therefore, by opening a connection to each database in turn and entering “DROP OWNED BY role name,” you can remove the majority of objects that are dependent on a role.

The fact that some of the items DROP OWNED BY wants to drop may be dependent on objects owned by other roles, preventing PostgreSQL from dropping them, means that this could still fail. Then, to have the DROP cascade to these dependant items, use “DROP OWNED BY role name CASCADE”.

Altering the objects’ ownership to a different role is an alternative to dumping them. This can be accomplished in bulk using REASSIGN OWNED BY. You must use REASSIGN OWNED BY in all databases where the role you want to get rid of owns objects, because, as we’ve already said, this only affects objects in the current database. You might wish to use DROP OWNED BY after REASSIGN OWNED BY to cancel the role’s permissions because REASSIGN OWNED BY does not alter or remove those permissions.

Objects left behind by DROP OWNED BY

Only two types of items, neither of which is part of any database, may be left behind by DROP OWNED BY:

• Databases that belong to the role will be deleted.
  tables owned by the role that will be deleted
• The databases can be easily deleted by connecting to Postgres, dropping them, or changing their ownership.

Tablespaces present a greater challenge since you cannot delete a tablespace unless no more objects are present.

Since objects from different databases could be in a tablespace, this is similar to how roles work. As a result, the DROP TABLESPACE option does not have a CASCADE option. In reality, you’ll probably end up giving a separate role ownership of the tablespace.

But I still get an error if I try to drop the role “postgres”!

Each PostgreSQL cluster has a unique role, which is the bootstrap superuser that is made during initdb. That role typically goes by the name “Postgres,” but it always has the object ID 10. You receive the following error messages if you try to drop or use DROP OWNED BY and REASSIGN OWNED BY on it:

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

There is no way to get rid of the bootstrap superuser because it is always required. Simply use ALTER ROLE… RENAME TO to change the user’s name if you don’t like it. In PostgreSQL, object names are merely tags that you can modify at any time.

Conclusion

Even if a role (user) has many dependencies, getting rid of it is not difficult with DROP OWNED BY and REASSIGN OWNED BY. Even better would be to stop giving permissions to roles that could be removed, so that the problem doesn’t even come up. Use group roles to make this happen!

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​