Thread: Extensions and privileges in public schema

Extensions and privileges in public schema

From

Lee Hachadoorian

Date:

05 December 2016, 00:10:45

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

Re: Extensions and privileges in public schema

From

Paul Ramsey

Date:

05 December 2016, 00:24:16

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.

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

Re: Extensions and privileges in public schema

From

"Charles Clavadetscher"

Date:

05 December 2016, 12:58:26

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
>

Re: Extensions and privileges in public schema

From

Lee Hachadoorian

Date:

06 December 2016, 20:12:59

On Sun, Dec 4, 2016 at 4:24 PM, Paul Ramsey <pramsey@cleverelephant.ca> wrote:

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

Paul,

I've been avoiding giving each student an individual login role, but it might be worth it to consider for a future term.

I've followed your (and Charles') advice to:

REVOKE CREATE ON SCHEMA public FROM public;

ALTER ROLE gus_faculty

SET search_path = scratch,public,tiger;

It also occurred to me that I don't want anyone changing data in spatial_ref_sys. I think I should revoke everything *except* SELECT and REFERENCES, and make this the default for new objects created in public schema:

ALTER DEFAULT PRIVILEGES IN SCHEMA scratch

REVOKE INSERT, UPDATE, DELETE, TRUNCATE, TRIGGER ON TABLES

FROM public;

Please let me know if this is inadvisable or violates accepted practice.

Best,

--Lee