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

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

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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.