Re: Proposal: allow database-specific role memberships - Mailing list pgsql-hackers

From Kenaniah Cerny
Subject Re: Proposal: allow database-specific role memberships
Date
Msg-id CA+r_aq-QO__EZfd6dNj+9Fg0=3Rm8rBmJ_fpSb4Jn-SWPH_Jxg@mail.gmail.com
Whole thread Raw
In response to Re: Proposal: allow database-specific role memberships  (Antonin Houska <ah@cybertec.at>)
Responses Re: Proposal: allow database-specific role memberships
List pgsql-hackers
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
Attachment

pgsql-hackers by date:

Previous
From: Thomas Munro
Date:
Subject: Re: 012_subtransactions.pl vs clang -fsanitize=undefined
Next
From: Justin Pryzby
Date:
Subject: Re: Custom tuplesorts for extensions