2013/2/4 <org.postgresql@io7m.com>:
> 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.
Moving and too "smart" logic to triggers is usually bad idea
better REVOKE DELETE rights for application users and implement
security definer stored procedures, that ensure correct deleting with
correct auditing.
Regards
Pavel Stehule
>
> M
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
