Re: Proposal: allow database-specific role memberships

Hi Antonin,

Thank you again for the detailed review and questions. It was encouraging
to see the increasing level of nuance in this latest round.

It's not clear from the explanation of the GRANT ... IN DATABASE ... / GRANT
> ... IN CURRENT DATABASE ... that, even if "membership in ... will be
> effective only when the recipient is connected to the database ...", the
> ADMIN option might not be "fully effective".

While I'm not entirely sure what you mean by fully effective, it sounds
like you may have expected a database-specific WITH ADMIN OPTION grant to
be able to take effect when connected to a different database (such as
being able to use db_4's database-specific grants when connected to db_3).
The documentation updated in this patch specifies that membership (for
database-specific grants) would be effective only when the grantee is
connected to the same database that the grant was issued for.

In the case of attempting to make a role grant to db_4 from within db_3,
the user would need to have a cluster-wide admin option for the role being
granted, as the test case you referenced in your example aims to verify.

I have added a couple of lines to the documentation included with this
patch in order to clarify.

> Specifically on the regression tests:
>
> * The function check_memberships() has no parameters - is there a
> reason not to use a view?
>

I believe a view would work just as well -- this was an implementation
detail that was fashioned to match the pre-existing rolenames.sql file's
test format.

> * I'm not sure if the pg_auth_members catalog can contain InvalidOid in
> other columns than dbid. Thus I think that the query in
> check_memberships() only needs an outer JOIN for the pg_database
> table,
> while the other joins can be inner.
>

This is probably true. The tests run just as well using inner joins for
pg_roles, as this latest version of the patch reflects.

> * In this part
>
> SET SESSION AUTHORIZATION role_read_12_noinherit;
> SELECT * FROM data; -- error
> SET ROLE role_read_12; -- error
> SELECT * FROM data; -- error
>
> I think you don't need to query the table again if the SET ROLE
> statement
> failed and the same query had been executed before the SET ROLE.

I left that last query in place as a sanity check to ensure that
role_read_12's privileges were indeed not in effect after the call to SET
ROLE.

As we appear to now be working through the minutiae, it is my hope that
this will soon be ready for merge.

- Kenaniah