Re: Options for passing values to triggers?
От | Gurjeet Singh |
---|---|
Тема | Re: Options for passing values to triggers? |
Дата | |
Msg-id | CAH_BQteR5dNsgP2wDSZUUsnvJ_jzXQ1Bq2a867rx58mysWW98Q@mail.gmail.com обсуждение исходный текст |
Ответ на | Options for passing values to triggers? (<org.postgresql@io7m.com>) |
Список | 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?
В списке pgsql-general по дате отправления:
Следующее
От: David WooffindinДата:
Сообщение: PGAdmin on Windows (yeah, i know!!) and registry/.ini questions