Horton

Re: pg_auth_members.grantor is bunk

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

View thread View on postgr.es
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>, Tom Lane <tgl@sss.pgh.pa.us>, Mark Dilger <mark.dilger@enterprisedb.com>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-06-24T20:29:47Z
Lists: pgsql-hackers 
On Fri, Jun 24, 2022 at 1:19 PM Robert Haas <robertmhaas@gmail.com> wrote:

> On Mon, Jun 6, 2022 at 7:41 PM Stephen Frost <sfrost@snowman.net> wrote:
> >
> > In terms of how that's then used, yeah, it's during REVOKE because a
> > REVOKE is only able to 'find' role authorization descriptors which match
> > the triple of role revoked, grantee, grantor (though there's a caveat in
> > that the 'grantor' role could be the current role, or the current user).
>
> What is supposed to happen if someone tries to execute DROP ROLE on a
> role that has previously been used as a grantor?
>
> Upthread, I proposed that "drop role baz" should fail here
>

I concur with this.

I think that the grantor owns the grant, and that REASSIGNED OWNED should
be able to move those grants to someone else.

By extension, DROP OWNED should remove them.

David J.

Commits

  1. Make role grant system more consistent with other privileges.

  2. Ensure that pg_auth_members.grantor is always valid.

  3. Remove the ability of a role to administer itself.

  4. Add tests of the CREATEROLE attribute

  5. Replace explicit PIN entries in pg_depend with an OID range test.

  6. Shore up ADMIN OPTION restrictions.

  7. Add pg_has_role() family of privilege inquiry functions modeled after the

  8. Align GRANT/REVOKE behavior more closely with the SQL spec, per discussion