Re: Proposal: allow database-specific role memberships

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

Attached a rebased version fixing test warnings raised in CI.

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