Horton

Re: pg_auth_members.grantor is bunk

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: Stephen Frost <sfrost@snowman.net>, Mark Dilger <mark.dilger@enterprisedb.com>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-06-02T19:50:58Z
Lists: pgsql-hackers 
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jun 2, 2022 at 3:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Maybe.  What I was pointing out is that this is SQL-standard syntax
>> and there are SQL-standard semantics that it ought to be implementing.
>> Probably those semantics match what you describe here, but we ought
>> to dive into the spec and make sure before we spend a lot of effort.
>> It's not quite clear to me whether the spec defines any particular
>> unique key (identity) for the set of role authorizations.

> I sort of thought http://postgr.es/m/3981966.1646429663@sss.pgh.pa.us
> constituted a completed investigation of this sort. No?

I didn't think so.  It's clear that the spec expects us to track the
grantor, but I didn't chase down what it expects us to *do* with that
information, nor what it thinks the rules are for merging multiple
authorizations.

			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