1

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.

2 Answers 2

0

If you have logging properly configured (refer to option 18.8.2. When To Log. PostgreSQL: Documentation: 9.1: Error Reporting and Logging | postgresql.org) and your log destination is syslog, then you can use RAISE to log something. More info can be found here: PostgreSQL: Documentation: 8.3: Errors and Messages | postgresql.org

0

For my knowledge there is not direkt way to generate syslog messages from triggers but you could think about external programs for this task. but see: Calling external program from PostgreSQL trigger if you think about external programs inside trigger. There would be an option if you insert messages into an audit-table inside your database and you create an extra task (polling :-( to check for events like "adding or removing user".

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .