Re: Extensions and privileges in public schema

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

On Sun, Dec 4, 2016 at 1:10 PM, Lee Hachadoorian <
Lee(dot)Hachadoorian+L(at)gmail(dot)com> 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
>