Postgres Permissions

It’s taken me a long time to build up an understanding of the Postgres permissions system, so I thought I’d write it down to help others get up to speed faster.

First let’s talk about what things can be guarded by permissions. The big ones are databases and tables/views, but you can also set permissions on schemas, functions, sequences, types, languages, tablespaces, domains, foreign servers, foreign data wrappers, and large objects. (Phew!) There are also a few permissions that apply to the whole cluster. (In Postgres terminology, the “cluster” is the server process(es) keeping its files in /var/lib/postgresql/9.5/main and listening on 5432 (or whatever), and a “database” is a namespace in the cluster where you define tables. You can list the databases in your cluster with \l.) We’ll explore below what actions can be allowed or retricted for each of these subjects. Collectively, let’s call permissionable things “topics”.

(As of Postgres 9.5 there is also Row Level Security (RLS). That is complicated enough to be a separate topic, so here I want to cover just the traditional permissions system.)

Second let’s ask who has permissions. In Postgres permissions come first from your user, and second from any groups your user is a part of. Collectively, users and groups are called “roles”. Or to be more accurate, before Postgres 8.1 users and groups were separate things, but now everything is just a role. A role can even be both a “group” and a “user” at the same time: it can have members but also serve as a direct login.

Permission Mechanics

The key to understanding Postgres is that the stored permission between any role and any topic is only ever a 0 or a 1. When Postgres does a permissions check, it takes all your roles’ permissions on the topic, and if there are any with a 1, you’re in. In other words your effective permission is the OR of all your roles’ permissions. (There is one exception to this, explained below, but it rarely matters.)

You can see what groups each role belongs to with \du (list users). You can add someone to a role with GRANT r TO u, or remove them with REVOKE r FROM u, where r is the group and u is the user. (I am using “group” and “user” loosely here for clarity, but remember that they are all roles.)

So some consequences of this: If you REVOKE a permission from a role r1, it loses its direct 1, but it might still get the permission indirectly from other roles. The REVOKE isn’t remembered in any way. It just sets the 1 to a 0 for r1. It doesn’t override other possible sources of a 1.

Another consequence is that commands like GRANT SELECT ON ALL TABLES have an immediate effect of setting a lot of 1s, but they are not remembered either. So if you add some more tables, they will not automatically share the same grants as the old tables.

The complement of GRANT ON ALL is ALTER DEFAULT PRIVILEGES. This lets you say what the 1s and 0s should be for future objects. It does not change any existing objects; it just says how to set up new ones. You can combine GRANT ON ALL and ALTER DEFAULT to get 100% coverage: first for the old objects, then for the new. Actually it would probably be less racy to do the DEFAULT first, in case someone else is making more objects while you type.

The final trick in all this is a magic role named PUBLIC. Every other role is a member of the PUBLIC role, so if it has any 1s, then everyone will apply those 1s. Note there is no way to cancel a 1 from the PUBLIC role, because of what we said above about how permission checks do an OR. You might REVOKE something from a troublemaker and give them a 0, but if the PUBLIC role has a 1, they can do the thing anyway. Of course you can REVOKE a permission from the PUBLIC role itself, but then you have to individually GRANT the permission to all the roles that should have it.

One wrinkle here is that a role can have either the INHERIT or NOINHERIT attribute. If it has NOINHERIT, then it does not benefit from permissions of roles it belongs to. In other words, its own direct 1s are the only ones it’s got (ha!). In practice this is not a significant block though, because if you belong to an admin role but have NOINHERIT, you can still SET ROLE admin and act with the permissions of that role. So NOINHERIT should not be used as a way of restricting what people can do. Also note that even with NOINHERIT, you still inherit permissions from the PUBLIC role. In general I don’t see much point to NOINHERIT, except for the psychological benefit of making people more explicitly prepare before running dangerous commands.

So you have the PUBLIC role, and you have ALTER DEFAULT PRIVILEGES, and both are a kind of default, but in different ways. It is important to keep these separate! In fact I’m pretty sure I’ve seen places in the docs where PUBLIC permissions are referred to as “defaults”. If you are working with either of these, watch out that you are clear on how they differ!

Cluster Permissions

Okay, so those are the mechanics. Now let’s look at some specific permissions. At the top level you have cluster-wide permissions. These are stored as attributes on your role. They are:

LOGIN: This means you can connect to the cluster. You still must in addition have permission to CONNECT to an individual database (covered below), but if your role doesn’t have this basic LOGIN attribute, you can’t get in at all.

SUPERUSER: This means you can do anything. You’ve got root. Your other permissions don’t matter, because when Postgres sees you have this, it doesn’t ask any further questions.

CREATEDB: You can create more databases.

CREATEROLE: You can create more roles.

REPLICATION: You can start streaming replication. Normally you’d only give this to a special role used by slaves to connect to the master database.

You can set these permissions with CREATE ROLE or change them with ALTER ROLE. You can see them with \du.

Database Permissions

The next level is the database. If you type \l you can see the databases in your cluster, and the last columm shows the permissions on each. For example:

perms=# \l
                                                   List of databases
            Name            |     Owner      | Encoding |   Collate   |    Ctype    |         Access privileges         
----------------------------+----------------+----------+-------------+-------------+-----------------------------------
 perms                      | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres                     +
                            |                |          |             |             | postgres=CTc/postgres            +
                            |                |          |             |             | admin=C/postgres
 postgres                   | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0                  | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                      +
                            |                |          |             |             | postgres=CTc/postgres
 template1                  | postgres       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                      +
                            |                |          |             |             | postgres=CTc/postgres            +

For each database, you’ll see several lines of permissions. Each line shows a role, an equals sign, and then the list of permissions for that role. Someones a line is missing the role and starts with just an equals sign; this is for the PUBLIC role. It shows the permissions that everyone gets for free. Whatever you see here is allowed for anyone.

There is also a forward slash followed by another role name. That shows who granted those permissions.

So in the above example you can see that for the perms database, the PUBLIC role has Tc permissions, and admin has C, granted by postgres.

Each permission is shown by a single letter. They mean:

T: Create temporary (in-memory) tables.

c: CONNECT to the database. It has always worried me that by default databases let anyone connect. In theory this is okay since you can’t SELECT or change anything, but you can still list the tables (\dt), list their colums \d tablename, etc. More precisely, connecting lets people query the tables in pg_catalog and information_schema. They can see the names of all the other database users, all the other databases, and quite a bit more. Personally I feel this is a significant shortcoming in Postgres’s system.

You can revoke access to these system tables, but at the cost of breaking important things. For instance pgadmin fails if it can’t list the databases. Another idea is to use RLS on the system tables, but that is not supported.

Of course, if you can connect to any database, you can do these things. I’m not aware of any information you can get about database foo by connecting there that you can’t also get while connected to database bar. So you could argue that granting everyone CONNECT is not a problem! I don’t have a good answer to that, even though it feels suspicious. I think the moral, then, is that all database roles require more trust than you might expect.

C: This allows you to CREATE a new schema within the database. As the owner of that schema, you’ll be able to do lots of things with it, like create tables, etc.

Finally, individual letters may be followed by an asterisk (*). That means that the user not only has the permission, but may also GRANT the permission to others. The object owner may always grant permission, so Postgres doesn’t bother to show asterisks for them.

Table/View Permissions

For permission purposes, Postgres treats tables and views as the same thing. So ALL TABLES will include views, too.

You can list the permissions for tables and views (and sequences too) using \dp or \z (They do the same thing.):

perms=# \dp
                                       Access privileges
 Schema |      Name      |   Type   |     Access privileges     | Column privileges | Policies 
--------+----------------+----------+---------------------------+-------------------+----------
 public | sales          | table    | postgres=arwdDxt/postgres+|                   | 
        |                |          | admin=arwdDxt/postgres    |                   | 
 public | sales_id_seq   | sequence |                           |                   | 
 public | widgets        | table    | postgres=arwdDxt/postgres+|                   | 
        |                |          | admin=arwdDxt/postgres   +|                   | 
        |                |          | joe=r/postgres            |                   | 
 public | widgets_id_seq | sequence |                           |                   | 

What you see here is a lot like the output from \l. A blank means you have never made any permissions changes for this object, so its permissions still match the default. (When I say “the default”, I don’t mean the defaults you’ve established with ALTER DEFAULT PRIVILEGES, but rather the default defaults that Postgres ships with.) Note that once you change something, even if you change it back, the column will never go back to blank again.

Of course now there are more letters than before. That’s because tables have lots of permissions:

SELECT (r): You can read from the table.

UPDATE (w): You can write to the table.

INSERT (a): You can append to the table.

DELETE (d): You can delete from the table.

TRUNCATE (D): You can really delete from the table.

REFERENCES (x): This lets you create foreign key constraints elsewhere that reference this table. You need the permission on both tables. Also, you can grant this permission for just specific columns. (I confess I don't really get this permission. What is the use of forbidding foreign keys? I mean I get why not anyone can create a foreign key, but what is the use of making it a separate permission from other DDL commands? Besides, if a foreign key makes sense, why not just define it yourself?)

In the first version of this post, I questioned the usefulness of this permission, but the folks from the Postgres mailing list were really helpful in making it click for me. As Peter Holzer explains, only a table’s owner may add constraints to it (or run ALTER TABLE at all). But suppose joe owns the table joes_widgets, and fred owns the table freds_frozzles. Joe wants to add a frozzle_id column pointing to Fred’s table. He would need the REFERENCES privilege on Fred’s table to do it.

And why can’t Postgres just check Joe’s SELECT privilege instead? It’s because making a foreign key does more than just read from the table. Joe’s foreign key could prevent Fred from changing/deleting his own records, as well as costing Fred time checking the constraint. I don’t know how often this permission will come into play for me, but I’m happy to figure out why it’s there!

TRIGGER (t): You can add a trigger to the table.

It is also useful to see the default privileges for each table (from ALTER DEFAULT PRIVILEGES):

perms=# \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges 
-------+--------+------+-------------------
(0 rows)

perms=# ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO joe;
ALTER DEFAULT PRIVILEGES
perms=# \ddp
               Default access privileges
  Owner   | Schema | Type  |     Access privileges     
----------+--------+-------+---------------------------
 postgres |        | table | postgres=arwdDxt/postgres+
          |        |       | joe=r/postgres
(1 row)

perms=# create table inventory (id serial, widget_id integer);
CREATE TABLE
perms=# \dp
                                        Access privileges
 Schema |       Name       |   Type   |     Access privileges     | Column privileges | Policies 
--------+------------------+----------+---------------------------+-------------------+----------
 public | inventory        | table    | postgres=arwdDxt/postgres+|                   | 
        |                  |          | joe=r/postgres            |                   | 
 ...

Here you can see that new tables will start out with whatever permissions we have prepared as the defaults.

Note that you can also say ALTER DEFAULT PRIVILEGES FOR ROLE r GRANT .... Normally when you alter default privileges, it only affects (future) objects you own. If you want to change the privileges for future objects created by someone else, you can use FOR ROLE. Naturally you can only that if you belong to that role.

With all these rules, it is natural to want to see the final calculated rights of each user in the system. Unfortunately Postgres doesn’t have a built-in way to show that. You can say SELECT * FROM information_schema.role_table_grants WHERE table_schema = 'public', but that does not compute inherited permissions.

Fortunately there is a function called has_table_privilege you can use to get most of the way there. It does consider role memberships: for instance if joe belongs to the admin role and has INHERIT, then has_table_privilege('joe', 'public.sales', 'INSERT') will return true. Change joe to NOINHERIT and it will return false. I do wish there were a way to run the computation as if all roles were INHERIT, but this is close enough for me! Others have put this together in a nice query to get the permissions for any table you like. Here is a version I like:

SELECT  t.tablename,
        u.usename,
        has_table_privilege(usename, concat(schemaname, '.', tablename), 'select') AS select,
        has_table_privilege(usename, concat(schemaname, '.', tablename), 'insert') AS insert,
        has_table_privilege(usename, concat(schemaname, '.', tablename), 'update') AS update,
        has_table_privilege(usename, concat(schemaname, '.', tablename), 'delete') AS delete, 
        has_table_privilege(usename, concat(schemaname, '.', tablename), 'truncate') AS truncate, 
        has_table_privilege(usename, concat(schemaname, '.', tablename), 'references') AS references,
        has_table_privilege(usename, concat(schemaname, '.', tablename), 'trigger') AS trigger
FROM    pg_tables t,
        pg_user u 
WHERE   t.tablename = 'inventory';

Of course you could just as well filter on user to see one person’s permissions for all the tables. If you do a lot of permissions work, it might even be worth wrapping this up into a view so you can filter on whatever you like.

Incidentally, there are lots of other has_whatever_privilege functions to ask questions not covered by psql \ commands.

Sequence Permissions

If you squint, a sequence is pretty much just a tiny table: you can SELECT from it (call currval), or UPDATE it (call setval), or do both at once (call nextval). So it appears in \dp with the tables and views. But unlike tables and views, they also have the USAGE permission, which allows currval or nextval.

The USAGE permission is not just an abbreviation for SELECT plus UPDATE; it has its own bit. You can see it in \dp as U (vs. r and w). Yet if you have it, you don’t need r or w to use the sequence. Granting only USAGE would let someone use the sequence normally but not call setval, which might be useful.

I can’t think of any reason to give someone just SELECT on a sequence. Even currval won’t work, because currval only tells you the last time you called nextval. You will get an error like ERROR: currval of sequence "widgets_id_seq" is not yet defined in this session.

Function Permissions

Functions have just an EXECUTE permission. New functions will grant execute permission to PUBLIC, so be careful if you are creating something sensitive. If you like, you can change this with ALTER DEFAULT PERMISSIONS.

You can see functions in psql with \df or \df+, but neither will show the permissions. Instead you should use a query like

SELECT proacl FROM pg_proc WHERE proname = 'f';

The format of proacl is like anything else:

perms=# select proacl from pg_proc where proname = 'f';
                proacl                 

---

 {postgres=X/postgres,joe=X/postgres}

Here we have already revoked execute from PUBLIC, then granted it to joe.

Note that usually functions execute with the permissions of the invoker, but you can create “setuid” functions that execute with the permissions of the definer. These can be very useful, but they have similar dangers to Unix setuid programs, so you have to be careful with them.

Schema Permissions

Schemas have just CREATE and USAGE permissions. CREATE lets you add more stuff to the schema (tables, functions, sequences, etc.). USAGE is a prerequisite to using objects within the schema. For instance to SELECT from a table, you must first have USAGE permission on its schema. (You must also have SELECT permission on the table.) Note that even without USAGE, you can still list the schema contents, and even query for its tables’ definitions etc.

To be honest I find the schema USAGE permission kind of confusing and superfluous, and I don’t think I’m the only one. I can’t think of any security system you can build with this permission you can’t build with permissions on individual tables, functions, etc., and because you can say GRANT ... ON ALL whatevers IN SCHEMA foo, it is not even necessary as an abbreviation. I guess if you really want to lock people out, you might as well use it as an extra layer though.

Conclusion

There are permissions for more specialized database objects too, like types and foreign data wrappers. But if you have made it this far, you should have no problem understanding the documentation for those. You might also want to read about RLS to get really fine-grained access control. Here I’ve only tried to cover the basics, and give you an accurate mental model of how Postgres decides who can do what. If you have any questions, comments, or corrections, I’m happy to listen!

blog comments powered by Disqus Prev: Scaling Sidekiq Next: Where to put Postgres json_each?