When you create the student user, remove their create privs in public.
Then create a scratch schema and grant them privs there.
Finally, alter the student user so that the scratch schema appears FIRST in their search path. This will cause unqualified CREATE statements to create in the scratch schema.
For full separation, give each student their own login and set the search path to
"$user", public
That way each student gets their own private scratch area, and it is used by default for their creates.
P
Paul,
I've been avoiding giving each student an individual login role, but it might be worth it to consider for a future term.
I've followed your (and Charles') advice to:
REVOKE CREATE ON SCHEMA public FROM public;
ALTER ROLE gus_faculty
SET search_path = scratch,public,tiger;
It also occurred to me that I don't want anyone changing data in spatial_ref_sys. I think I should revoke everything *except* SELECT and REFERENCES, and make this the default for new objects created in public schema:
ALTER DEFAULT PRIVILEGES IN SCHEMA scratch
REVOKE INSERT, UPDATE, DELETE, TRUNCATE, TRIGGER ON TABLES
FROM public;
Please let me know if this is inadvisable or violates accepted practice.
