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. Due to 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 to 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 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 that contains a schema for each department. Within one schema, all users can run the SELECT queries against views...