pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Robert Haas <rhaas@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Ensure that pg_auth_members.grantor is always valid.
Date: Thu, 18 Aug 2022 17:26:02 +0000
Message-ID: <E1oOjHN-00092c-RO@gemulon.postgresql.org> (raw)

Ensure that pg_auth_members.grantor is always valid.

Previously, "GRANT foo TO bar" or "GRANT foo TO bar GRANTED BY baz"
would record the OID of the grantor in pg_auth_members.grantor, but
that role could later be dropped without modifying or removing the
pg_auth_members record. That's not great, because we typically try
to avoid dangling references in catalog data.

Now, a role grant depends on the grantor, and the grantor can't be
dropped without removing the grant or changing the grantor.  "DROP
OWNED BY" will remove the grant, just as it does for other kinds of
privileges. "REASSIGN OWNED BY" will not, again just like what we do
in other cases involving privileges.

pg_auth_members now has an OID column, because that is needed in order
for dependencies to work. It also now has an index on the grantor
column, because otherwise dropping a role would require a sequential
scan of the entire table to see whether the role's OID is in use as
a grantor. That probably wouldn't be too large a problem in practice,
but it seems better to have an index just in case.

A follow-on patch is planned with the goal of more thoroughly
rationalizing the behavior of role grants. This patch is just trying
to do enough to make sure that the data we store in the catalogs is at
some basic level valid.

Patch by me, reviewed by Stephen Frost

Discussion: http://postgr.es/m/CA+TgmoaFr-RZeQ+WoQ5nKPv97oT9+aDgK_a5+qWHSgbDsMp1Vg@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/6566133c5f52771198aca07ed18f84519fac1be7

Modified Files
--------------
doc/src/sgml/catalogs.sgml                |   9 ++
src/backend/catalog/catalog.c             |   2 +
src/backend/catalog/dependency.c          |  14 ++-
src/backend/catalog/objectaddress.c       | 108 +++++++++++++++++
src/backend/catalog/pg_shdepend.c         |  47 +++++---
src/backend/catalog/system_views.sql      |   2 +-
src/backend/commands/alter.c              |   1 +
src/backend/commands/event_trigger.c      |   1 +
src/backend/commands/tablecmds.c          |   1 +
src/backend/commands/user.c               | 192 +++++++++++++++++++++++-------
src/include/catalog/dependency.h          |   1 +
src/include/catalog/pg_auth_members.h     |   5 +-
src/test/regress/expected/create_role.out |  16 ++-
src/test/regress/expected/privileges.out  |  32 +++++
src/test/regress/sql/create_role.sql      |   7 +-
src/test/regress/sql/privileges.sql       |  27 +++++
16 files changed, 397 insertions(+), 68 deletions(-)



view thread (3+ messages)  latest in thread

Message-ID: <E1oOjHN-00092c-RO@gemulon.postgresql.org>
Permalink:  ../E1oOjHN-00092c-RO@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1oOjHN-00092c-RO@gemulon.postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: rhaas@postgresql.org
  Subject: Re: pgsql: Ensure that pg_auth_members.grantor is always valid.
  In-Reply-To: <E1oOjHN-00092c-RO@gemulon.postgresql.org>
  References: <E1oOjHN-00092c-RO@gemulon.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox