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
