Infrastructure at your Service

Daniel Westermann

Where does PostgreSQL store information about default privileges?

A recent comment on the blog post about PostgreSQL default privileges is the reason for this little post: “I’d love to know where postgres stores default privileges?”. Well, all the information about privileges should be stored somewhere in the catalog so let’s check where that information could be.

To start with, we create a brand new user, a brand new schema and then modify the default privileges for that user in the given schema:

postgres=# create user a with login password 'a';
CREATE ROLE
postgres=# create schema s;
CREATE SCHEMA
postgres=# alter default privileges in schema s grant select on tables to a;
ALTER DEFAULT PRIVILEGES

There is a “useconfig” column in the pg_user catalog table so you might think the information about default privileges is there:

postgres=# \d pg_user
                        View "pg_catalog.pg_user"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 usename      | name                     |           |          | 
 usesysid     | oid                      |           |          | 
 usecreatedb  | boolean                  |           |          | 
 usesuper     | boolean                  |           |          | 
 userepl      | boolean                  |           |          | 
 usebypassrls | boolean                  |           |          | 
 passwd       | text                     |           |          | 
 valuntil     | timestamp with time zone |           |          | 
 useconfig    | text[]                   | C         |          | 

postgres=# select * from pg_user where usename = 'a';
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
---------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 a       |    16408 | f           | f        | f       | f            | ******** |          | 
(1 row)

It is not, and actually it does not make any sense as default privileges are attached to schemas, not users. So it could be in the pg_namespace catalog table:

postgres=# select * from pg_namespace where nspname = 's';
  oid  | nspname | nspowner | nspacl 
-------+---------+----------+--------
 16409 | s       |       10 | 
(1 row)

It is not there as well but it gives you a hint. Privileges have something to do with ACLs (Access control lists) so let’s check if there is a catalog table that could relate to that:

postgres=# select * from pg_tables where tablename like '%acl%';
 schemaname |   tablename    | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+----------------+------------+------------+------------+----------+-------------+-------------
 pg_catalog | pg_default_acl | postgres   |            | t          | f        | f           | f
(1 row)

Here we go:

postgres=# select * from pg_default_acl where defaclnamespace = 's'::regnamespace;
  oid  | defaclrole | defaclnamespace | defaclobjtype |   defaclacl    
-------+------------+-----------------+---------------+----------------
 16410 |         10 |           16409 | r             | {a=r/postgres}
(1 row)

“a=r” means: User “a” has read privileges on all the objects. Modifying the default privileges once more:

postgres=# alter default privileges in schema s grant insert on tables to a;
ALTER DEFAULT PRIVILEGES
postgres=# select * from pg_default_acl defaclnamespace = 's'::regnamespace;
  oid  | defaclrole | defaclnamespace | defaclobjtype |    defaclacl    
-------+------------+-----------------+---------------+-----------------
 16410 |         10 |           16409 | r             | {a=ar/postgres}
(1 row)

Now “a” was added, and that means append(insert). You can check the various flags in the documentation.

The “/postgres” string defines the owner of the schema:

postgres=# alter user a superuser;
ALTER ROLE
postgres=# \c postgres a
You are now connected to database "postgres" as user "a".
postgres=# create schema s2;
CREATE SCHEMA
postgres=# select * from pg_default_acl where defaclnamespace = 's2'::regnamespace;
 oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl 
-----+------------+-----------------+---------------+-----------
(0 rows)

postgres=# create user b;
CREATE ROLE
postgres=# alter default privileges in schema s2 grant select on tables to b;
ALTER DEFAULT PRIVILEGES
postgres=# select * from pg_default_acl where defaclnamespace = 's2'::regnamespace;
  oid  | defaclrole | defaclnamespace | defaclobjtype | defaclacl 
-------+------------+-----------------+---------------+-----------
 16413 |      16408 |           16411 | r             | {b=r/a}
(1 row)

I hope that answers the question.

3 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure