Re: Proposal: allow database-specific role memberships

Denis Laxalde a écrit :
> 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.

As the original commitfest entry,
https://commitfest.postgresql.org/36/3374/, was "stalled", I created a
new one at https://commitfest.postgresql.org/50/5284/; hoping this is okay.

> 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.