5

When a PostgreSQL database is spatially enabled with PostGIS, PostGIS creates metadata tables (spatial_ref_sys), views (geometry_columns), and many, many functions in the public schema. As is usual practice, I keep actual data tables outside of the public schema.

I use PostGIS to teach a course on spatial databases. The students necessarily have privileges to much of the database, although they can only alter data or create objects in certain schemas. By default, the public role has full access to the public schema. It occurs to me that this should be curtailed. Not only do I want students to not make inappropriate changes or deletions to data and objects in this schema, I would also like their "experiments" to be contained within particular schemas (named for a course code, or to have one scratch schema for all courses), so that I can easily drop an entire schema at the end of the semester.

However, I have not seen discussion before in PostGIS or PostgreSQL mailing lists or forums about limiting privileges in the public schema, and I am concerned about the impact. My questions are:

  1. Is it advisable to limit privileges in public schema of PostGIS database?
  2. If I do so, what would be the correct set of privileges and/or default privileges that would keep all (existing and future) tables and functions in the public schema available to all users, but unable to be altered in their data or their definition?

On various servers I am running PostGIS 2.1 or 2.2 on various flavors of Linux, but the question applies to all versions and platforms.

Improve this question
2
  • Hi Lee, have you thought about creating individual databases for each student/group and create a 'student' role which owns these databases? Of course, you have to create the PostGIS extension for all databases, but it can be scripted anyway.
    – Greg Z
    Oct 11, 2016 at 18:09
  • The students are also installing PostGIS on personal laptops that they can completely control. The purpose of the shared server is to give students across several classes access to a common set of data sources (Census, local infrastructure, etc.). So this suggestion, while valid for some use cases, would not I think be useful for our situation.
    – Lee Hachadoorian
    Oct 11, 2016 at 20:47

1 Answer 1

Reset to default
2

Well, you can easily move postgis to other schema:

CREATE EXTENSION POSTGIS;
CREATE SCHEMA postgis;
ALTER EXTENSION postgis SET SCHEMA postgis;

After that, be careful with search_path, etc....

Improve this answer
2
  • This is not quite the question I was asking, although relevant. Are you suggesting continuing to use public schema as a place where students could create experimental tables and functions, i.e. what I have proposed to segregate in a scratch schema?
    – Lee Hachadoorian
    Oct 12, 2016 at 17:57
  • 1
    I think that is not o GIS problem but a BD (privilege) one. You can create a schema for each student and each one own that schema. Your data can be in a schema with only SELECT privilege for the students. Students are members of a Group Role "student" to grant more easily.... etc. You can also revoke privileges on some public objects for student group.
    – WKT
    Oct 12, 2016 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.