Re: [COMMITTERS] pgsql: Add pg_audit, an auditing extension

On Sun, May 17, 2015 at 11:00 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Fujii, Michael,
>
> * Fujii Masao (masao.fujii@gmail.com) wrote:
>> pg_audit uses 1.0.0 as its version number. But, is the third digit really
>> required? Why? We usually uses the version number with two digits in
>> contrib modules.
>
> [...]
>
>> In Makefile, PGFILEDESC should be added.
>>
>> +# pg_audit/Makefile
>>
>> should be "# contrib/pg_audit/Makefile" for the consistency.
>>
>> The categories of some SQL commands are different between log_statement and
>> pg_audit. For example, REINDEX is treated as DDL in pg_audit, but not in
>> log_statement. That's confusing. We should use the same "category-mapping"
>> rule as much as possible.
>
> [...]
>
> * Michael Paquier (michael.paquier@gmail.com) wrote:
>> And on top of that the following things should be changed:
>> - Removal of REGRESS_OPTS which is empty
>> - Removal of MODULE, which overlaps with MODULE_big
>> - $(WIN32RES) needs to be added to OBJS for Windows versioning
>
> I've pushed these changes.

Thanks a lot!

Here are other random comments on pg_audit.
# Move to pgsql-hackers

Isn't it better to suppress STATEMENT message in audit log?
Otherwise, we always get two duplicate statements as follows, and which would
increases the audit log volume very much, I'm afraid.
   LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,SELECT 1;,<not logged>   STATEMENT:  SELECT 1;

Also CONTEXT message containing the same statement can sometimes be logged
at the same time.

When I tried the object audit feature, I got the following log messages.
The class and command type of this SELECT query is WRITE and UPDATE in the log
message. Is this intentional? Why? Maybe pg_audit treats something like
"SELECT FOR SHARE/UPDATE" as UPDATE command? But in session logging,
the class and command type of this query was READ and SELECT. So confusing.
   LOG:  AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.aaa,"SELECT 1
FROM ONLY ""public"".""aaa"" x WHERE ""id"" OPERATOR(pg_catalog.=) $1
FOR KEY SHARE OF x",<not logged>   CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."aaa" x WHERE
"id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"   STATEMENT:  INSERT INTO bbb VALUES (1);

The queries to reproduce the above message is here.
   ALTER SYSTEM SET pg_audit.role TO 'foo';   SELECT pg_reload_conf();   CREATE TABLE aaa (id int primary key);
CREATETABLE bbb (id int references aaa(id));   INSERT INTO aaa VALUES(generate_series(1,100));   GRANT SELECT ON aaa TO
foo;  INSERT INTO bbb VALUES (1);
 

Now the class of FETCH command is MISC, but isn't it better to classify that as
READ?

When a query contains a double quote or comma, it's quoted in the audit log
as follows. Doesn't this make the analysis of the queries in the log files a bit
difficult because other queries are not quoted?
   LOG:  AUDIT: SESSION,2,1,READ,SELECT,,,"SELECT '""';",<not logged>   STATEMENT:  SELECT '"';

When log_destination is csvlog, the above quoted query is quoted again.
   2015-05-18 14:44:38.313
JST,"postgres","postgres",17725,"[local]",55597c45.453d,1,"SELECT",2015-05-18
14:44:37 JST,2/2,0,LOG,00000,"AUDIT:
SESSION,1,1,READ,SELECT,,,""SELECT '""""';"",<not
logged>",,,,,,"SELECT '""';",,,"psql"

Regards,

-- 
Fujii Masao