From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Little, Douglas" <DOUGLAS(dot)LITTLE(at)orbitz(dot)com> |
Cc: | "PostgreSQL General (pgsql-general(at)postgresql(dot)org)" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: log select access |
Date: | 2012-07-26 21:59:20 |
Message-ID: | CAHyXU0zYDC30TP0FQaHzXAcR9+MXfKCDGXr2qUbr9mNcpZwYfg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jul 26, 2012 at 4:32 PM, Little, Douglas
<DOUGLAS(dot)LITTLE(at)orbitz(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | hartrc | 2012-07-26 22:07:52 | postgres maintenance db |
Previous Message | Little, Douglas | 2012-07-26 21:32:35 | log select access |