Postgresql – Grant CREATE SCHEMA (only) to users

permissionspostgresqlpostgresql-9.6

Imagine I have one shared database set up for my class that each student has the potential to access (access to the database is open and only requires kerberos auth). The public space is where I place all my default course material (tables, views, etc), most of which can be accessed by anyone interested.

What I would like is for:

  1. each student (any authenticated user) to be able to create their own schema and use that as the scope where they can create tables and views
  2. students to not be able to create tables/views/objects in public
  3. to apply these settings to all all users; or more specifically, not have to target specific user accounts when assigning permissions

Thought process: 

REVOKE ALL ON DATABASE class FROM PUBLIC
GRANT CREATE ON DATABASE class TO PUBLIC  -- can it be CREATE SCHEMA ?
-- being able to create on database makes it seem like they can create in public
-- can permissions be revoked from public?

As user:

psql -h <host> class

-- desire: permitted
CREATE SCHEMA <current_user>;
GRANT USAGE ON <current_user> TO teacher;
CREATE TABLE <current_user>.<table name> ...; 
SELECT * FROM public.<table name>;            
SELECT * FROM <current_user>.<table name>;    

-- desire: not permitted
CREATE TABLE public.<table name> ...;

I'm not sure those are the proper permissions. I was also curious if I could create a roll and use the role to administer permissions. The tricky bit is I would like any authenticated user to be part of that role, which I don't think you can add public to a role, so I don't know how that would work.

I haven't thought this through thoroughly, but appreciate any feedback, concerns, and answers offered. Of course, I'm available to answer questions for clarification — being concise while still offering comprehensive details is a challenge.

Best Answer

Postgres is quite good at doing such things, this is one reason I like it.

You can create an arbitrarily complex role hierarchy, but for our purposes a simple one will be enough:

CREATE ROLE student;

REVOKE CREATE ON SCHEMA public FROM public;  -- the second public is a pseudorole
                                             -- every role (user, group) is a member of
REVOKE CREATE ON DATABASE class FROM public; -- disallows schema creation, see below

GRANT USAGE ON SCHEMA public TO student;

At this point, student is a role that cannot log in. We will use it as the privilege collector for the actual students, which you will have to create like this:

CREATE USER jsmith IN ROLE student;  -- will be able to log in, 
                                     -- and a member of the student role

You can put the latter two statements into a stored function/procedure (which will include some dynamic SQL), or a script in your second favourite language.

This way your students will be able to create a schema for themselves (the name can be anything), and as they own that schema, they can also create objects in it. If you want to restrict them to a schema named after them, do the following after the CREATE USER:

CREATE SCHEMA jsmith AUTHORIZATION jsmith;

To allow access for the teacher to all student schemas and objects, the easiest solution is:

GRANT student TO teacher;

What remains now is to allow students to access the existing and future tables in the public schema:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO student;
ALTER DEFAULT PRIVILEGES IN SCHEMA public FOR ROLE teacher 
      GRANT SELECT ON TABLES TO student;

The last command assumes the tables will be created by the user/role teacher.

Related Question