Re: Options for passing values to triggers?

[Gurjeet Singh <gurjeet@xxxxxxxx>]

On Mon, Feb 4, 2013 at 2:01 PM, <org.postgresql@xxxxxxxx> wrote:

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.

I have no opinion of whether this is the right way of going abut it, but here's a way it can be done. Recent versions of postgres allow you to set arbitrary session level variables, so you can use SQL commands to set/get these variables.

.) At the start of a session, set the app user name in a variable

SET my_app.audit_user = 'app_user_1';

.) Inside your trigger function:

current_app_user = select current_setting('my_app.audit_user');

PS:
Question to PG-hackers: Why are such variables not visible in pg_settings view?