Extensions and privileges in public schema

From: Lee Hachadoorian <Lee(dot)Hachadoorian+L(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Extensions and privileges in public schema
Date: 2016-12-04 21:10:38
Message-ID: CANnCtnLHvKQ6pisNVbHeDhGOr-6uiHb+1nYU_Lo5Bqae9TWgng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Paul Ramsey 2016-12-04 21:24:07 Re: Extensions and privileges in public schema
Previous Message Adrian Klaver 2016-12-04 20:25:24 Re: Postgres and LibreOffice's 'Base'