Auditing changes
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:
CREATE TABLE salaries(
emp_name text PRIMARY KEY,
salary integer NOT NULL
);
CREATE TABLE salary_change_log(
changed_by text DEFAULT CURRENT_USER,
changed_at timestamp DEFAULT CURRENT_TIMESTAMP,
salary_op text,
emp_name text,
old_salary integer,
new_salary integer
);
REVOKE ALL ON salary_change_log FROM PUBLIC;
GRANT ALL ON salary_change_log TO managers;
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:
CREATE OR REPLACE FUNCTION log_salary_change () RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO salary_change_log(salary_op,emp_name,new_salary)
VALUES (TG_OP,NEW.emp_name,NEW.salary);
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO salary_change_log(salary_op,emp_name,old_salary,new_salary)
VALUES (TG_OP,NEW.emp_name,OLD.salary,NEW.salary);
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO salary_change_log(salary_op,emp_name,old_salary)
VALUES (TG_OP,NEW.emp_name,OLD.salary);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER audit_salary_change
AFTER INSERT OR UPDATE OR DELETE ON salaries
FOR EACH ROW EXECUTE PROCEDURE log_salary_change ();
Now, let's test out some salary management:
postgres=# INSERT INTO salaries values('Bob',1000);
INSERT 0 1
postgres=# UPDATE salaries SET salary = 1100 WHERE emp_name = 'Bob';
UPDATE 1
postgres=# INSERT INTO salaries VALUES('Mary',1000);
INSERT 0 1
postgres=# UPDATE salaries SET salary = salary + 200;
UPDATE 2
postgres=# SELECT * FROM salaries;
-[ RECORD 1 ]--
emp_name | Bob
salary | 1300
-[ RECORD 2 ]--
emp_name | Mary
salary | 1200
Each one of these changes is saved into the salary change log table for auditing purposes:
postgres=# SELECT * FROM salary_change_log; -[ RECORD 1 ]-------------------------- changed_by | frank changed_at | 2012-01-25 15:44:43.311299 salary_op | INSERT emp_name | Bob old_salary | new_salary | 1000 -[ RECORD 2 ]-------------------------- changed_by | frank changed_at | 2012-01-25 15:44:43.313405 salary_op | UPDATE emp_name | Bob old_salary | 1000 new_salary | 1100 -[ RECORD 3 ]-------------------------- changed_by | frank changed_at | 2012-01-25 15:44:43.314208 salary_op | INSERT emp_name | Mary old_salary | new_salary | 1000 -[ RECORD 4 ]-------------------------- changed_by | frank changed_at | 2012-01-25 15:44:43.314903 salary_op | UPDATE emp_name | Bob old_salary | 1100 new_salary | 1300 -[ RECORD 5 ]-------------------------- changed_by | frank changed_at | 2012-01-25 15:44:43.314903 salary_op | UPDATE emp_name | Mary old_salary | 1000 new_salary | 1200
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:
REVOKE ALL ON salaries 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:
CREATE OR REPLACE FUNCTION get_salary(text)
RETURNS integer
AS $$
-- if you look at other people's salaries, it gets logged
INSERT INTO salary_change_log(salary_op,emp_name,new_salary)
SELECT 'SELECT',emp_name,salary
FROM salaries
WHERE upper(emp_name) = upper($1)
AND upper(emp_name) != upper(CURRENT_USER);
-- don't log select of own salary
-- return the requested salary
SELECT salary FROM salaries WHERE upper(emp_name) = upper($1);
$$ LANGUAGE SQL SECURITY DEFINER;
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:
CREATE OR REPLACE FUNCTION set_salary(i_emp_name text, i_salary int)
RETURNS TEXT AS $$
DECLARE
old_salary integer;
BEGIN
SELECT salary INTO old_salary
FROM salaries
WHERE upper(emp_name) = upper(i_emp_name);
IF NOT FOUND THEN
INSERT INTO salaries VALUES(i_emp_name, i_salary);
INSERT INTO salary_change_log(salary_op,emp_name,new_salary)
VALUES ('INSERT',i_emp_name,i_salary);
RETURN 'INSERTED USER ' || i_emp_name;
ELSIF i_salary > 0 THEN
UPDATE salaries
SET salary = i_salary
WHERE upper(emp_name) = upper(i_emp_name);
INSERT INTO salary_change_log
(salary_op,emp_name,old_salary,new_salary)
VALUES ('UPDATE',i_emp_name,old_salary,i_salary);
RETURN 'UPDATED USER ' || i_emp_name;
ELSE -- salary set to 0
DELETE FROM salaries WHERE upper(emp_name) = upper(i_emp_name);
INSERT INTO salary_change_log(salary_op,emp_name,old_salary)
VALUES ('DELETE',i_emp_name,old_salary);
RETURN 'DELETED USER ' || i_emp_name;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
Now, drop the audit trigger (otherwise the changes will be logged twice) and test the new functionality:
postgres=# DROP TRIGGER audit_salary_change ON salaries;
DROP TRIGGER
postgres=#
postgres=# SELECT set_salary('Fred',750);
-[ RECORD 1 ]------------------
set_salary | INSERTED USER Fred
postgres=# SELECT set_salary('frank',100);
-[ RECORD 1 ]-------------------
set_salary | INSERTED USER frank
postgres=# SELECT * FROM salaries ;
-[ RECORD 1 ]---
emp_name | Bob
salary | 1300
-[ RECORD 2 ]---
emp_name | Mary
salary | 1200
-[ RECORD 3 ]---
emp_name | Fred
salary | 750
-[ RECORD 4 ]---
emp_name | frank
salary | 100
postgres=# SELECT set_salary('mary',0);
-[ RECORD 1 ]-----------------
set_salary | DELETED USER mary
postgres=# SELECT * FROM salaries ;
-[ RECORD 1 ]---
emp_name | Bob
salary | 1300
-[ RECORD 2 ]---
emp_name | Fred
salary | 750
-[ RECORD 3 ]---
emp_name | frank
salary | 100
postgres=# SELECT * FROM salary_change_log ;
...
-[ RECORD 6 ]--------------------------
changed_by | gsmith
changed_at | 2013-01-25 15:57:49.057592
salary_op | INSERT
emp_name | Fred
old_salary |
new_salary | 750
-[ RECORD 7 ]--------------------------
changed_by | gsmith
changed_at | 2013-01-25 15:57:49.062456
salary_op | INSERT
emp_name | frank
old_salary |
new_salary | 100
-[ RECORD 8 ]--------------------------
changed_by | gsmith
changed_at | 2013-01-25 15:57:49.064337
salary_op | DELETE
emp_name | mary
old_salary | 1200
new_salary |