PostgreSQL: Get member roles and permissions

PostgreSQL provides a highly sophisticated and powerful security and permission system. It allows you to define users (= roles), groups and so on. However, without a graphical user interface, it is usually a bit tricky to figure out which role is assigned to whom. The following blog post explains how this can be done. Learn how to retrieve information on roles and role membership in PostgreSQL.

Table of Contents

Preparing users and roles

To show how users can be analyzed, we first create a couple of users and roles:

CREATE USER a;
CREATE USER b;
CREATE ROLE c LOGIN;
CREATE ROLE d LOGIN;
CREATE ROLE e LOGIN;
CREATE ROLE f LOGIN;

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 important thing to note here is that “users” and “roles” are basically the same thing. The main difference is that a role is always NOLOGIN while a “user” is LOGIN. However, if you want a role to be able to log in, you can simply mark it as login. Otherwise, there is no difference. Behind the scenes, roles and users are all the same.

We can now assign roles to other roles (= users), using simple GRANT statements:

GRANT c TO a;
GRANT d TO c;
GRANT e TO c;
GRANT f TO d;

1

2

3

4

GRANT c TO a;

GRANT d TO c;

GRANT e TO c;

GRANT f TO d;

The goal is now to figure out how roles are nested and which role is assigned to which other role. Before we take a look at the final query, it makes sense to check out the system catalog and understand how data is stored. Let’s look at the roles first:

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)

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)

pg_authid contains a list of all roles, as well as some additional information (can log in, is superuser yes / no, etc.). What is noteworthy here is that each user has an internal number (= object id) which identifies the role.

The second important system table here is pg_auth_members. It basically knows which role is assigned to which other role. It is a simple role / member list which contains object ids to identify our users. The following query shows what my system table contains:

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)

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)

In PostgreSQL, all object ids below 16384 are reserved for system objects. Therefore I have excluded those, because we are only interested in users we have created on our own. Making the system catalog more readable requires a bit of joining, as you will see in the next section.

Resolving users and role membership in PostgreSQL

In PostgreSQL, roles / users can be nested. Nesting can be infinitely deep, which is why it is necessary to write a recursion, see below:

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)

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 query shows a list of all roles and how they are nested. The trick is mostly the “regrole” data type which allows us to cast an object ID directly to a username so that we can read it more easily. No additional joins are needed to resolve the ID.

Finally …

If you are interested in security in general, I would like to recommend “PostgreSQL TDE” which is a more secure version of PostgreSQL capable of encrypting data on disk. It can be downloaded from our website.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.