Horton

Re: role self-revocation

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, "David G. Johnston" <david.g.johnston@gmail.com>, Stephen Frost <sfrost@snowman.net>, Joshua Brindle <joshua.brindle@crunchydata.com>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-03-09T21:31:00Z
Lists: pgsql-hackers 
Robert Haas <robertmhaas@gmail.com> writes:
> Well, the problem is that as far as I can see, the admin option is an
> optional feature of membership. You can grant someone membership
> without admin option, or with admin option, but you can't grant them
> the admin option without membership, just like you can't purchase an
> upgrade to first class without the underlying plane ticket. What would
> the syntax look even like for this? GRANT foo TO bar WITH ADMIN OPTION
> BUT WITHOUT MEMBERSHIP? Yikes.

I don't think we need syntax to describe it.  As I just said in my
other reply, we have a perfectly good precedent for this already
in ordinary object permissions.  That is: an object owner always,
implicitly, has GRANT OPTION for all the object's privileges, even
if she revoked the corresponding plain privilege from herself.

Yeah, this does mean that we're effectively deciding that the creator
of a role is its owner.  What's the problem with that?

> But do we really have to solve this problem before we can clean up
> this session exception?

I think we need a plan for where we're going.  I don't see "clean up
the session exception" as an end in itself; it's part of re-examining
how all of this ought to work.  I don't say that we have to have a
complete patch right away, only that we need a coherent end goal.

			regards, tom lane

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