Grant CREATE SCHEMA (only) to users

Question

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.

Answer 1

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.

Answer 2

You might want to go about this a different way. Create a script that the teacher can run (assuming the teacher is setup as a dba on the database) that sets all of that up. The script would do everything you need to get your students up and running.

Create Script: Input Username

• Create the user on the database
• Create the schema
• Create needed objects based on template schema
• Load needed data from the template schema
• Grant/Revoke privileges as needed

Teardown Script: Input Username

• Removes Schema
• Removes user

You could make it a little more advanced and allow for an email to be passed in and it can email the students information they need to login to the database. This is assuming your database has access to send email and a reliable email system to use.