Cannot Drop Postgresql Role. Error: 'Cannot Be Dropped Because Some Objects Depend on It'

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;

Run both commands in every database of the same cluster where the role owns anything or has any privileges!

And finally:

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. Then DROP OWNED will (also) drop all objects 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

ERROR: role cannot be dropped because some objects depend on it

There is no easy way.

Some commands save you work: REASSIGN OWNED, REVOKE ALL ON ALL ... IN SCHEMA ....
But eventually, you will have to remove all these dependencies before you can drop the role.

Don't forget that single a role can own objects / have permissions in many databases.

Why role cannot be dropped because some objects depend on it

Encountered the same problem, and managed to solve it using the following SQL

This should remove all the rights before removing the user:

REASSIGN OWNED BY <user> TO <other-user>;

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON TABLES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON SEQUENCES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON FUNCTIONS FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ROUTINES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON TYPES FROM <user>;

REVOKE GRANT OPTION FOR ALL PRIVILEGES ON SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA <schema> FROM <user>;

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON TABLES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON SEQUENCES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON FUNCTIONS FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON ROUTINES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON TYPES FROM <user>;

REVOKE ALL PRIVILEGES ON SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA <schema> FROM <user>;

DROP ROLE <user>;

This first assigns all database objects to another user, revokes all the privileges, including default privileges, before finally dropping the role itself.

Dropping role in PostgresSQL

According to your error message, there are some default privileges set for the role. Remove them with

ALTER DEFAULT PRIVILEGES FOR ROLE rohit IN SCHEMA public
   REVOKE ALL ON TABLES FROM readonly;

Drop a role with privileges

I need to drop this role.

Run this in every database of the same cluster where the role might own anything or have any granted privileges:

REASSIGN OWNED BY some_role_name TO postgres;
DROP OWNED BY some_role_name;

postgres being the default superuser, you can pick any other. It is going to own objects currently owned by the old role. Immediately after REASSIGN OWNED, there are no objects left that would be owned by the same user. It may seem unintuitive to run DROP OWNED. The wording of the command is misleading, since it also revokes all privileges and default privileges for the role in the same database. The manual:

DROP OWNED drops all the objects within the current database that are owned by one of the specified roles. Any privileges granted to the given roles on objects in the current database and on shared objects (databases, tablespaces) will also be revoked.

Bold emphasis mine.

You still have to execute it in every single database where the role owns anything or has any granted privileges. The manual:

Because REASSIGN OWNED does not affect objects within other databases, it is usually necessary to execute this command in each database that contains objects owned by a role that is to be removed.

Finally, run (once):

DROP role some_role_name;

Roles are stored in a cluster-wide system catalog, while ownership and privileges on objects are stored in database-local system catalogs.

Detailed explanation in this related answer:

• Find objects linked to a PostgreSQL role

There is a related page in the manual with instructions.

Full automation

There is no single command to do it all. But you can let Postgres generate a complete psql script for you.

Dependencies for roles are stored in the system catalog pg_shdepend:

This information allows PostgreSQL to ensure that those objects are unreferenced before attempting to delete them.

Since we (potentially) need to connect to different databases, we need a combination of psql meta-commands (\c my_database) and SQL DDL commands as shown above. Create this function somewhere in your DB cluster once:

CREATE OR REPLACE FUNCTION f_generate_ddl_to_remove_role(dead_role_walking regrole)
  RETURNS text
  LANGUAGE sql AS
$func$
SELECT concat_ws(
   E'\n'
 ,(SELECT string_agg(format(E'\\c %I\nREASSIGN OWNED BY %2$s TO postgres; DROP OWNED BY %2$s;'
                          , d.datname, dead_role_walking)
                   , E'\n')
   FROM  (
      SELECT DISTINCT dbid
      FROM   pg_shdepend
      WHERE  refobjid = dead_role_walking
      ) s
   JOIN   pg_database d ON d.oid = s.dbid)
 , format(E'DROP role %s;\n', dead_role_walking)
   )
$func$;

Call:

SELECT f_generate_ddl_to_remove_role('some_role_name');

Produces a string like:

\c my_db1
REASSIGN OWNED BY some_role_name TO postgres; DROP OWNED BY some_role_name;
\c my_db2
REASSIGN OWNED BY some_role_name TO postgres; DROP OWNED BY some_role_name;
DROP role some_role_name;

Or, if the role does not own anything and has no privileges, just:

DROP role some_role_name;

If you provide a non-existing role name, you get an error.

Copy the string (without enclosing single quotes) to a psql session opened with a superuser like postgres. Or concatenate a bash script with it. All done.

There are several related answers with more explanation for dynamic SQL:

• https://stackoverflow.com/search?q=%5Bplpgsql%5D+%5Bdynamic-sql%5D+string_agg

How to drop user in postgres if it has depending objects

Before dropping the user you can run :

REASSIGN OWNED BY vantaa TO <newuser>

you could just reassign to postgres if you don't know who to reassign that to ...

REASSIGN OWNED BY vantaa TO postgres;

How to drop role from database

After you ran REASSIGN ALL, it is probably enough to run

DROP OWNED BY admin;

to get rid of the privileges granted to the role. Then you should be able to drop it.

Read this for more background information.

---

Related Topics