pgsql: Make role grant system more consistent with other privileges. - Mailing list pgsql-committers

Make role grant system more consistent with other privileges.

Previously, membership of role A in role B could be recorded in the
catalog tables only once. This meant that a new grant of role A to
role B would overwrite the previous grant. For other object types, a
new grant of permission on an object - in this case role A - exists
along side the existing grant provided that the grantor is different.
Either grant can be revoked independently of the other, and
permissions remain so long as at least one grant remains. Make role
grants work similarly.

Previously, when granting membership in a role, the superuser could
specify any role whatsoever as the grantor, but for other object types,
the grantor of record must be either the owner of the object, or a
role that currently has privileges to perform a similar GRANT.
Implement the same scheme for role grants, treating the bootstrap
superuser as the role owner since roles do not have owners. This means
that attempting to revoke a grant, or admin option on a grant, can now
fail if there are dependent privileges, and that CASCADE can be used
to revoke these. It also means that you can't grant ADMIN OPTION on
a role back to a user who granted it directly or indirectly to you,
similar to how you can't give WITH GRANT OPTION on a privilege back
to a role which granted it directly or indirectly to you.

Previously, only the superuser could specify GRANTED BY with a user
other than the current user. Relax that rule to allow the grantor
to be any role whose privileges the current user posseses. This
doesn't improve compatibility with what we do for other object types,
where support for GRANTED BY is entirely vestigial, but it makes this
feature more usable and seems to make sense to change at the same time
we're changing related behaviors.

Along the way, fix "ALTER GROUP group_name ADD USER user_name" to
require the same privileges as "GRANT group_name TO user_name".
Previously, CREATEROLE privileges were sufficient for either, but
only the former form was permissible with ADMIN OPTION on the role.
Now, either CREATEROLE or ADMIN OPTION on the role suffices for
either spelling.

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/ce6b672e4455820a0348214be0da1a024c3f619f

Modified Files
--------------
doc/src/sgml/ref/alter_group.sgml         |   6 +-
doc/src/sgml/ref/grant.sgml               |  12 +-
doc/src/sgml/ref/revoke.sgml              |  12 +-
src/backend/commands/user.c               | 565 ++++++++++++++++++++++++++----
src/backend/parser/gram.y                 |   2 +
src/backend/utils/adt/acl.c               |  47 ++-
src/backend/utils/cache/syscache.c        |   8 +-
src/bin/pg_dump/pg_dumpall.c              | 177 +++++++++-
src/include/catalog/catversion.h          |   2 +-
src/include/catalog/pg_auth_members.h     |   4 +-
src/include/utils/acl.h                   |   1 +
src/test/regress/expected/create_role.out |  16 +-
src/test/regress/expected/privileges.out  |  73 +++-
src/test/regress/sql/create_role.sql      |   7 +-
src/test/regress/sql/privileges.sql       |  42 ++-
15 files changed, 834 insertions(+), 140 deletions(-)