On 13.07.2023 11:26, Pavel Luzanov wrote:
> 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",
>
The new version contains only this change.
--
-----
Pavel Luzanov