Thread: Returning SQL statement
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.
take a look at dblink in the contrib directory, it has a function called dblink_current_query() that returns the current query. I use it all the time. Jim ---------- Original Message ----------- From: "rlee0001" <robeddielee@hotmail.com> To: pgsql-general@postgresql.org Sent: 11 Jan 2006 14:57:42 -0800 Subject: [GENERAL] Returning SQL statement > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org ------- End of Original Message -------
am 11.01.2006, um 14:57:42 -0800 mailte rlee0001 folgendes: > 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: Why do you want to reivent the wheel? http://pgfoundry.org/projects/tablelog/ HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
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. >
Thanks guys but I cannot use C on the database server. I am a lowly coder in a large organization and even getting pl/PGSQL loaded into the production database practically took an act of congress. So for now solutions that require stored procedures to be written in C are not an option. pl/Perl would almost work. They loaded it onto the production database when I requested pl/PGSQL but they didn't load it into my sandbox (development) database. You have no idea has bureaucratic a place can be. :o) I think I'm just going to build a SQL statement by comparing each field in turn between OLD and NEW. Thanks for all your help everyone! Its highly appreciated. Maybe in the next version of postgresql some of this stuff will be built in since appearantly it is so desirable to people. -Robert