Re: pg_audit_users - Auditing user activity

On 12/20/2014 07:22 AM, Pierre Ducroquet wrote:
> 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.

So timestamp log ins. I might come up with another term than 'useless
users':)
>
>

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