Re: Logging access to data in database table

Hello,

I need to log access to certain data in database in some log (I prefer
to have that both in syslog and table in database), and I find it easy
to write to syslog, but I can't solve the problem of writing this to
database table.

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?

Thanks in advance,
Ivan

-----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-----

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(at)gmail(dot)com>

> Hello,
>
> I need to log access to certain data in database in some log (I prefer to
> have that both in syslog and table in database), and I find it easy to
> write to syslog, but I can't solve the problem of writing this to database
> table.
>
> 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?
>
> Thanks in advance,
> Ivan
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>

In response to Misa Simic <misa(dot)simic(at)gmail(dot)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(dot)simic(at)gmail(dot)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(at)potentialtech(dot)com>

> In response to Misa Simic <misa(dot)simic(at)gmail(dot)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/
>

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(at)turnstep(dot)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(at)potentialtech(dot)com
> <mailto:wmoran(at)potentialtech(dot)com>>
>
> In response to Misa Simic <misa(dot)simic(at)gmail(dot)com
> <mailto:misa(dot)simic(at)gmail(dot)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