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?
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 only takes a minute to sign up.Sign up to join this community
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.
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
CREATE public.simple_table(id integer); Query returned successfully with no result in 181 ms.