Hi all, In building off of prior art regarding the 'pg_read_all_data' and 'pg_write_all_data' roles, I would like to propose an extension to roles that would allow for database-specific role memberships (for the purpose of granting database-specific privileges) as an additional layer of abstraction.
= Problem = There is currently no mechanism to grant the privileges afforded by the default roles on a per-database basis. This makes it difficult to cleanly accomplish permissions such as 'db_datareader' and 'db_datawriter' (which are database-level roles in SQL Server that respectively grant read and write access within a specific database). The recently-added 'pg_read_all_data' and 'pg_write_all_data' work similarly to 'db_datareader' and 'db_datawriter', but work cluster-wide. = Proposal = I propose an extension to the GRANT / REVOKE syntax as well as an additional column within pg_auth_members in order to track role memberships that are only effective within the specified database. Role membership (and subsequent privileges) would be calculated using the following algorithm: - Check for regular (cluster-wide) role membership (the way it works today) - Check for database-specific role membership based on the currently-connected database Attached is a proof of concept patch that implements this. = Implementation Notes = - A new column (pg_auth_members.dbid) in the system catalog that is set to InvalidOid for regular role memberships, or the oid of the given database for database-specific role memberships. - GRANT / REVOKE syntax has been extended to include the ability to specify a database-specific role membership: - "IN DATABASE database_name" would cause the GRANT to be applicable only within the specified database. - "IN CURRENT DATABASE" would cause the GRANT to be applicable only within the currently-connected database. - Omission of the clause would create a regular (cluster-wide) role membership (the way it works today). The proposed syntax (applies to REVOKE as well): GRANT role_name [, ...] TO role_specification [, ...] [ IN DATABASE database_name | IN CURRENT DATABASE ] [ WITH ADMIN OPTION ] [ GRANTED BY role_specification ] - 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) - 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. = Example = 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; = Final Thoughts = This is my first attempt at contributing code to the project, and I would not self-identify as a C programmer. I wanted to get a sense for how receptive the contributors and community would be to this proposal and whether there were any concerns or preferred alternatives before I further embark on a fool's errand. Thoughts? Thanks, -- Kenaniah
poc-database-role-membership-v1.patch
Description: Binary data