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.