Horton

Thread

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.

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

    David G. Johnston <david.g.johnston@gmail.com> — 2025-04-07T13:42:28Z

    On Monday, April 7, 2025, Ing. Marijo Kristo <marijo.kristo@icloud.com>
wrote:
>
>
> Seems like a bug to me.
> Can someone else verifiy this ?
>

It would help greatly if you create a reproducer that starts from a clean
install, creates the roles and database, and demonstrates the issue.


> postgres=# \du vault_admin;
>             List of roles
>   Role name  |       Attributes
> -------------+------------------------
> vault_admin | Superuser, Create role
>
> postgres=# set role vault_admin;
>

You are setting role to another role that has superuser which is basically
pointless.

Use “granted by” in your revoke command.  If that works this isn’t a bug.

David J.

  2. Aw:  Re: Re: Revoke Connect Privilege from Database not working

    Ing. Marijo Kristo <marijo.kristo@icloud.com> — 2025-04-07T14:27:41Z

    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 postgres=# drop user test_user; ERROR: role "test_user" cannot be dropped because some objects depend on it DETAIL: privileges for database testdb #fix by removing superuser privilege from test_admin postgres=# reset role; RESET postgres=# alter user test_admin nosuperuser; ALTER ROLE postgres=# set role test_admin; SET postgres=> revoke connect on database testdb from test_user; REVOKE postgres=> drop role test_user; DROP ROLE Best Regards Marijo Kristo David G. Johnston <david.g.johnston@gmail.com> schrieb am 7. Apr. 2025 um 15:42: On Monday, April 7, 2025, Ing. Marijo Kristo < marijo.kristo@icloud.com > wrote: Seems like a bug to me. Can someone else verifiy this ? It would help greatly if you create a reproducer that starts from a clean install, creates the roles and database, and demonstrates the issue. postgres=# \du vault_admin; List of roles Role name | Attributes -------------+---------------- -------- vault_admin | Superuser, Create role postgres=# set role vault_admin; You are setting role to another role that has superuser which is basically pointless. Use “granted by” in your revoke command. If that works this isn’t a bug. David J.

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

    David G. Johnston <david.g.johnston@gmail.com> — 2025-04-07T15:37:43Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-04-07T16:06:17Z

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

IIRC, said rule was invented before we had the GRANTED BY
syntax.  It probably doesn't make as much sense today,
but I'd be very afraid of breaking peoples' work flows
by changing it.

			regards, tom lane

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

    David G. Johnston <david.g.johnston@gmail.com> — 2025-04-07T16:22:45Z

    On Mon, Apr 7, 2025 at 9:06 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

The command in question did include "granted by" which is why this is a
bug.  The explicit granted by specification is being ignored if the
invoking user is a superuser.

revoke connect on database testdb:v
from test_user:v
---------------
granted by test_admin:v;
---^^^^^^^^^

So if we stick with status quo behavior we'd need to write the following
because the ignoring part is a POLA violation:

If a superuser chooses to issue a GRANT or REVOKE command, the command is
performed as though it were issued by the owner of the affected object, and
the granted by clause is ignored.

David J.

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

    Nathan Bossart <nathandbossart@gmail.com> — 2025-11-13T16:47:14Z

    On Mon, Apr 07, 2025 at 09:22:45AM -0700, David G. Johnston wrote:
> On Mon, Apr 7, 2025 at 9:06 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
> 
> The command in question did include "granted by" which is why this is a
> bug.  The explicit granted by specification is being ignored if the
> invoking user is a superuser.

This is admittedly a half-formed idea, but perhaps we could have whatever's
specified in GRANTED BY override select_best_grantor(), like in the
attached patch.  I've no idea if this is the intention of the standard, but
it should at least address the reported issue.  FWIW I recently received an
independent report about the same thing.  

-- 
nathan