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
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:
- Is it advisable to limit privileges in
publicschema of PostGIS database?
- 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
publicschema 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.