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

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