Re: pgaudit - an auditing extension for PostgreSQL

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