PostgreSQL's permission model is built on roles, and tracing which role is a member of which quickly becomes difficult once nesting is involved and no graphical tool is at hand. The catalogs hold all the necessary information, but turning object IDs into readable names requires the right query.
The role model and the catalogs behind it
A "user" and a "role" are the same object in PostgreSQL; the practical difference is that a role defaults to NOLOGIN while a user can log in. Marking a role as login removes even that distinction. Both are stored identically behind the scenes.
Roles are assigned to other roles with ordinary GRANT statements. Starting from a small set of roles and users:
|
1 2 3 4 5 6 |
CREATE USER a; CREATE USER b; CREATE ROLE c LOGIN; CREATE ROLE d LOGIN; CREATE ROLE e LOGIN; CREATE ROLE f LOGIN; |
the grants can be issued like this:
|
1 2 3 4 |
GRANT c TO a; GRANT d TO c; GRANT e TO c; GRANT f TO d; |
Two system catalogs matter here. pg_authid lists every role together with its attributes, such as whether it can log in or is a superuser. Each role carries an internal object ID that identifies it. The second catalog, pg_auth_members, is the membership list: it records which role is assigned to which other role, again using object IDs.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
test=# SELECT oid, rolname, rolcanlogin FROM pg_authid WHERE oid > 16384; oid | rolname | rolcanlogin ---------+---------+------------- 1098572 | a | t 1098573 | b | t 1098574 | c | t 1098575 | d | t 1098576 | e | t 1098577 | f | t (6 rows) |
A look at pg_auth_members on a sample system shows the raw membership pairs:
|
1 2 3 4 5 6 7 8 9 10 |
test=# SELECT * FROM pg_auth_members WHERE roleid > 16384; roleid | member | grantor | admin_option ---------+---------+---------+-------------- 1098574 | 1098572 | 10 | f 1098575 | 1098574 | 10 | f 1098576 | 1098574 | 10 | f 1098577 | 1098575 | 10 | f (4 rows) |
Object IDs below 16384 are reserved for system objects, so they are excluded from the output above — only self-created users are of interest. Making the catalog contents readable needs some joining.
Resolving IDs to names with a recursive query
Role nesting can be arbitrarily deep, which is why resolving membership requires a recursive query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
test=# WITH RECURSIVE x AS ( SELECT member::regrole, roleid::regrole AS role, member::regrole || ' -> ' || roleid::regrole AS path FROM pg_auth_members AS m WHERE roleid > 16384 UNION ALL SELECT x.member::regrole, m.roleid::regrole, x.path || ' -> ' || m.roleid::regrole FROM pg_auth_members AS m JOIN x ON m.member = x.role ) SELECT member, role, path FROM x ORDER BY member::text, role::text; member | role | path --------+------+------------------ a | c | a -> c a | d | a -> c -> d a | e | a -> c -> e a | f | a -> c -> d -> f c | d | c -> d c | e | c -> e c | f | c -> d -> f d | f | d -> f (8 rows) |
The result is a list of all roles along with how they are nested. Most of the work is done by the regrole data type, which casts an object ID directly to a role name. That eliminates the joins otherwise needed to translate IDs into readable user names.
