Request a Demo
Home   >   mailing lists

Re: Extensions and privileges in public schema - Mailing list pgsql-general

From Paul Ramsey
Subject Re: Extensions and privileges in public schema
Date
Msg-id CACowWR0WBp1f_LJ=J8E8pm8fCe+MiP-YBFqe7Q60Gqkcmahp0A@mail.gmail.com
Whole thread
In response to Extensions and privileges in public schema  (Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>)
Responses Re: Extensions and privileges in public schema
Re: Extensions and privileges in public schema
List pgsql-general
Tree view
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@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.

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

pgsql-general by date:

Previous
From: Lee Hachadoorian
Date:
Subject: Extensions and privileges in public schema
Next
From: Peter Geoghegan
Date:
Subject: Re: Avoid sorting when doing an array_agg