How do I see which roles have `admin option` on them?

Question

I've seen plenty of questions about someone trying to grant a role_specification to some other_role, but getting the GRANT error

ERROR: must have admin option on rolerole_specification

and the answers all in some way or other seem to boil down to "That's happening because the WITH ADMIN wasn't used when the original GRANTroleTOrole_specification... was issued."

So now, I can't do GRANTrole_specificationTOother_role

But what has stumped me is that I can't find anything about how to actually see if the admin option is set for a role. Answers like List the database privileges using psql reveal privileges for roles, but none of those attributes appear to correspond to be corresponding to the admin option. The Postgres documentation seems mute on this, too.

So... where does Postgres keep track of this, and how can I query for it?

---

Based on the answer by @Erwin Brandstetter I have a few deeper questions...

• \drg is not availble to me. I take it that's in a version of Postgres newer than 13?
• That makes the SQL really valuable, but I'm not sure how that translates. My result is:

SELECT roleid::regrole, member::regrole, grantor::regrole, admin_option
FROM   pg_auth_members where roleid::regrole::text like 'content%';;
    roleid     |    member     |    grantor    | admin_option 
---------------+---------------+---------------+--------------
 content_adm   | int_web_admin | content_adm   | f
 content_rwd   | randall       | postgres      | f
 content_rwd   | content_adm   | postgres      | f

All of the roleids have the admin_option as false in all of the rows of the pg_auth_members table (but a postgres roleid was notably not there). Even so, content_adm can grant content_adm to the int_web_admin role, but it cannot grant content_rwd to it.

The relevant lines of \du show:

    Role name     |  Attributes    |            Member of             
------------------+----------------+----------------------------------
 content_adm      |                | {content_rwd}
 content_rwd      | Cannot login   | {}
 randall          |                | {content_rwd}
 int_web_admin    |                | {sys_role,content_adm}

(I believe the content_adm and randall membership of content_rwd was set up by the previous DBA)

So my second follow-up question is: How do I translate the SQL query result to information similar to the \drg?

Answer 1

The admin option is set for a membership of a role in another role (a.k.a. "group role"). So it's not an attribute of the role itself.

Information about memberships is stored in the system catalog pg_auth_members - in a single instance for the whole DB cluster.

So which roles have the "admin option" in which group - granted by whom?

SELECT roleid::regrole, member::regrole, grantor::regrole, admin_option
FROM   pg_auth_members;

In Postgres 16, a corresponding meta-command has been added to psql: \drg:

test=> \drg
                  List of role grants
 Role name | Member of |       Options       | Grantor  
-----------+-----------+---------------------+----------
 erwin     | dummy     | INHERIT, SET        | postgres
 erwin     | postgres  | ADMIN, INHERIT, SET | postgres
(2 rows)

Or \drgS to also include system roles.

I granted the admin option in role postgres to erwin to demonstrate. The superuser "postgres" has no members by default. But if you make a role member (an extremely empowering move), that role can do anything after SET role postgres, regardless of the admin option

To get the same result in any modern version with plain SQL:

SELECT m.rolname AS "Role name"
     , roleid::regrole AS "Member of"
     , pg_catalog.concat_ws(', ', CASE WHEN pam.admin_option THEN 'ADMIN'   END
                                , CASE WHEN m.rolinherit     THEN 'INHERIT' END
                                , 'SET'
                           ) AS "Options"
     , grantor::regrole AS "Grantor"
FROM   pg_catalog.pg_auth_members pam
JOIN   pg_catalog.pg_roles m ON pam.member = m.oid
WHERE  m.rolname !~ '^pg_'
ORDER  BY 1, 2, 4;

I had a peek at the implementation of \drg in Postgres 16, which is equivalent.