Skip to main content

Postgres Roles

Recently I had to find a way to dynamically generate a list of users and their roles at work, but I couldn't quickly find the SQL I needed to generate the list. However, I could look at the system tables for Postgres and figured out how I could reverse engineer this and map the users back to the roles they were assigned. This code only lists one role/user per line, but you could revise it so you do this as a sub-query and return a list (array) of roles (all roles on one line).

SELECT 
    r.rolname AS "role",
    u.rolname AS "username",
    u.rolsuper AS "SUPER"
FROM
    pg_authid u,
    pg_authid r,
    pg_auth_members m 
WHERE 
    m.roleid = r.oid AND
    m.member = u.oid 
ORDER BY 
    2, 1
;

Of course, if you wanted to you could list only those users who had a single role and change the sort order:

SELECT 
    r.rolname AS "role",
    u.rolname AS "username",
    u.rolsuper AS "SUPER"
FROM
    pg_authid u,
    pg_authid r,
    pg_auth_members m 
WHERE 
    m.roleid = r.oid AND
    m.member = u.oid AND
    r.rolname in (
        'dba',
        ''
    )
ORDER BY 
    2, 1
;

I cheated a little in the above by including an empty role name (the double ticks at line 13) so I can add additional roles (or comment them out as appropriate) and the inclusion of the '' is a cheat so all roles are included. I could just have easily searched for a role name of "ZZZ" or some other arbitrary value (but I know I will also never have a blank or empty role name).