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

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.

Hm.
Can you explain why LEFT JOIN to r and g are fine after removing LEFT 
JOIN to pam?
Why not to change all three left joins to plain join?

The query for v16+ now looks like:

SELECT m.rolname AS "Role name", r.rolname AS "Member of",
   pg_catalog.concat_ws(', ',
     CASE WHEN pam.admin_option THEN 'ADMIN' END,
     CASE WHEN pam.inherit_option THEN 'INHERIT' END,
     CASE WHEN pam.set_option THEN 'SET' END
   ) AS "Options",
   g.rolname AS "Grantor"
FROM pg_catalog.pg_roles m
      JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid)
      LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)
      LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)
WHERE m.rolname !~ '^pg_'
ORDER BY 1, 2, 4;


And for versions <16 I forget to simplify expression for 'Options' 
column after removing LEFT JOIN on pam:

SELECT m.rolname AS "Role name", r.rolname AS "Member of",
   pg_catalog.concat_ws(', ',
     CASE WHEN pam.admin_option THEN 'ADMIN' END,
     CASE WHEN pam.roleid IS NOT NULL AND m.rolinherit THEN 'INHERIT' END,
     CASE WHEN pam.roleid IS NOT NULL THEN 'SET' END
   ) AS "Options",
   g.rolname AS "Grantor"
FROM pg_catalog.pg_roles m
      JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid)
      LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)
      LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)
WHERE m.rolname !~ '^pg_'
ORDER BY 1, 2, 4;

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",

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com