From: | <org(dot)postgresql(at)io7m(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Options for passing values to triggers? |
Date: | 2013-02-04 19:01:46 |
Message-ID: | 20130204190146.01905562@copperhead.int.arc7.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello.
I'm modelling a system where I'd like to log inserts and deletes
to two or more tables (with foreign key references between them).
As a (contrived) example:
CREATE TABLE projects (
project_id SERIAL PRIMARY KEY,
project_name TEXT UNIQUE NOT NULL
);
CREATE TABLE project_repositories (
repos_id SERIAL PRIMARY KEY,
repos_project INTEGER NOT NULL,
repos_url TEXT UNIQUE NOT NULL,
FOREIGN KEY (repos_project) REFERENCES projects (project_id)
);
CREATE TABLE tasks (
task_id SERIAL PRIMARY KEY,
task_repos INTEGER NOT NULL,
FOREIGN KEY (task_repos) REFERENCES project_repositories (repos_id)
);
And then the log table:
CREATE TABLE audit (
audit_id BIGSERIAL PRIMARY KEY,
audit_time TIMPSTAMP WITH TIME ZONE NOT NULL,
audit_user TEXT NOT NULL,
audit_session TEXT NOT NULL,
audit_type TEXT NOT NULL,
audit_message TEXT NOT NULL
);
Note: The audit_user and audit_session columns are NOT postgresql roles
or sessions; they are from the external application.
So, the intention is that when something is deleted from the projects
table, an event will be recorded of type 'PROJECT_DELETE', including
the name of the project and user responsible for the deletion. Similar
events would be logged for the tasks and project_repositories tables.
Creation would be logged in the same manner.
I'd like to model this using triggers with cascading deletes (so that
when a project is deleted, each one of its repositories is deleted and
logged as having been deleted, and any tasks that depend on those
repositories too).
The problem: I'm not sure what the most pleasant way (or if it's
even possible) to pass 'audit_user' and 'audit_session' to the trigger
functions. The values are created by the external application that
queries the database and aren't otherwise present in the database in
any form.
Furthermore: I'm intending to partition the system into separate roles
such that the role that executes the database queries doesn't have read
or write permission to the audit table (meaning that any logging is
going to have to occur via a function with SECURITY DEFINER).
Any advice or "you don't want to it that way" abuse would be much
appreciated.
M
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2013-02-04 19:10:42 | Re: Options for passing values to triggers? |
Previous Message | Andreas Joseph Krogh | 2013-02-04 18:48:09 | DEFERRABLE NOT NULL constraint |