Re: pgaudit - an auditing extension for PostgreSQL - Mailing list pgsql-hackers

From MauMau
Subject Re: pgaudit - an auditing extension for PostgreSQL
Date
Msg-id 61B2CC56456D4C91817619D13F5E5EA8@maumau
Whole thread Raw
In response to Re: pgaudit - an auditing extension for PostgreSQL  (Abhijit Menon-Sen <ams@2ndQuadrant.com>)
Responses Re: pgaudit - an auditing extension for PostgreSQL
List pgsql-hackers
Hello,

I had a quick look through the code and did some testing.  Let me give you 
some comments.  I will proceed with checking if pgaudit can meet PCI DSS 
requirements.

By the way, I'd like to use pgaudit with 9.2.  Is it possible with a slight 
modification of the code?  If it is, what features of pgaudit would be 
unavailable?  Could you support 9.2?


(1)
The build failed with PostgreSQL 9.5, although I know the README mentions 
that pgaudit supports 9.3 and 9.4.  The cause is T_AlterTableSpaceMoveStmt 
macro is not present in 9.5.  I could build and use pgaudit by removing two 
lines referring to that macro.  I tested pgaudit only with 9.5.


(2)
I could confirm that DECLARE is audit-logged as SELECT and FETCH/CLOSE are 
not.  This is just as expected.  Nice.


(3)
SELECT against a view generated two audit log lines, one for the view 
itself, and the other for the underlying table.  Is this intended?  I'm not 
saying that's wrong but just asking.


(4)
I'm afraid audit-logging DML statements on temporary tables will annoy 
users, because temporary tables are not interesting.  In addition, in 
applications which use the same temporary table in multiple types of 
transactions as follows, audit log entries for the DDL statement are also 
annoying.

BEGIN;
CREATE TEMPORARY TABLE mytemp ... ON COMMIT DROP;
DML;
COMMIT;

The workaround is "CREATE TEMPORARY TABLE mytemp IF NOT EXIST ... ON COMMIT 
DELETE ROWS".  However, users probably don't (or can't) modify applications 
just for audit logging.


(5)
This is related to (4).  As somebody mentioned, I think the ability to 
select target objects of audit logging is definitely necessary.  Without 
that, huge amount of audit logs would be generated for uninteresting 
objects.  That would also impact performance.


(6)
What's the performance impact of audit logging?  I bet many users will ask 
"about what percentage would the throughtput decrease by?"  I'd like to know 
the concrete example, say, pgbench and DBT-2.


(7)
In README, COPY FROM/TO should be added to read and write respectively.


(8)
The code looks good.  However, I'm worried about the maintenance.  How can 
developers notice that pgaudit.c needs modification when they add a new SQL 
statement?  What keyword can they use to grep the source code to find 
pgaudit.c?


Regards
MauMau




pgsql-hackers by date:

Previous
From: Stephen Frost
Date:
Subject: Re: Additional role attributes && superuser review
Next
From: Simon Riggs
Date:
Subject: Re: pgaudit - an auditing extension for PostgreSQL