Database-specific role memberships proposal

Overview

This is a proposal for implementing database-specific role memberships as an addition to pre-existing cluster-wide role membership system.

Discussion

Discussion about this feature proposal can be found in the following pgsql-hackers thread:

• Proposal: allow database-specific role memberships

Implementation

An implementation of this proposal is tracked in the following patchlist:

Patch	Date	Description
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-v3.patch	2021-11-28	Rebased
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 dumpRoleMembership()
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

Background

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.

Problem

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.

Proposed Solution

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.

Syntax

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:

Clause	Effect
<not specified>	Statement is applicable to cluster-wide role membership. This is how it works today.
IN DATABASE database_name	Statement is applicable to role membership only within the specified database.
IN CURRENT 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:

1. Check for cluster-wide role membership (the way it works today)
2. Check for database-specific role membership based on the currently-connected database

Example Usage

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;

Implementation Details

• A new column (pg_auth_members.dbid) is added to the system catalog. It will be set to InvalidOid for 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_all will dump database-specific role memberships using the IN CURRENT DATABASE syntax. pg_dump has 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.