Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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!

share|improve this answer
    
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
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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