On 03.03.2023 19:21, David G. Johnston wrote:
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.
I must admit that I am slowly coming to the same conclusions that you have already outlined in previous messages.
Indeed, adding ADMIN to pg_has_role looks logical. The function will show whether one role can manage another directly or indirectly (via SET ROLE).
Adding ADMIN will lead to the question of naming other values. It is more reasonable to have INHERIT instead of USAGE.
And it is not very clear whether (except for backward compatibility) a separate MEMBER value is needed at all.
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.
I agree.
--
Pavel Luzanov
Postgres Professional: https://postgrespro.com