Cannot drop PostgreSQL role. Error: `cannot be dropped because some objects depend on it`
DROP USER
(or DROP ROLE
, same thing) cannot proceed while the role still owns anything or has any granted privileges on other objects.
Get rid of all privileges with DROP OWNED
(which isn't too obvious from the wording). The manual:
[...] Any privileges granted to the given roles on objects in the current database and on shared objects (databases, tablespaces) will also be revoked.
So the reliable sequence of commands to drop a role is:
REASSIGN OWNED BY ryan TO postgres; -- or some other trusted role
DROP OWNED BY ryan;
-- repeat both in ALL databases where the role owns anything or has any privileges!
DROP USER ryan;
REASSIGN OWNED
changes ownership for all objects currently owned by the role.DROP OWNED
then only revokes privileges (ownerships out of the way).
Alternatively, you can skip REASSIGN OWNED
to instead drop all object owned by the user. (Are you sure?!)
Related:
- Drop a role with privileges (with a function to generate commands for all relevant DBs)
- Find objects linked to a PostgreSQL role
What worked for me was 1) Connecting to the database
\c mydatabase
2) Reassigning Ownership
REASSIGN OWNED BY ryan TO <newuser>;
Or/and just deleting the object
DROP OWNED BY ryan;
3) Executing REVOKE PRIVILEGES
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM ryan;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM ryan;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM ryan;
4) Dropping the user
DROP USER ryan;
PS: You might not need to execute both Step 2 and 3, just one of the two steps might be usually enough.