Обсуждение: Options for passing values to triggers?
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
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
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?