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: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "Ing. Marijo Kristo" <marijo.kristo@icloud.com>, PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Date: 2025-04-07T16:22:45Z
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 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.