Setting up a simple “permissions architecture” in PostgreSQL

I host the databases of several clients. For each database dbname, I currently have a dbnameusers role that I grant to the users of that client.

I would now like to have read only “sub roles”.

I created a test database, a role usersro and a user ro (in role usersro).

Then I granted all privileges on the DB to usersro and revoked the CREATE privilege :

# grant all on database test to usersro;
GRANT
# revoke create on database test from usersro;
REVOKE

l shows something that looks correct :

# l
                            List of databases
    Name    |    Owner    | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+-------------+----------+-------------+-------------+-----------------------
 test       | cat         | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/cat              +
            |             |          |             |             | cat=CTc/cat          +
            |             |          |             |             | usersro=Tc/cat

But connecting as user ro, I can still create a table. I guess this comes from the PUBLIC role but I haven’t found a way to see those default privileges.

My guess is that I should revoke all privileges from the PUBLIC role and recreate them in the usersrw role and only part of them in usersro (hence the need to know which are the default ones).

Is this the correct approach ? I have the feeling that PostgreSQL is quite permissive out of the box and that you have to break everything in order to build a multilevel access architecture.

To answer your direct question “if I revoke the CREATE privilege from the PUBLIC role, how do I see that ? “…

Use the following SQL (from this stackoverflow question) to get database-specific privileges for PUBLIC role:

   SELECT nspname,
       coalesce(nullif(role.name,''), 'PUBLIC') AS name,
       substring(
          CASE WHEN position('U' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', USAGE' ELSE '' END 
          || CASE WHEN position('C' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', CREATE' ELSE '' END 
       , 3,10000) AS privileges
FROM pg_namespace pn, (SELECT pg_roles.rolname AS name
   FROM pg_roles UNION ALL SELECT '' AS name) AS role
 WHERE (','||array_to_string(nspacl,',')) LIKE '%,'||role.name||'=%'
 AND nspowner > 1;

Result:

      nspname       |   name   |  privileges   
--------------------+----------+---------------
 pg_catalog         | postgres | USAGE, CREATE
 pg_catalog         | PUBLIC   | USAGE
 information_schema | postgres | USAGE, CREATE
 information_schema | PUBLIC   | USAGE
 test               | postgres | USAGE, CREATE
 public             | postgres | USAGE, CREATE
 **public             | PUBLIC   | USAGE, CREATE**

Now, run the following:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Re-run the first SQL, and we get the following result-set:

   nspname       |   name   |  privileges   
--------------------+----------+---------------
 pg_catalog         | postgres | USAGE, CREATE
 pg_catalog         | PUBLIC   | USAGE
 information_schema | postgres | USAGE, CREATE
 information_schema | PUBLIC   | USAGE
 test               | postgres | USAGE, CREATE
 public             | postgres | USAGE, CREATE
 **public             | PUBLIC   | USAGE**

Compare the last lines from the 2 result-sets. You will see the missing CREATE privilege.

Don’t forget to add the following back:

GRANT  CREATE ON SCHEMA public TO  PUBLIC;

To answer your other direct question : “Does that mean that the public role has all privileges granted (by default) ? “

‘PUBLIC’ is not an explicit role. This is what tripped me when I started Postgres.

Run the following command to confirm this – PUBLIC is absent from this result set:

select * from pg_roles;

Erwin Brandstetter has given a great explanation of PUBLIC role and another even more detailed explanation of PUBLIC role here .

My own notes – just paraphrasing the Postgres documentation on GRANT…

Any particular role will have the sum of following privileges:

1. privileges granted directly to it +
2. privileges granted to any role it is presently a member of +
3. privileges granted to PUBLIC.

This official Postgres post does a great job of explaining how to setup rights:
Managing Rights in Postgres

Just to clarify,

I guess this comes from the PUBLIC role but I haven’t found a way to see those default privileges.

You are right, and you can see PUBLIC privileges in your l output, where your first item of aclitem[] is =Tc/cat and actually it is your PUBLIC privileges according to documentation (on the bottom):

=xxxx/username — privileges granted to PUBLIC by username

And one more moment, do not confuse, PUBLIC privileges and default privileges is something different. Where the first one is privileges that actually granted to all users (yes, it could be done by default if you just created your database) which can be found using different queries for different object types (such as @Sanora provided).
And second one is privileges that will be applied to objects created in the future which you can find in pg_default_acl.

The CREATE privilege on a database controls the ability to create schemas. To control the ability to create tables, you need to control the CREATE privilege on that SCHEMA, not on the DATABASE containing the schema.

REVOKE CREATE on SCHEMA public FROM usersro ;