Horton

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

David G. Johnston <david.g.johnston@gmail.com>

View thread
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: "Ing. Marijo Kristo" <marijo.kristo@icloud.com>, PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Date: 2025-04-07T15:37:43Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow choosing specific grantors via GRANT/REVOKE ... GRANTED BY.

On Mon, Apr 7, 2025 at 7:27 AM Ing. Marijo Kristo <marijo.kristo@icloud.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