Hello > -----Original Message----- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Paul Ramsey > Sent: Sonntag, 4. Dezember 2016 22:24 > To: Lee Hachadoorian <lee.hachadooria...@gmail.com> > Cc: pgsql-general <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] 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.hachadooria...@gmail.com <mailto:lee.hachadooria...@gmail.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.
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
