Returning SQL statement

Поиск
Список
Период
Сортировка
От rlee0001
Тема Returning SQL statement
Дата
Msg-id 1137020262.694897.38930@g44g2000cwa.googlegroups.com
обсуждение исходный текст
Ответы Re: Returning SQL statement  ("Jim Buttafuoco" <jim@contactbda.com>)
Re: Returning SQL statement  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Re: Returning SQL statement  ("rlee0001" <robeddielee@hotmail.com>)
Список pgsql-general
I want to write a row-level trigger in PL/PGSQL that inserts rows into
an audit log whenever records are UPDATEd for a specific table. In my
audit log I want to record:

* The primary key of the record being modified (easy)
* The current date (easy)
* The username of the user (easy)
* The SQL statement used to modify the record

The last one is the most important and it seems to be impossible. Lets
assume the user executes the following statement:

UPDATE inventory SET
   status=5,
   location_detail='somewhere over the rainbow',
   policy=1,
   security_comments='',
   excludes_sms=false,
   excludes_epo=false,
   excludes_ws=false
WHERE asset_serial='jg432lk';

...I want a field in the audit log to actually contain the above
statement text. The only way I can imagine to do it would be to have a
field in "inventory" called "AUDIT_SQL" and have my PHP application
populate that field with the statement (sans the "AUDIT_SQL='...',"
line itself). I can then have my trigger NULL-out this field in the NEW
row. The problem is that several users (including me) use EMS
PostgreSQL Manager to update data as well and I want these manual data
entries to be audited as well. If I don't update the "AUDIT_SQL"
command by hand it just won't get done and NULL will be seen in
"AUDIT_SQL" by any subsequent invokations of the trigger from within
EMS. Or worse user who see this field can falsify it. To keep users
from falisifying the field I could MD5-encode its contents and store
the hash in a seperate "AUDIT_HASH" field and have the trigger perform
an integrity check, canceling the operation if the hash is incorrect.
But the savy (and malicious) user can bypass this check and still
falsify the audit data. Is there a better way to pass such dynamic data
into a trigger procedure? Perhaps there is a way to store data in
"variables"? Something like this:

SET mysqlvariable = 'hello world';
UPDATE ...;

...Then the UPDATE trigger can read my parameters from the variable.
This might be ideal.

So in the meantime, since I can't figure out how to do the SQL thing, I
have my PHP record user activity into the audit log and activity done
from within EMS is not being recorded at all. This is not desirable.

So my question is this: Is there a PostgreSQL function that will return
the statement that triggered the trigger without the end user/PHP
having to provide it? Perhaps a function that will return the last
statement that was executed? Can you even calculate a MD5 hash in
PL/PGSQL (I assume so)?

I'm using PG 8.1. I have access to both PL/PGSQL and (maybe) PL/Perl.


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

Предыдущее
От: Marcos
Дата:
Сообщение: Concept about stored procedures
Следующее
От: "Jim Buttafuoco"
Дата:
Сообщение: Re: Returning SQL statement