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.

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".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.