Thread: pg_audit_users - Auditing user activity

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.

Thanks

 Pierre

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.klaver@aklaver.com

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.

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.klaver@aklaver.com