Re: psql: Add role's membership options to the \du+ command - Mailing list pgsql-hackers

From Pavel Luzanov
Subject Re: psql: Add role's membership options to the \du+ command
Date
Msg-id 50ea2597-5e3f-372c-1ea8-5ba4f3edd690@postgrespro.ru
Whole thread Raw
In response to Re: psql: Add role's membership options to the \du+ command  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: psql: Add role's membership options to the \du+ command
Re: psql: Add role's membership options to the \du+ command
List pgsql-hackers
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




pgsql-hackers by date:

Previous
From: Kyotaro Horiguchi
Date:
Subject: Re: add non-option reordering to in-tree getopt_long
Next
From: Shruthi Gowda
Date:
Subject: Re: 'ERROR: attempted to update invisible tuple' from 'ALTER INDEX ... ATTACH PARTITION' on parent index