On Fri, Mar 3, 2023 at 4:01 AM Pavel Luzanov <p.luza...@postgrespro.ru> wrote:
> Hello, > > On 22.02.2023 00:34, David G. Johnston wrote: > > I didn't even know this function existed. But I see that it was changed in > 3d14e171 with updated documentation: > > https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-ACCESS > Maybe that's enough. > > > I think that should probably have ADMIN as one of the options as well. > Also curious what it reports for an empty membership. > > > I've been experimenting for a few days and I want to admit that this is a > very difficult and not obvious topic. > I'll try to summarize what I think. > > 1. > About ADMIN value for pg_has_role. > Implementation of ADMIN value will be different from USAGE and SET. > To be True, USAGE value requires the full chain of memberships to have > INHERIT option. > Similar with SET: the full chain of memberships must have SET option. > But for ADMIN, only last member in the chain must have ADMIN option and > all previous members > must have INHERIT (to administer directly) or SET option (to switch to > role, last in the chain). > Therefore, it is not obvious to me that the function needs the ADMIN value. > Or you can SET to some role that then has an unbroken INHERIT chain to the administered role. ADMIN basically implies SET/USAGE but it doesn't work the other way around. I'd be fine with "pg_can_admin_role" being a newly created function that provides this true/false answer but it seems indisputable that today there is no core-provided means to answer the question "can one role get ADMIN rights on another role". Modifying \du to show this seems out-of-scope but the pg_has_role function already provides that question for INHERIT and SET so it is at least plausible to extend it to include ADMIN, even if the phrase "has role" seems a bit of a misnomer. I do cover this aspect with the Role Graph pseudo-extension but given the presence and ease-of-use of a boolean-returning function this seems like a natural addition. We've also survived quite long without it - this isn't a new concept in v16, just a bit refined. > > 2. > pg_has_role function description starts with: Does user have privilege for > role? > - This is not exact: function works not only with users, but with > NOLOGIN roles too. > - Term "privilege": this term used for ACL columns, such usage may be > confusing, > especially after adding INHERIT and SET in addition to ADMIN option. > Yes, it missed the whole "there are only roles now" memo. I don't have an issue with using privilege here though - you have to use the GRANT command which "defines access privileges". Otherwise "membership option" or maybe just "option" would need to be explored. > > 3. > It is possible to grant membership with all three options turned off: > grant a to b with admin false, inherit false, set false; > But such membership is completely useless (if i didn't miss something). > May be such grants must be prohibited. At least this may be documented in > the GRANT command. > I have no issue with prohibiting the "empty membership" if someone wants to code that up. > 4. > Since v16 it is possible to grant membership from one role to another > several times with different grantors. > And only grantor can revoke membership. > - This is not documented anywhere. > Yeah, a pass over the GRANTED BY actual operation versus documentation seems warranted. > - Current behavior of \du command with duplicated roles in "Member of" > column strongly confusing. > This is one of the goals of the discussion patch. > This indeed needs to be fixed, one way (include grantor) or the other (du-duplicate), with the current proposal of including grantor getting my vote. > > I think to write about this to pgsql-docs additionally to this topic. > I wouldn't bother starting yet another thread in this area right now, this one can absorb some related changes as well as the subject line item. David J.
