Setting up the correct data privilege mechanism

PostgreSQL provides a fine-grained privilege system that dictates who can use a particular set of data and how that set of data can be accessed by an approved user. Because of its granular nature, creating an effective set of privileges can be confusing, and may result in undesired behavior. There are different levels of access that can be provided, from controlling who can connect to the database server itself, to who can query a view, to who can execute a PostGIS function.

The challenges of establishing a good set of privileges can be minimized by thinking of the database as an onion. The outermost layer has generic rules and each layer inward applies rules that are more specific than the last. An example of this is a company's database server that only the company's network can access.

Only one of the company's divisions can access database A, which contains a schema for each department. Within one schema, all users can run the SELECT queries...