The Bear's cave

I've seen many weird and wacky ways of creating an audit trail in databases, some better than others. The basic requirements are that users are able to do their jobs, but aren't allowed to do anything they aren't supposed to. And that what they do is logged usefully, securely, and automatically. So, assuming that you have a list of routes, and an order in which they should be used: Users should be able to reorganise the priorities, but not create, delete or amend the routes themselves. The database should also audit these changes, and store them safely in another table which can be viewed by the auditors, but not amended by anyone. Managers should have the ability to update anything about the routes, but again, this should be logged, and visible but not modifiable. Roles can either be users, or groups depending on how they are constructed. Users in the Route Manager group will be able to modify anything about the routes table, and users(only) in the Route Users group will only be able to modify the priorities.

create role route_manager with nologin ;
create role route_user with nologin ;

The next three groups set up the auditing functions. auditd is the master owner, and is only operated by the machine. This ensures that the audit table is not modifiable by any user. Auditor is the group for the auditors: the people who check the data.

create role auditd with nologin ;
create role auditor with nologin ;

Finally, create a couple of "real" users who will be accessing the data.

create role joe_user     with login in role route_user             encrypted password 'his password';
create role dave_manager with login in role route_manager, auditor encrypted password 'his password';

The creation of the route table is quite straight forward. The owner is set to auditd, so that the route_manager cannot subvert the auditing by changing the table. And finally, the groups are granted the rights appropriate to their roles.

create table troute (
    rid serial primary key,
    target text not null,
    cost integer not null
);
alter table troute owner to auditd;
grant select, insert, delete, update on troute to route_manager;
grant select on troute to route_user;

Next, and updater function is created. This is owned by route_manager and allows limited update privileges. Execute is granted to route_users, and the security is set to definer. This is the equivalent of setuid in UNIX, or RunAs in Windows, thus allowing route_user to update the priorities but nothing else.

create or replace function update_route_cost(r integer, c integer) returns integer as $$
declare
    nrows integer;
begin
    select into nrows count(*) from troute where rid = r;
    if nrows = 0 then
        return 0;
    end if;
    update troute set cost = c where rid = r;
    return nrows;
end;
$$ language plpgsql security definer;
alter function update_route_cost(r integer, c integer) owner to route_manager;
grant execute on  function update_route_cost(r integer, c integer) to group route_user;

Once again, setting the Audit table up is quite trivial.

create table troute_aud (
    aid serial primary key,
    mtime timestamp with time zone default current_timestamp,
    act char(1) not null,
    rid integer not null,
    otrg text,
    ntrg text,
    ocost integer,
    ncost integer,
    usr text not null
);
alter table troute_aud owner to auditd;
grant select on troute_aud to auditor;
alter sequence troute_aud_aid_seq owner to auditd;

In a similar way to the last function, the audit function is set up with security definer so that it can run with permission to modify the table. Unlike the ;last one, it returns a trigger datatype, and relies on a couple of implicit objects unique to triggers.

create or replace  function audit_troute() returns trigger as $$
declare
    lrid integer;
begin
    if TG_OP = 'INSERT' then
        insert into troute_aud(act, rid, otrg, ntrg, ocost, ncost, usr)
        values('I', NEW.rid,NULL,NEW.target,NULL,NEW.cost,session_user);
    elseif TG_OP = 'UPDATE' then
        insert into troute_aud(act, rid, otrg, ntrg, ocost, ncost, usr)
        values('U', NEW.rid,OLD.target,NEW.target,OLD.cost,NEW.cost,session_user);
    elseif TG_OP = 'DELETE' then
        insert into troute_aud(act, rid, otrg, ntrg, ocost, ncost, usr)
        values('D', NEW.rid,OLD.target,NULL,OLD.cost,NULL,session_user);
    else
        return NULL;
    end if;
    return NEW;
end;
$$ language plpgsql security definer;
alter function audit_troute() owner to auditd;

And finally, tie the two together.

create trigger tr_route after insert or update or delete on troute for each row execute procedure audit_troute();