Database-specific role memberships proposal
This is a proposal for implementing database-specific role memberships as an addition to pre-existing cluster-wide role membership system.
Discussion about this feature proposal can be found in the following pgsql-hackers thread:
An implementation of this proposal is tracked in the following patchlist:
|poc-database-role-membership-v1.patch||2021-10-09||Provides a baseline implementation (no tests or docs)|
|database-role-memberships-v2.patch||2021-10-24||Complete implementation (with tests and documentation)|
|database-role-memberships-v4.patch||2021-12-01||Rebased with catversion.h omitted|
|database-role-memberships-v5.patch||2022-01-21||Rebased to resolve conflicts from prior commit fest|
|database-role-memberships-v5-alternate.patch||2022-01-22||Documentation updates with an abstracted acl.c modification|
|database-role-memberships-v6.patch||2022-01-22||Fixes file pointer segfault|
|database-role-memberships-v7.patch||2022-03-23||Fixes an issue with the queries generated by |
|database-role-memberships-v8.patch||2022-04-02||Rebased to address merge conflicts|
|database-role-memberships-v9.patch||2022-07-01||Rebased to address merge conflicts|
|database-role-memberships-v10.patch||2022-07-17||Rebased to address merge conflicts|
|database-role-memberships-v11.patch||2022-07-24||Adds clarifying documentation|
From the Chapter 22. Database Roles documentation (v14):
PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role. The concept of roles subsumes the concepts of “users” and “groups”. In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both.
From the Chapter 22.5 Predefined Roles documentation (v14):
PostgreSQL provides a set of predefined roles that provide access to certain, commonly needed, privileged capabilities and information. Administrators (including roles that have the CREATEROLE privilege) can GRANT these roles to users and/or other roles in their environment, providing those users with access to the specified capabilities and information.
Roles can be granted membership to another role, but this can only be done cluster-wide. At present, there is no mechanism to grant role membership within the context of an individual database. This makes it difficult to implement permission schemes analogous to 'db_datareader' and 'db_datawriter' (which are database-level roles within SQL Server that respectively grant read and write access within a specific database).
Without a per-database role membership abstraction in place, PostgreSQL admins would need to create multiple roles with their own privilege grants specific to each database as a workaround. This gets even messier when enterprise authentication is involved.
The role membership system would be extended to allow for both cluster-wide and database-specific role membership. A column would be added to
pg_auth_members in order to track role memberships that are only effective within the specified database.
The GRANT / REVOKE syntax would be extended to allow for database-specific role memberships:
GRANT role_name [, ...] TO role_specification [, ...] [ IN DATABASE database_name | IN CURRENT DATABASE ] -- new clause [ WITH ADMIN OPTION ] [ GRANTED BY role_specification ]
REVOKE [ ADMIN OPTION FOR ] role_name [, ...] FROM role_specification [, ...] [ IN DATABASE database_name | IN CURRENT DATABASE ] -- new clause [ GRANTED BY role_specification ] [ CASCADE | RESTRICT ]
The GRANT / REVOKE role membership statement would then be processed as follows:
|<not specified>||Statement is applicable to cluster-wide role membership. This is how it works today.|
||Statement is applicable to role membership only within the specified database.|
||Statement is applicable to role membership only within the currently-connected database.|
Role Membership Algorithm
Role membership (and subsequent privileges) would be calculated using the following algorithm:
- Check for cluster-wide role membership (the way it works today)
- Check for database-specific role membership based on the currently-connected database
CREATE DATABASE accounting; CREATE DATABASE sales; CREATE ROLE alice; CREATE ROLE bob; -- Alice is granted read-all privileges cluster-wide (nothing new here) GRANT pg_read_all_data TO alice; -- Bob is granted read-all privileges to just the accounting database GRANT pg_read_all_data TO bob IN DATABASE accounting;
- A new column (
pg_auth_members.dbid) is added to the system catalog. It will be set to
InvalidOidfor cluster-wide membership, and set to the oid of the specified database for database-specific membership.
- The GRANT / REVOKE role membership syntax has been extended (see above).
- DROP DATABASE has been updated to clean up any database-specific role memberships that are associated with the database being dropped.
pg_dump_allwill dump database-specific role memberships using the
IN CURRENT DATABASEsyntax.
pg_dumphas not been modified.
- C API:
is_admin_of_role()'s signature has been updated to include the oid of the database being checked as a third argument. This now returns true if the member has WITH ADMIN OPTION either globally or for the database given.
roles_is_member_of()will additionally include any database-specific role memberships for the database being checked in its result set.