Thread: log select access

On Thu, Jul 26, 2012 at 4:32 PM, Little, Douglas
<DOUGLAS.LITTLE@orbitz.com> wrote:
>
> Hello everybody,
>
>
>
> For PCI compliance I need to log user access to my PCI columns in a table
> and retain for 2 years.
>
> I know I can grep the log,  but with 1m log rows/day and infrequent PCI
> access,  I’m thinking this isn’t the most efficient method.
>
>
>
> I’ve been thinking about a SELECT rule, for the access views defined on
> the tables with PCI columns.
>
> I reviewed the doc but belive I’m constrained
>
> I would like to create a select rule that would log the statement in
> addition to executing the select.
>
>
>
> “Presently, ON SELECT rules must be unconditional INSTEAD rules and must
> have actions that consist of a
>
> single SELECT command. Thus, an ON SELECT rule effectively turns the table
> into a view, whose visible”
>
>
>
> makes me think I can’t do this.   Any advice how I might accomplish the
> goal.
>
>
>
> format
>
> CREATE [ OR REPLACE ] RULE name AS ON event
>
> TO table [ WHERE condition ]
>
> DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
>
>
>
> Create rule pci_select as on select to creditcard do
>
> Instead (begin
>
> Insert into pci_log( sql statement);
>
> Select * from creditcard;
>
> end)


Well for starters you don't have access to the sql statement: the only
way to do that is through server side logging or capture inside the
application.  You can be tricky with views (which is a select rule)
and try something like this:

create table log(log text);

create or replace function log() returns bool as $$
  insert into log values (clock_timestamp() || ': ' || current_user);
select false
$$ language sql;

create view log_foo as select * from foo union all select * from foo
where (select log());

this crude way of making a 'statement level trigger' for a table.  of
course, this only logs the database role which isn't much help if
you're using application managed roles -- but this is solvable as long
as you can get it the logged in user somehow -- perhaps through a
session table.

merlin