Why is a new user allowed to create a table?

Question

I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core:

postgres=# \l
                                          List of databases
     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges       
---------------+--------------+-----------+-------------+-------------+-------------------------------
 postgres      | postgres     | SQL_ASCII | C           | C           | 
 project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2
 template0     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
 template1     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
(5 rows)

So far so good. Now I create a user:

postgres=# CREATE ROLE dietrich ENCRYPTED PASSWORD 'md5XXX' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER

Okay. When I try to connect to the database, the user is not allowed to do so:

$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich: 
psql: FATAL:  permission denied for database "project2_core"
DETAIL:  User does not have CONNECT privilege.

This is what I expected. Now the strange stuff starts. I grant the user CONNECT:

postgres=# GRANT CONNECT ON DATABASE project2_core TO dietrich;
GRANT
postgres=# \l
                                          List of databases
     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges       
---------------+--------------+-----------+-------------+-------------+-------------------------------
 postgres      | postgres     | SQL_ASCII | C           | C           | 
 project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2+
               |              |           |             |             | dietrich=c/project2
 template0     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
 template1     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
(5 rows)

And without any further grants, the user is allowed to create a table:

$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich: 
psql (9.2.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

project2_core=> create table adsf ();
CREATE TABLE
project2_core=> \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | adsf | table | dietrich
(1 row)

I would have expected that the user is not allowed to do anything before I explicitly do GRANT USAGEon the schema and then GRANT SELECT on the tables.

Where is my mistake? What am I doing wrong? How can I achieve what I want (that a new user is not allowed to do anything before explicitly granting her the appropriate rights.

I'm lost, and your help is greatly appreciated :)

EDIT Following the advice by @daniel-verite, I now revok all immediately after creating the database. The user dietrich is not allowed to create a table any more. Good. BUT: Now, also the owner of the database, project2, is not allowed to create a table. Even after issuing GRANT ALL PRIVILEGES ON DATABASE project2_core TO project2 and GRANT ALL PRIVILEGES ON SCHEMA public TO project2, I get an error ERROR: no schema has been selected to create in, and when I specifically try to CREATE TABLE public.WHATEVER ();, I get ERROR: permission denied for schema public. What am I doing wrong?

Answer 1

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. Assuming a non-superuser foo_user should be granted this privilege, this should be done with:

GRANT ALL ON schema public TO foo_user;

To know what ALL means for a schema, we must refer to GRANT in the doc, (in PG 9.2 there are no less than 14 forms of GRANT statements that apply to different things...). It appears that for a schema it means CREATE and USAGE.

On the other hand, GRANT ALL PRIVILEGES ON DATABASE... will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables.

Regarding this error: ERROR: no schema has been selected to create in, it happens when trying to create an object without schema qualification (as in create table foo(...)) while lacking the permission to create it in any schema of the search_path.

Answer 2

The crucial thing to understand here is that privileges are not heirachical and are not inherited from containing objects. ALL means all privileges for this object not all privileges for this object and all contained objects.

When you grant ALL on a database, you're granting CREATE, CONNECT, TEMP. These are actions on the database object its self:

• CONNECT: Connect to the DB
• CREATE: Create a schema (not a table)
• TEMP: Create temporary objects, including but not limited to temp tables

Now, each PostgreSQL database by default has a public schema that's created when the database is created. This schema has all rights granted to the role public, of which everybody is implicitly a member. For a schema, ALL means CREATE, USAGE:

• CREATE: Create objects (including tables) within this schema
• USAGE: List objects in the schema and access them if their permissions permit

If you do not specify the schema to create an object like a table in, the database engine uses the search_path, and by default the public schema is first on the search_path so the table is created there. Everyone has rights to public by default, so the creation is allowed. The users' rights on the database are irrelevant at this point, as the user isn't trying to do anything to the database object its self, only a schema within it.

It doesn't matter that you haven't granted the user any rights other than granting CONNECT on the database, because the public schema permits all users to create tables in it by default. Daniel has already explained how to revoke that right if desired.

If you want to delegate every right explicitly, revoke all from public, or simply drop the public schema. You can create a new template database with this change applied if you want to. Alternately you can apply it to template1, but that'll likely break lots of 3rd party code that assumes that public exists and is writeable.

---

This might make more sense if you look at a filesystem analogy.

If I have the directory structure (mode simplified to only show the mode that applies to the current user):

/dir1           mode=r-x
/dir1/dir2      mode=rwx

then I cannot create anything within /dir1, because I do not have write permission. So if I touch /dir1/somefile I will get a permission denied error.

However, I do have permission to look inside /dir1 and to access contained files and directories, including /dir1/dir2. I have write permission on dir2. So touch /dir1/dir2/somefile will succeed, even though I don't have write permission to dir1.

Same thing with databases and schemas.

Answer 3

If you only want to prevent new users from creating tables, you need to run the following command:

REVOKE CREATE ON SCHEMA public FROM public;

If you REVOKE ALL (as other answers suggest), you will also prevent users from having USAGE permissions. USAGE means that users can use the permissions assigned to them, so if you remove that then your users will not be able to list or access tables that they have access to.

Alternatively, you could also REVOKE CREATE for a specific user:

REVOKE CREATE ON schema public FROM myuser;

See also: How to create a read only user with PostgreSQL.