Re: Logging access to data in database table

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> If this protected data is read only using postgres function , and if in
> the same function I add something like "insert into log_table (blah blah
> blah)", somebody could simply do
> begin;
> select * from access_function(); /* assuming access_function is function
> for accessing sensitive data */
> rollback;
> and no info about access would be written in log_table.
>
> Is there some way to enforce insert within function to be always
> performed (I checked and commit can't be called within functions), or is
> there maybe some completely different clever way to solve this problem?

You would need to break out of the transaction somehow within that
function and make a new call to the database, for example using dblink
or plperlu. I've done the latter before and it wasn't too painful.
The general idea is:

- ---
$dbh = DBI->connect(...)
$sth = $dbh->prepare('INSERT into log_table...');
$sth->execute(@values);
$dbh->commit();

Fetch the data as normal, and return to the user.
- ---

Of course, you would want to cache the $dbh and $sth bits.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201201251237
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk8gPgAACgkQvJuQZxSWSsjrhACfSkVNk0OuPdhxNITcxplpygFp
HKcAnjQxliNTime4+DyddOSSV50nNISd
=jqoP
-----END PGP SIGNATURE-----