So we needed to add a Postgres user that could only read tables from our database--not only the currently existing tables, but any tables added in the future--and I found Postgres's permissions system surprisingly complex and unfriendly.

Sure, everything you need to know is buried throughout multiple manual pages and tons of Stack Overflow answers, among other places, but I didn't feel there was anything out there that brought together all the disparate sources into one easy to follow beginner's guide to understanding Postgres permissions, so here we are, the easy guide to creating permanent read-only users in PostgreSQL¹.

Before we dive in, I'm using version 9.3 of Postgres, on Ubuntu 14.04:

$ psql --version
psql (PostgreSQL) 9.3.11

So all this information will apply to that version of Postgres, and all the commands will work on that version of Ubuntu. I'm not sure what changes, if any, would need to be made for earlier, later, or different versions of Postgres and Linux.

Structure

It really helps to understand some of the different data structures that come into play when messing with Postgres's permissions, by default, every database you create has a default schema named public, the schema you use is incredibly important and could be a great source of frustration and annoyance² as you mess with the users and try and set their permissions.

But it won't be frustrating for us because we're going to learn by example, on a fresh install of Postgres, let's create our two users, our owner foo and our read only user readonly:

$ sudo -u postgres psql -c "CREATE USER foo WITH ENCRYPTED PASSWORD '...'" -d template1
$ sudo -u postgres psql -c "CREATE USER readonly WITH ENCRYPTED PASSWORD '...'" -d template1

and let's have our new foo user own our two databases:

$ sudo -u postgres createdb -E UTF8 --locale=en_US.UTF-8 -O foo db1
$ sudo -u postgres createdb -E UTF8 --locale=en_US.UTF-8 -O foo db2

Now, let's create a table in db1:

$ psql -U foo db1
db1=> CREATE TABLE bar1 (_id INT);
CREATE TABLE
Time: 8.384 ms
db1=> \q

and a different table in db2:

$ psql -U foo db2
db2=> CREATE TABLE bar2 (_id INT);
CREATE TABLE
Time: 9.042 ms
db1=> \q

So now database db1 should have a bar1 table, and database db2 should have a bar2 table. Now we're ready to understand the public schema of the database.

What we are going to do is connect to db1 and list its tables, then connect to db2 and list its tables:

vagrant@vagrant:~$ psql -U foo db1
db1=> \d
List of relations
-[ RECORD 1 ]--
Schema | public
Name   | bar1
Type   | table
Owner  | foo

db1=> \c db2
You are now connected to database "db2" as user "foo".
db2=> \d
List of relations
-[ RECORD 1 ]--
Schema | public
Name   | bar2
Type   | table
Owner  | foo

Notice that each table belongs to schema public, but each public schema has a different table. So now we understand that when we mess with the public schema, we need to be cognizant of what database we are connected to because any changes we make to the public schema will only affect the public schema of the connected database.

The second thing we should undertand is Postgres seems to use role, group, and user interchangeably:

The concept of roles subsumes the concepts of "users" and "groups". In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both.

We're only ever going to use user here, but when looking at different sources of information, you might find people using role or, less likely, group³.

The readonly user

We want the readonly user to be able to read all tables from db1. Before we do anything, let's see where we stand:

$ psql -U readonly db1
db1=> select * from bar1;
ERROR:  permission denied for relation bar1
Time: 1.043 ms

One of the first things you noticed (hopefully) is our readonly user could connect to db1 without doing anything, this is because users have CONNECT privileges by default, but even though readonly could connect to db1, we couldn't look at the table because we hadn't given readonly any privileges to do so.

OK, so we better dig into the manual and learn all about the GRANT query, and since we've already spent some time learning about schemas previously, we understand each of our databases has a separate public schema, and those schemas contain our tables, so we probably want to grant some permissions to readonly in our public schema, something like:

$ sudo -u postgres psql -d db1 -c "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly"
$ sudo -u postgres psql -d db1 -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly"

Let's test it out:

$ psql -U readonly db1
db1=> select * from bar1;
(No rows)
Time: 0.758 ms

db1=> \c db2
You are now connected to database "db2" as user "readonly".
db2=> select * from bar2;
ERROR:  permission denied for relation bar2
Time: 0.939 ms

It looks like it worked, but just to be sure, let's double check, first, we'll try and create a new table:

$ psql -U readonly db1
db1=> CREATE TABLE bar3 (_id INT);
CREATE TABLE
Time: 6.279 ms
db1=>

Oh snap! That shouldn't have worked, what the heck? Well, let's dig into the permissions of the public schema:

$ psql -U readonly db1
db1=> \dn+
List of schemas
-[ RECORD 1 ]-----+-----------------------
Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
                  | =UC/postgres
Description       | standard public schema

What does that =UC/postgres mean? I'm glad you asked, the answer can be found, of course, in the manual:

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            U -- USAGE
            C -- CREATE

        /yyyy -- role that granted this privilege

Huh, the public schema has CREATE privileges by default, well that was unexpected. So we need to remove those privileges from the readonly user, should be easy enough using a revoke query:

$ sudo -u postgres psql db1
psql (9.3.14)
Type "help" for help.

db1=# REVOKE CREATE ON SCHEMA public FROM readonly;
REVOKE
Time: 3.782 ms
db1=#

Easy peasy, let's try again:

$ psql -U readonly db1
db1=> CREATE TABLE bar4 (_id INT);
CREATE TABLE
Time: 7.259 ms

WTF?

Turns out, revoking a privilege from the user directly doesn't override the granted privileges on public. Why? I don't know:

@user3669651: But it's not the same. Revoking privileges from readonly2 doesn't change the fact that every user can create tables in the public schema until you revoke from role public ... – Erwin Brandstetter Jun 20 '14 at 23:32

It really doesn't make any sense to me, but this is the reality we have to deal with, and we need our readonly user to not be able to create tables, so we have to change up the public schema:

$ sudo -u postgres psql -d db1 -c "REVOKE CREATE ON SCHEMA public FROM public"

And, of course, we'll want to give all the permissions back to our owner, because if we don't, they won't be able to create tables anymore (go ahead, ask me the fun way I discovered this):

$ sudo -u postgres psql -d db1 -c "GRANT ALL ON schema public TO foo"

Now, our readonly user shouldn't be able to create tables anymore:

$ psql -U readonly db1
db1=> CREATE TABLE bar5 (_id INT);
ERROR:  permission denied for schema public
Time: 1.082 ms

Boom, we're finally making progress! I'm sure it's just a formality, but let's make sure we can read new tables also:

$ psql -U foo db1
db1=> CREATE TABLE bar5 (_id INT);
CREATE TABLE
Time: 7.252 ms
db1=> \q
$ psql -U readonly db1
db1=> select * from bar5;
ERROR:  permission denied for relation bar5
Time: 0.950 ms

And we were doing so well.

So now we'll need to spend some more time researching, where we'll eventually realize that granting permissions only works for things that existed the moment those permissions were granted, but if we wanted to have those permissions moving forward for all the new things, we'll need to alter the default privileges that are applied when those new things are created, so let's do that:

$ sudo -u postgres psql -d db1 -c "ALTER DEFAULT PRIVILEGES FOR USER foo IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO readonly"
$ sudo -u postgres psql -d db1 -c "ALTER DEFAULT PRIVILEGES FOR USER foo IN SCHEMA public GRANT SELECT ON TABLES TO readonly"

These are very similar to our original GRANT queries (which feels like we executed a lifetime ago now).

Let's make sure the queries worked:

$ psql -U foo db1
db1=> CREATE TABLE bar6 (_id INT);
CREATE TABLE
Time: 5.025 ms
db1=> \q
$ psql -U readonly db1
db1=> select * from bar6;
(No rows)
Time: 0.816 ms

Well what do you know, success!

The most important part of those ALTER DEFAULT commands is the ...FOR USER foo..., if you don't have that part they won't work (which would lead to quite a while trying to figure out why the query does nothing, not that I would know), and it's also important to run those queries for any user that is going to be creating stuff in that database, because:

$ sudo -u postgres psql db1
db1=# CREATE TABLE bar7 (_id INT);
CREATE TABLE
Time: 5.430 ms
db1=# \q
$ psql -U readonly db1
db1=> select * from bar7;
ERROR:  permission denied for relation bar7
Time: 0.920 ms
db1=> \q

So you would need to run the ALTER DEFAULT queries with ...FOR USER postgres... in order for the readonly user to be able to read any tables created in db1 by the postgres user.

Well, we made it, after all that, I feel like we've really grown as people, and as friends. Hopefully this little trip into the abyss that is Postgres's permissions has been helpful :)