Re: pg_audit_users - Auditing user activity

On Saturday, December 20, 2014 06:57:54 AM Adrian Klaver wrote:
> 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.

I need to know when database users log in and do just anything on the database,
even a single select. The need is to delete useless users from the database.