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

On Mon, Apr 7, 2025 at 7:27 AM Ing. Marijo Kristo <marijo(dot)kristo(at)icloud(dot)com>
wrote:

> 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
>

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.

David J.

# revokescript.psql
create database testdb:v;
create user test_admin:v createrole;
grant connect on database testdb:v to test_admin:v with grant option;
set role test_admin:v;
create user test_user:v password 'testuserpw';
grant connect on database testdb:v to test_user:v;
reset role;
alter user test_admin:v superuser;
set role test_admin:v;
revoke connect on database testdb:v from test_user:v granted by
test_admin:v;
\l+ testdb:v
drop user test_user:v;

> psql postgres --file revokescript.psql -v v=1