Hello > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Paul Ramsey > Sent: Sonntag, 4. Dezember 2016 22:24 > To: Lee Hachadoorian <Lee.Hachadoorian+L@xxxxxxxxx> > Cc: pgsql-general <pgsql-general@xxxxxxxxxxxxxx> > Subject: 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.Hachadoorian+L@xxxxxxxxx <mailto: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. You could REVOKE CREATE ON SCHEMA public FROM public; So your students would not be able to create objects in the public schema. Bye Charles > > 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 <http://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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
