If you need to know who did what to the data and when it was done, one way to do that is to log every action that is performed on an important table.
There are at least two equally valid ways of doing the auditing:
Use auditing triggers
Allow tables to be accessed only through functions, and do the auditing inside these functions
Here, we will take a look at minimal examples of 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 grant only 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 | 1200Each one of those 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 | 1000new_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 following:
REVOKE ALL ON salaries FROM PUBLIC;
Also, give users access to only two functions: the first is for any user looking at salaries and the other is for changing salaries, which is available only to managers.
The functions themselves will have all the access to underlying tables because they are declared as SECURITY DEFINER, which means they run with the privileges of the user who created them.
The salary lookup function will look like the following:
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 for 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 if the user exists; if the user does not, it is created. Setting someone's salary to 0 will remove him from the salary table. Thus, the interface is much simplified 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 (or 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 |