If you need to know who did what to the data and when it was done, one way to find out is to log every action that is performed in an important table. In PostgreSQL 9.3, you can also audit the data definition language (DDL) changes to the database using event triggers. We will learn more about this in the later chapters.
There are at least two equally valid ways to perform data auditing:
- Using auditing triggers
- Allowing tables to be accessed only through functions and auditing inside these functions
Here, we will take a look at a minimal number of examples for both the approaches.
First, let's create the tables:
You don't generally want your users to be able to change audit logs, so only grant the managers the right to access these. If you plan to let users access the salary table directly, you should put a trigger on it for auditing:
Now, let's test out some salary management:
Each one of these changes is saved into the salary change log table for auditing purposes:
On the other hand, you may not want anybody to have direct access to the salary table, in which case you can perform the REVOKE
command. The following command will revoke all privileges from PUBLIC
:
Also, give users access to only two functions: the first function is for any user taking a look at salaries and the other function can be used to change salaries, which is available only to managers.
The functions will have all the access to the underlying tables because they are declared as SECURITY DEFINER
, which means that they run with the privileges of the user who created them.
This is how the salary lookup function will look:
Notice that we implemented a soft-security approach, where you can look up other people's salaries, but you have to do it responsibly, that is, only when you need to, as your manager will know that you have checked.
The set_salary()
function abstracts away the need to check whether the user exists; if the user does not exist, it is created. Setting someone's salary to 0
will remove him or her from the salary table. Thus, the interface is simplified to a large extent, and the client application of these functions needs to know, and do, less:
Now, drop the audit
trigger (otherwise the changes will be logged twice) and test the new functionality: