I have a program which uses these tables, and I want to add some additional functionality to its logging without modifying the program.
groups
------
id bigint not null
name character varying(100) not null
users
-----
id bigint not null
name character varying(100) not null
users_groups
------------
group_id bigint not null
user_id bigint not null
I want to write into syslog6 a "user123 added to group456" or "user123 removed from group456" message every time a user added or removed from a group.
My first idea was using PostgreSQL triggers.
CREATE OR REPLACE FUNCTION process_ext_audit()
RETURNS trigger AS $ext_audit$
BEGIN
IF (TG_OP = 'DELETE') THEN
SELECT name
into uname
FROM users
WHERE id = OLD.user_id;
SELECT name
into gname
FROM groups
WHERE id = OLD.group_id;
-- write into local6: "uname removed from gname"
ELSIF (TG_OP = 'INSERT') THEN
SELECT name
into uname
FROM users
WHERE id = NEW.user_id;
SELECT name
into gname
FROM groups
WHERE id = NEW.group_id;
-- write into local6: "uname added to gname"
END IF;
RETURN NULL;
END;
$ext_audit$ LANGUAGE plpgsql;
CREATE TRIGGER ext_audit
AFTER INSERT OR DELETE ON users_groups
FOR EACH ROW EXECUTE PROCEDURE process_ext_audit();
Is my approach good? If yes, how can I write into syslog from this function?
I use postgresql 9.2 with CentOS 7 which uses rsyslog.
