Re: Proposal: allow database-specific role memberships

Michael Paquier a écrit :
> On Wed, Sep 07, 2022 at 12:50:32PM +0500, Ibrar Ahmed wrote:
>> The patch requires a rebase, please do that.
>>
>> Hunk #5 succeeded at 454 (offset 28 lines). 1 out of 5 hunks FAILED
>> -- saving rejects to file doc/src/sgml/ref/grant.sgml.rej
>
> There has been no updates on this thread for one month, so this has
> been switched as RwF.

I took the liberty to rebase this (old) patch, originally authored by
Kenaniah Cerny.

This is about adding a "IN DATABASE <datname>" clause to GRANT and
REVOKE commands allowing to control role membership in a database scope,
rather that cluster-wise. This could be interesting in combination with
predefined roles, e.g.:

GRANT pg_read_all_data TO bob IN DATABASE app;
GRANT pg_maintain TO dba IN DATABASE metrics;

without having to grant too many privileges when a user is supposed to
only operate on some databases.

The logic of the original patch (as of its version 11) is preserved. One
noticeable change concerns tests: they got moved in src/test/regress
(there were in 'unsafe_tests'), with proper cleanup, and now avoid using
superuser as well as modifying templates.

Is this a feature that's still interesting? (Feedbacks, from 2022, in
the thread were a bit mixed.)

Personally, I have a few concerns regarding the feature and its
implementation:

- The IN DATABASE clause does not make much sense for some roles, like
pg_read_all_stats (the implementation does not guard against this).

- An 'IN SCHEMA' clause might be a natural supplementary feature.
However, the current implementation relying on a new 'dbid' column added
in pg_auth_members catalog might not fit well in that case.

Thanks,
Denis