PostgreSQL: Serializable transactions with GRANT/DROP role

Question

I have been reading a lot about serializable transactions in Postgres, but came across an issue that I haven't been able to resolve. Let's assume that I have two Postgres sessions A and B from different psql processes, and that before starting any transactions, I create roles role1 and role2

myuser=# CREATE ROLE role1 ;
CREATE ROLE
myuser=# CREATE ROLE role2 ;
CREATE ROLE

At this point, I can start transactions in both of the sessions via myuser=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ; and verify that both sessions see both roles, via SELECT * FROM pg_catalog.pg_roles;

Let's assume that I drop the role role2 from the session B, verify that it's not shown and commit:

myuser=# DROP ROLE role2 ;
DROP ROLE
myuser=# SELECT * FROM pg_catalog.pg_roles;
                    rolname                    | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid
-----------------------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 role1                                         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 16563
myuser=# commit ;
COMMIT

Now, let's go back to session A:

Even after dropping role2 and committing inside session B, we can still see that the transaction in session A still sees both roles (as expected, as this is serializable):

myuser=# SELECT * FROM pg_catalog.pg_roles;
                    rolname                    | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid
-----------------------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------
 role1                                         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 16563
 role2                                         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 16564

Now, let's try the GRANT command. Here is the weird part:

myuser=# GRANT role2 TO role1 ;
ERROR:  role "role2" does not exist

Right before running the GRANT, we can see that both roles role1 and role2 exist, but now we see such error. Why is that?

Thanks!

score 3 · Answer 1 · answered Nov 21 '17 at 09:13

With “normal” (i.e. non-catalog) tables conflicting operations like this would lead to a serialization error. While a serializable transaction sees an old state of the database, any attempt to modify a value that has been changed since would lead to such an error.

What makes this case different is that the tables that store roles and role membership (pg_authid and pg_auth_members) are catalog tables (even shared catalogs since roles apply to all databases), and for these processing and error messages are slightly different.

It is good and necessary that the operation fails, even if the error message is surprising.

PostgreSQL: Serializable transactions with GRANT/DROP role

1 Answers1