Re: psql: Add role's membership options to the \du+ command

Pavel Luzanov <p.luzanov@postgrespro.ru> writes:
> On 08.07.2023 20:07, Tom Lane wrote
>> 3. Not sure about use of LEFT JOIN in the query.  That will mean we
>> get a row out even for roles that have no grants, which seems like
>> clutter.  The LEFT JOINs to r and g are fine, but I suggest changing
>> the first join to a plain join.

> Can you explain why LEFT JOIN to r and g are fine after removing LEFT 
> JOIN to pam?

The idea with that, IMO, is to do something at least minimally sane
if there's a bogus role OID in pg_auth_members.  With plain joins,
the output row would disappear and you'd have no clue that anything
is wrong.  With left joins, you get a row with a null column and
there's reason to investigate why.

Since such a case should not happen in normal use, I don't think it
counts for discussions about compactness of output.  However, this
is also an argument for using a plain not left join between pg_roles
and pg_auth_members: if we do it as per the earlier patch, then
nulls in the output are common and wouldn't draw your attention.
(Indeed, I think broken and not-broken pg_auth_members contents
would be indistinguishable.)

> I plan to replace it to:

>    pg_catalog.concat_ws(', ',
>      CASE WHEN pam.admin_option THEN 'ADMIN' END,
>      CASE WHEN m.rolinherit THEN 'INHERIT' END,
>      'SET'
>    ) AS "Options",

That does not seem right.  Is it impossible for pam.set_option
to be false?  Even if it is, should this code assume that?

            regards, tom lane