Options for passing values to triggers?

Поиск
Список
Период
Сортировка
От
Тема Options for passing values to triggers?
Дата
Msg-id 20130204190146.01905562@copperhead.int.arc7.info
обсуждение исходный текст
Ответы Re: Options for passing values to triggers?  (Pavel Stehule <pavel.stehule@gmail.com>)
Re: Options for passing values to triggers?  (Gurjeet Singh <gurjeet@singh.im>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Andreas Joseph Krogh
Дата:
Сообщение: DEFERRABLE NOT NULL constraint
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: Options for passing values to triggers?