Re: Logging access to data in database table

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