PostgreSQL drop role fails because of default privileges

Taken from the PostgreSQL documentation on ALTER DEFAULT PRIVILEGES, Notes section:

If you wish to drop a role for which the default privileges have been altered, it is necessary to reverse the changes in its default privileges or use DROP OWNED BY to get rid of the default privileges entry for the role.

Another worthy mention from the documentation regarding DROP OWNED BY in this case is also that

Because DROP OWNED only affects the objects in the current database, it is usually necessary to execute this command in each database that contains objects owned by a role that is to be removed.

Therefore, your mileage may vary, meaning that you may have to issue the statement in more DBs.

Having received the same messages as mentioned in the question, I’ve tried out the DROP OWNED BY statement and it worked. Hope this helps!

Leave a Comment