Horton

Re: pg_auth_members.grantor is bunk

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: "David G. Johnston" <david.g.johnston@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-07-28T19:09:32Z
Lists: pgsql-hackers

Attachments

On Tue, Jul 26, 2022 at 12:46 PM Robert Haas <robertmhaas@gmail.com> wrote:
> I believe that these patches are mostly complete, but I think that
> dumpRoleMembership() probably needs some more work. I don't know what
> exactly, but there's nothing to cause it to dump the role grants in an
> order that will create dependent grants after the things that they
> depend on, which seems essential.

OK, so I fixed that, and also updated the documentation a bit more. I
think these patches are basically done, and I'd like to get them
committed before too much more time goes by, because I have other
things that depend on this which I also want to get done for this
release. Anybody object?

I'm hoping not, because, while this is a behavior change, the current
state of play in this area is just terrible. To my knowledge, this is
the only place in the system where we allow a dangling OID reference
in a catalog table to persist after the object to which it refers has
been dropped. I believe it's also the object type where multiple
grants by different grantors aren't tracked separately, and where the
grantor need not themselves have the permission being granted. It
doesn't really look like any of these things were intentional behavior
so much as just ... nobody ever bothered to write the code to make it
work properly. I'm hoping the fact that I have now done that will be
viewed as a good thing, but maybe that won't turn out to be the case.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

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