Search Postgresql Archives

Re: Returning SQL statement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx>
To: pgsql-general@xxxxxxxxxxxxxx
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 -------



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux