Re: pg_audit_users - Auditing user activity

On 12/20/2014 06:40 AM, Pierre Ducroquet wrote:
> Hi
>
> I'm working on a web credit card payment solution, so in a PCI-DSS environment,
> and the auditors gave me trouble with one specific audit point for the
> PostgreSQL database. They require the list of users in the database that had no
> activity in the past 90 days to be deleted.
> So far, it seems the only solution to implement that in PostgreSQL would be to
> parse the log, hoping not to lose any line.
> That seems too risky for me, so I wrote my own solution for this issue, and I
> would like to submit it here for review/suggestion and to help other users
> facing the same needs. Since it's very small, I've taken the liberty of
> attaching it to this email.
> The code is more or less «inspired» by pg_stat_statements. So far I've not
> implemented saving upon restarts of the database, I'll probably do it in the
> next days/weeks. It has been tested against PostgreSQL 9.2 only, but I'll test
> 9.3 and 9.4 soon.

Would it not be easier to just put a timestamp field in the user table
and touch it every time they used their card. Then just delete everyone
with a timestamp > 90 days.

>
> Thanks
>
> Pierre
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com