PostgreSQL, How to keep only one schema?

Question

I don't want some users be available to create schemas, and only use the public schema.

I'd like to remove the permission of creating schemas, but I can't found anything on PotsgreSQL 9.1 documentation. How can I do this?

Answer 1

The documentation of GRANT says the following:

CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an 
existing object, you must own the object and have this privilege for the containing schema.

So let's simply try this. I created a user for this purpose, and if you look at it, it has the CREATEDB attribute, just for clarifying which CREATE privilege refers to what.

CREATE USER schematest WITH LOGIN CREATEDB PASSWORD 'secure';

REVOKE CREATE ON DATABASE access_test FROM schematest;

GRANT CREATE ON SCHEMA public TO schematest;

These result in the following behaviour:

CREATE SCHEMA other_schema;

ERROR: permission denied for database access_test
SQL state: 42501

So far so good: the user cannot create a new schema. Let's see if it can create objects in the public schema:

CREATE public.simple_table(id integer);

Query returned successfully with no result in 181 ms.

Hooray!