Re: Extensions and privileges in public schema

Hello

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Paul Ramsey
> Sent: Sonntag, 4. Dezember 2016 22:24
> To: Lee Hachadoorian <Lee.Hachadoorian+L@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.Hachadoorian+L@gmail.com <mailto: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.

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
>