Horton

Re: pg_auth_members.grantor is bunk

Jeff Davis <pgsql@j-davis.com>

View thread View on postgr.es
From: Jeff Davis <pgsql@j-davis.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Jacob Champion <jchampion@timescale.com>, Stephen Frost <sfrost@snowman.net>, "David G. Johnston" <david.g.johnston@gmail.com>, Mark Dilger <mark.dilger@enterprisedb.com>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-09-07T01:27:32Z
Lists: pgsql-hackers 
On Tue, 2022-09-06 at 16:26 -0700, Jeff Davis wrote:
> In other words, omitting
> GRANTED BY is the same as specifying "GRANTED BY current_user".

Let me correct this thinko to distinguish between specifying GRANTED BY
and not:

  * Let the granting user be the one specified in the GRANTED BY clause
if it exists; otherwise the current user.
  * If the granting user has privileges to be the grantor (ADMIN OPTION
for roles, GRANT OPTION for other objects) then the granting user is
the grantor.
  * Else if GRANTED BY was *not* specified, infer the grantor:
    - If the granting user inherits from a role with the privileges
to be the grantor, then it selects a role with the fewest inheritance
hops as the grantor.
    - Else if the current user is any superuser, the grantor is the top
"owner" (bootstrap superuser for roles; object owner for other objects)
  * Else error (or if an error would break important backwards
compatibility, silently make it work like before and perhaps issue a
WARNING).

The basic idea is to use superuser privileges as a last resort in order
to maximize the cases that work normally (independent of superuser-
ness).

Regards,
	Jeff Davis

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