Cannot drop a role that is granted to connect database

Question

I am using PostgreSQL 10.4 and I found a strange behavior.

If we create an role and grant it to CONNECT the database:

CREATE ROLE dummy;
GRANT CONNECT ON DATABASE test TO dummy;

Then we cannot drop this role, even if it owns no object at all, this command:

DROP ROLE dummy;

Raises:

ERROR: role "dummy" cannot be dropped because some objects depend on it
SQL state: 2BP01
Detail: privileges for database test

Documentation is a bit misleading:

Class 2B — Dependent Privilege Descriptors Still Exist

2B000 dependent_privilege_descriptors_still_exist

2BP01 dependent_objects_still_exist

It says dependent objects still exist, but it seems there are no objects dependent to this specific role, it owns nothing on the database.

Anyway, if we revoke the CONNECT privilege, then role can be dropped:

REVOKE CONNECT ON DATABASE test FROM dummy;
DROP ROLE dummy;

I just checked the behavior also exists on PostgreSQL 9.5. I feel it a bit strange and I cannot understand why this specific privilege makes dropping role fails.

Additional observations

This is really blocking, because we can neither reassign this object:

REASSIGN OWNED BY dummy TO postgres;

Nor drop the object:

DROP OWNED BY dummy;

Both raise related errors:

ERROR: permission denied to reassign objects
SQL state: 42501

ERROR: permission denied to drop objects
SQL state: 42501

As @RaymondNijland pointed out, this must be because the CONNECT privileges is viewed as a role dependent object. The following query:

WITH
R AS (SELECT * FROM pg_roles WHERE rolname = 'dummy')
SELECT
    D.*
FROM
    R, pg_shdepend AS D
WHERE
    refobjid = R.oid;

Returns a single row when CONNECT is granted:

"dbid";"classid";"objid";"objsubid";"refclassid";"refobjid";"deptype"
0;1262;27961;0;1260;27966;"a"

And no row at all when the privilege is revoked. This at least explain why we cannot reassign the object.

About the Dependency Type, the documentation states:

SHARED_DEPENDENCY_ACL (a)

The referenced object (which must be a role) is mentioned in the ACL (access control list, i.e., privileges list) of the dependent object. (A SHARED_DEPENDENCY_ACL entry is not made for the owner of the object, since the owner will have a SHARED_DEPENDENCY_OWNER entry anyway.)

But I have not enough insight to understand it clearly.

My question are:

• Do Postgres always require to revoke privileges before dropping a role?
• If not, why this specific privilege behaves like this?

Answer 1

There are 2 ways to run REASSIGN OWNED and DROP OWNED. The 1st way is with a superuser and the 2nd way is with a normal user.

<The 1st way>:

For example, first, log in to apple database with any superuser e.g., postgres. *My answer explains how to create a superuser. and my answer explains how to make a user a superuser:

psql -U postgres apple

Finally, you can run REASSIGN OWNED and DROP OWNED as shown below:

REASSIGN OWNED BY david TO peter

Or:

REASSIGN OWNED BY peter TO david

Or:

DROP OWNED BY david;

Or:

DROP OWNED BY peter;

<The 2nd way>:

For example, first, log in to any database e.g., postgres database with any superuser e.g., postgres as shown below:

psql -U postgres

Then, create the user john as shown below:

CREATE ROLE john WITH LOGIN PASSWORD 'banana';

Then, grant the users david and peter to the user john, exit as shown below:

GRANT david TO john;
GRANT peter TO john;
\q

Then, log in to apple database with the user john as shown below:

psql -U john apple

Finally, you can run REASSIGN OWNED and DROP OWNED as shown below:

REASSIGN OWNED BY david TO peter

Or:

REASSIGN OWNED BY peter TO david

Or:

DROP OWNED BY david;

Or:

DROP OWNED BY peter;

Answer 2

There are some very unintuitive permission requirements when using REASSIGN.

I have found that when a superuser account is not available (as in the case of RDS or Cloud SQL) I have to grant the target role to my current role in order to reassign or drop owned objects from the target role. For instance, if my active user is postsgres, and I'm trying to remove user_a:

> DROP OWNED BY user_a
ERROR:  permission denied to drop objects
> GRANT user_a TO postgres;
GRANT ROLE
> DROP OWNED BY user_a;
DROP OWNED

Now, it becomes a little trickier if user_a happens to be a member of postgres, especially if it happens to inherit that membership through some other role, let's call it schema_admin...

> DROP OWNED BY user_a
ERROR:  permission denied to drop objects
> GRANT user_a TO postgres;
ERROR:  role "user_a" is a member of role "postgres"

-- Alright, let's try to revoke it...
> REVOKE postgres FROM user_a;
REVOKE ROLE
> GRANT user_a TO postgres;
ERROR:  role "user_a" is a member of role "postgres"

-- It's still a member through the inherited grant - trying to revoke again doesn't work:
> REVOKE postgres FROM user_a;
WARNING:  role "user_a" is not a member of role "postgres"
REVOKE ROLE

-- So you have to identify the role it's inheriting from, and revoke that:
> REVOKE schema_admin FROM user_a;
REVOKE ROLE
> GRANT user_a TO postgres;
GRANT ROLE

-- Now just to be safe, I'll reassign owned objects before actually dropping everything:
> REASSIGN OWNED BY user_a TO postgres;
REASSIGN OWNED
> DROP OWNED BY user_a;
DROP OWNED
> DROP ROLE user_a;
DROP ROLE;

Voila!

Note: There is another widely-referenced and effective answer here: https://sysadmintips.com/services/databases/postgresql-error-permission-denied-to-reassign-objects/ which works great, as long as you are able to create and log in as a new temporary user. However, in some contexts, that is a problem in itself (and then you also have the extra cleanup to handle of removing that temporary role when you're done), so I tried to avoid that here.