In this recipe, we granted and revoked privileges based on the group or user, with security increasing as a group or user descends into the database. This resulted in group1 and group2 being able to connect to the chapter10 database and use objects found in the postgis schema. group1 could also insert new records into the spatial_ref_sys table. Only user2 was permitted to update or delete the records of spatial_ref_sys.

The GRANT and REVOKE statements used in this recipe work, but they can be tedious to use with a command-line utility, such as psql. Instead, use a graphical tool, such as pgAdmin, that provides a grant wizard. Such tools also make it easier to check the behavior of the database after granting and revoking privileges.

For additional practice, set up the privileges on the public schema and child objects so that, although group1 and group2 will be able to run the SELECT queries on the tables, only group2 will be able to use the INSERT statement on the caschools table. You will also want to make sure that an INSERT statement executed by a user of group2 actually works.