public inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: David G. Johnston <david.g.johnston@gmail.com>
To: Ing. Marijo Kristo <marijo.kristo@icloud.com>
To: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Subject: Re: Re: Re: Revoke Connect Privilege from Database not working
Date: Mon, 7 Apr 2025 08:37:43 -0700
Message-ID: <CAKFQuwbB-ZKtN_p_y5sWa2MrTuy5=pRNPWSj1Ud4HHvTuhb54w@mail.gmail.com> (raw)
In-Reply-To: <d9bf666c-4d11-4196-99a8-b71d01d9ad40@me.com>
References: <CAKFQuwa7m2smqqpgPetw=i8Aj-xqg9Zjc5Z2aX3AUwNh96WnXw@mail.gmail.com>
<d9bf666c-4d11-4196-99a8-b71d01d9ad40@me.com>
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: david.g.johnston@gmail.com, marijo.kristo@icloud.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Re: Re: Re: Revoke Connect Privilege from Database not working
In-Reply-To: <CAKFQuwbB-ZKtN_p_y5sWa2MrTuy5=pRNPWSj1Ud4HHvTuhb54w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox