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@turnstep.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-----

Well,

You could use dblink in your access_function() to log it...

But maybe it would be better to reorganise security on the way that users
who do not need to have access to some data - simply do not have it
(instead of to give them data and latter check log to confirm they have
taken it...)

Depends on concrete case, you could set that security on the table, or you
could set security on table just to 1 power user can read the data. Then
create your access function with SECURITY DEFINER (using power user)... and
the set security which users can select that function...

Kind Regards,

Misa

2012/1/25 Ivan Radovanovic <radovanovic@gmail.com>

In response to Misa Simic <misa.simic@gmail.com>:

But maybe it would be better to reorganise security on the way that users
who do not need to have access to some data - simply do not have it
(instead of to give them data and latter check log to confirm they have
taken it...)

In many cases that's not enough. For example with HIPAA in the US, a user
may be allowed to access data, but there still _has_ to be a log record
for each access.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

In response to Misa Simic <misa.simic@gmail.com>:

But maybe it would be better to reorganise security on the way that users
who do not need to have access to some data - simply do not have it
(instead of to give them data and latter check log to confirm they have
taken it...)

In many cases that's not enough. For example with HIPAA in the US, a user
may be allowed to access data, but there still _has_ to be a log record
for each access.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Thanks Bill,

Make sense... db_link is probably then solution... Everything depends on
concrete problem...

But I still think security should be reconsidered (I would use db_link just
in case there is no other options - if we must let users to have direct
access to DB)... I mean, in that case when we need log each request for
some sensitive data - we would not allow some user direct access to DB
where he would be able to do such thing BEGIN TRAN, execute function what
returns sensitive data, ROLLBACK Tran; (or many other things...)

at least there would be an application layer above DB... (concretly in our
case - Users do not have access to DB at all... everything is through Web
App, actually DB - Web Service - User Apps (Web, Windows, Mobile etc...))

Thanks,

Misa

2012/1/25 Bill Moran <wmoran@potentialtech.com>

On 01/25/12 18:38, Greg Sabino Mullane napisa:

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@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201201251237
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

Thanks for taking your time to reply, we will still consider whether to
use contrib/dblink or plperl, but this idea definitely wasn't something
any of us had in mind :-)

Thanks again,
Ivan

On 01/25/12 20:02, Misa Simic napisa:

Thanks Bill,

Make sense... db_link is probably then solution... Everything depends on
concrete problem...

But I still think security should be reconsidered (I would use db_link
just in case there is no other options - if we must let users to have
direct access to DB)... I mean, in that case when we need log each
request for some sensitive data - we would not allow some user direct
access to DB where he would be able to do such thing BEGIN TRAN, execute
function what returns sensitive data, ROLLBACK Tran; (or many other
things...)

at least there would be an application layer above DB... (concretly in
our case - Users do not have access to DB at all... everything is
through Web App, actually DB - Web Service - User Apps (Web, Windows,
Mobile etc...))

Thanks,

Misa

2012/1/25 Bill Moran <wmoran@potentialtech.com
<mailto:wmoran@potentialtech.com>>

In response to Misa Simic <misa.simic@gmail.com
<mailto:misa.simic@gmail.com>>:

But maybe it would be better to reorganise security on the way

that users

who do not need to have access to some data - simply do not have it
(instead of to give them data and latter check log to confirm

they have

taken it...)

In many cases that's not enough. For example with HIPAA in the US,
a user
may be allowed to access data, but there still _has_ to be a log record
for each access.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Thanks for reply,
Bill was right, this is security requirement that is independent of all
other security mechanisms we have implemented in this system :-)

I will check contrib/dblink - it seems to be one of the ways to solve
this problem

Best regards,
Ivan