Re: Returning SQL statement - Mailing list pgsql-general

You might want to consider an inheritance model for this.... I use it for
Sarbanes-Oxley and a viurtual rollback system...

http://www.postgresql.org/docs/8.0/interactive/tutorial-inheritance.html


"rlee0001" <robeddielee@hotmail.com> wrote in message
news:1137020262.694897.38930@g44g2000cwa.googlegroups.com...
>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.
>