Обсуждение: (pgaudit) Audit log is not output after the SET ROLE.

Hi.

I am testing the pgaudit(https://commitfest.postgresql.org/9/463/).
(use "http://www.postgresql.org/message-id/56B0101B.6070704@pgmasters.net" attached patch on 9.6-devel)


I found strange thing.

- After SET ROLE, part of the SQL is not the audit log output.
- SQL comprising a relation is not output to the audit log.

* Reproduce:
** prepare

createuser test_user -U postgres
createdb test -U postgres -O test_user
psql test -U test_user -c "CREATE TABLE team(id int, name text)"

** pgaudit settings

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'all'

** test sql script (test.sql)

SELECT 1;
SELECT * FROM team; -- output audit log
SET ROLE test_user;
SELECT 2;
SELECT * FROM team; -- no output audit log
SELECT 3;
RESET ROLE;
SELECT * FROM team; -- output audit log

** run script

psql test -U postgres -f test.sql

** audit log

LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,SELECT 1;,<not logged>
LOG:  AUDIT: SESSION,2,1,READ,SELECT,,,SELECT * FROM team;,<not logged>
LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET ROLE test_user;,<not logged>
LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,SELECT 2;,<not logged>
LOG:  AUDIT: SESSION,5,1,READ,SELECT,,,SELECT 3;,<not logged>
LOG:  AUDIT: SESSION,6,1,MISC,RESET,,,RESET ROLE;,<not logged>
LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,SELECT * FROM team;,<not logged>



Regards,
Harada Toshi.
NTT Softeare Corporation

On 3/7/16 4:39 AM, Toshi Harada wrote:
>
> I am testing the pgaudit(https://commitfest.postgresql.org/9/463/).
> (use "http://www.postgresql.org/message-id/56B0101B.6070704@pgmasters.net" attached patch on 9.6-devel)
>
> I found strange thing.
>
> - After SET ROLE, part of the SQL is not the audit log output.
> - SQL comprising a relation is not output to the audit log.
>
> * Reproduce:
> ** prepare
>
> createuser test_user -U postgres
> createdb test -U postgres -O test_user
> psql test -U test_user -c "CREATE TABLE team(id int, name text)"
>
> ** pgaudit settings
>
> shared_preload_libraries = 'pgaudit'
> pgaudit.log = 'all'

Both of these are in postgresql.conf?

> ** test sql script (test.sql)
>
> SELECT 1;
> SELECT * FROM team; -- output audit log
> SET ROLE test_user;
> SELECT 2;
> SELECT * FROM team; -- no output audit log
> SELECT 3;
> RESET ROLE;
> SELECT * FROM team; -- output audit log
>
> ** run script
>
> psql test -U postgres -f test.sql
>
> ** audit log
>
> LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,SELECT 1;,<not logged>
> LOG:  AUDIT: SESSION,2,1,READ,SELECT,,,SELECT * FROM team;,<not logged>
> LOG:  AUDIT: SESSION,3,1,MISC,SET,,,SET ROLE test_user;,<not logged>
> LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,SELECT 2;,<not logged>
> LOG:  AUDIT: SESSION,5,1,READ,SELECT,,,SELECT 3;,<not logged>
> LOG:  AUDIT: SESSION,6,1,MISC,RESET,,,RESET ROLE;,<not logged>
> LOG:  AUDIT: SESSION,7,1,READ,SELECT,,,SELECT * FROM team;,<not logged>

Well, that definitely doesn't look right.

You may have noticed that the pgaudit patch is marked as "returned with
feedback" so it is closed for the current commitfest and will not be
included in 9.6.

I'll definitely look at this bug but I would ask that you resubmit it at
https://github.com/pgaudit/pgaudit/issues so we can continue the
conversation there.

Thanks!
--
-David
david@pgmasters.net