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