Proposal: allow database-specific role memberships

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