Proposal: allow database-specific role memberships - Mailing list pgsql-hackers

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