Thread: Re:   Re: Re: Revoke Connect Privilege from Database not working

Hi,
here is a full reproducer. Also revoking with the granted by clause does not work.

#clean initialization

postgres=# create database testdb owner postgres;

CREATE DATABASE

postgres=# create user test_admin createrole;

CREATE ROLE

postgres=# alter user test_admin with password 'test1234';

ALTER ROLE

postgres=# grant connect on database testdb to test_admin with grant option;

GRANT

#create user and grant connect privilege with test_admin

postgres=# set role test_admin;

SET

postgres=> create user test_user password 'testuserpw';

CREATE ROLE

postgres=> grant connect on database testdb to test_user;

GRANT

#generate the failure by granting test_admin superuser privileges

postgres=> reset role;

RESET

postgres=# alter user test_admin superuser;

ALTER ROLE

postgres=# set role test_admin;

SET

postgres=# revoke connect on database testdb from test_user;

REVOKE

postgres=# drop user test_user;

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

DETAIL:  privileges for database testdb

#test also with "granted by clause"

postgres=# revoke connect on database testdb from test_user granted by "test_admin";

REVOKE

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On master, confirmed that after this command the privilege:
> test_user=c/test_admin (on database testdb) still exists.  That seems like
> a bug. Its at least a POLA violation and I cannot figure out how to read
> the revoke reference page in a way that explains it.

I believe what's going on there is explained by the rule that
"grants and revokes done by a superuser are done as if issued
by the object owner".  So here, what would be revoked is
test_user=c/postgres, which isn't the privilege at issue.
Include GRANTED BY in the REVOKE to override the default
choice of grantor.