Postgresql – Setting up a simple “permissions architecture” in PostgreSQL

postgresqlrole

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.

Best Answer

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

Related Question