Re: Options for passing values to triggers? - Mailing list pgsql-general

On Mon, Feb 4, 2013 at 2:01 PM, <org.postgresql@io7m.com> 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?