This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
Database users are separate from the operating system users. The database users are able to call the database management system to perform a retrieval, insertion, deletion, or update of the data. Users are defined within a database for security reasons.
There are database objects in which the users have privileges to. These objects can be any object within a database. They can can be tables, forms, views... etc. The types of privileges a user is given define which commands they are and are not permitted to perform. A superuser is defined to have all privileges.
For instance, the small company named Book Town has a manager named Mark. They also have a sales department with two sales persons, Jessica and William. The accounting department has an accountant, Jennifer, and a book keeper, Jonathan. The accounting department should only have access to the invoice, shipped orders, and customer table. The sales department should also have access to the customer table, but they should only be able to view, but not modify it because this table is owned by the accountant. Sales need access to the inventory table of books. The manager should have access to all tables and an additional employee table. The next table summarizes the relation between employees and tables.
This table shows the employee names and the tables which these employees need access to:
Table 8-1. Book Town Employees
Department | Emp Name | Position | Table Name |
---|---|---|---|
Management | Mark | Manager | employee, ALL tables |
Sales | Jessica | Sales Rep | inventory, customer (only view) |
Sales | William | Sales Rep | inventory(owner), customer (only view) |
Accounting | Jonathan | Book Keeper | invoice (owner), shipped_orders (owner), customer |
Accounting | Jennifer | Accountant | invoice, shipped_orders, customer (owner) |
The previous table is used extensively to show how to define users and their properties. You should keep this relation in mind when reading through the examples in this chapter.
 
Continue to: