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: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Mark Dilger <mark.dilger@enterprisedb.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-09T22:35:18Z
Lists: pgsql-hackers 
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> So CREATE ROLE will assign ownership of AND membership in the newly created
> role to the session_user

I would NOT have it automatically assign membership in the new role,
even though the SQL spec says so.  We've not done that historically
and it doesn't seem desirable.  In particular, it's *really* not
desirable for a user (role with LOGIN).

> I'm fine with this.  It does introduce an OWNER concept to roles and so at
> minimum we need to add:
> ALTER ROLE foo OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER |
> SESSION_USER }

Agreed.

			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