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?


The documentation of GRANT says the following:


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.


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.


  • very good example! – francs Apr 18 '13 at 6:41
  • How does GRANT CREATE ON SCHEMA public TO schematest; prove the ability of schematest to create objects in the schema public? – Colin 't Hart Apr 18 '13 at 7:52
  • @Colin'tHart "For schemas, allows new objects to be created within the schema." Or I don't understand the question. – dezso Apr 18 '13 at 8:00
  • You used CREATE SCHEMA other_schema; which returned an error to prove that schematest cannot create a new schema. I would've expected CREATE TABLE public.test(col integer); or something similar to prove that schematest can create objects in public. – Colin 't Hart Apr 18 '13 at 8:22
  • Ah, I see - I managed to miss something from copy-paste, thanks for pointing that. – dezso Apr 18 '13 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.