When you create the student user, remove their create privs in public.
P
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
On Sun, Dec 4, 2016 at 1:10 PM, Lee Hachadoorian <Lee.Hachadoorian+L@xxxxxxxxx> wrote:
This question is specifically motivated by my use of the PostGIS extension, but since other extensions create functions and other supporting objects in public schema, I believe it is more general.I'm teaching a university-level class using PostGIS. I have created a scratch schema for students to create objects in. At the end of the term I can drop scratch and start fresh the following term.Students of course can also create objects in public schema, and often do unintentionally because the forget to schema qualify their CREATE TABLE statements. This complicates things because I can't drop public schema without dropping various PostGIS (and other) tables and functions. Additionally, while I doubt the students would do something like drop a public function or supporting table (like spatial_ref_sys), it nonetheless seems like a poor idea for these database objects to be vulnerable.What is considered best practices in this case? Should PostGIS extension be kept in its own schema (as was suggested when I asked about this on GIS.SE)? If I do so, can I treat public schema the way I have been using scratch schema, i.e. could I drop and recreate clean public schema at end of term? Should I leave extensions in public but limit rights of public role in that schema (so that they don't unintentionally create tables there, or accidentally delete other objects)? Or do Postgres DBA's just not worry about the objects in public schema, and rely upon applications and login roles to interact with the database intelligently?To be clear, primary goal is to keep student created objects in one schema which can be dropped at the end of the term. But the question of preventing accidental creation/deletion of objects in public schema is possibly related, and the overall database organization might address both concerns.Best,--Lee--Lee Hachadoorian
Assistant Professor of Instruction, Geography and Urban Studies
Assistant Director, Professional Science Master's in GIS
Temple University
