9.24

Suppose you wish to create an audit trial of changes to the takes relation.

  1. Define triggers to create an audit trail, logging the information into a relation called, for example, takes_trial. The logged information should include the user-id (assume a function user_id() provides this information) and a timestamp, in addition to old and new values. You must also provide the schema of the takes_trail relation.

  2. Can the preceding implementation guarantee that updates made by a malicious database administrator (or someone who manages to get the administrator’s password) will be in the audit trail? Explain your answer.

  1. Define triggers to create an audit trial, logging the information into a relation called, for example, takes_trial. The logged information should include the user-id (assume a function user_id() provides this information) and a timestamp, in addition to old and new values. You must also provide the schema of the takes_trail relation.

The following uses Postgresql server 13.8.

The following paragraph is taken from Postgresql documentation Chapter 39 Triggers.

The trigger function must be defined before the trigger itself can be created. The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure, not in the form of ordinary function arguments.)

CREATE TYPE OPERATION_TYPE AS ENUM ('insert', 'update', 'delete');

CREATE TABLE takes_trail ( 
    id                   BIGSERIAL PRIMARY KEY, 
    userid               VARCHAR(5),  -- the user id that performed the change. 
    change_occured_at    TIMESTAMP WITH TIME ZONE, -- timestamp when the change occured. 
    old_value            JSONB DEFAULT NULL, -- the old tuple of takes encoded as json
    new_value            JSONB DEFAULT NULL, -- the new tuple of takes encoded as json
    change_type          OPERATION_TYPE NOT NULL -- type of change that occured on the takes relation
);

CREATE FUNCTION audit_trail_takes() RETURNS trigger AS $audit_trail_takes$
    BEGIN 
    --
    -- Create a row in takes_trail to reflect the operation performed 
    -- on takes, making use of the special variable TG_OP to work out 
    -- the operation. 
    -- 
    IF (TG_OP = 'DELETE') THEN 
        INSERT INTO takes_trail (userid, change_occured_at, old_value, change_type)
        VALUES (user_id(), now(), to_json(OLD), 'delete');
    ELSIF (TG_OP = 'UPDATE') THEN 
        INSERT INTO takes_trail (userid, change_occured_at, old_value, new_value, change_type)
        VALUES (user_id(), now(), to_json(OLD), to_json(NEW), 'update');
    ELSIF (TG_OP = 'INSERT') THEN 
        INSERT INTO takes_trail (userid, change_occured_at, new_value, change_type)
        VALUES (user_id(), now(), to_json(NEW), 'insert');
    END IF;
    RETURN NEW;

    END;
$audit_trail_takes$ LANGUAGE plpgsql;

CREATE TRIGGER audit_trail_takes
AFTER INSERT OR UPDATE OR DELETE ON takes
    FOR EACH ROW EXECUTE FUNCTION audit_trail_takes();

After an update, delete and insert operations on the takes relation the query

SELECT * FROM takes_trail

results:-

 id | userid |       change_occured_at       |                                               old_value                                               |                                               new_value                                               | change_type 
----+--------+-------------------------------+-------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+-------------
  1 | NSR56  | 2022-08-21 13:50:03.638944+03 | {"id": "00128", "year": 2017, "grade": "A", "sec_id": "1", "semester": "Fall", "course_id": "CS-101"} | {"id": "00128", "year": 2017, "grade": "B", "sec_id": "1", "semester": "Fall", "course_id": "CS-101"} | update
  2 | NSR56  | 2022-08-21 13:51:16.101672+03 | {"id": "00128", "year": 2017, "grade": "B", "sec_id": "1", "semester": "Fall", "course_id": "CS-101"} |                                                                                                       | delete
  3 | NSR56  | 2022-08-21 13:52:07.583393+03 |                                                                                                       | {"id": "00128", "year": 2017, "grade": "C", "sec_id": "1", "semester": "Fall", "course_id": "CS-101"} | insert
(3 rows)

Note that I have replaced the non-existent function user_id() in the definition of the plpgsql function by the string ‘NSR56’.

I used json data types for the attributes old_value and new_value because the schema of the takes relation can change over time. The trigger code given above would still work in such a scenario.

  1. Can the preceding implementation guarantee that updates made by a malicious database administrator (or someone who manages to get the administrator’s password) will be in the audit trail? Explain your answer.

It cannot guarantee that updates made by a malicious database administrator, will be in the audit trail. This is because the database admin will have the privilege to delete rows from the takes_trail relation.