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:
- Is it advisable to limit privileges in
public
schema 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
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.