Please add "user" filter to pgaudit (refactored branch).

[harada-toshi]

Hi.

I am trying pgaudit (refactored branch).

In the setting of pgaudit (refactored), there is no user filtering function, but we think that it is better to add it assuming the following case.

For example, it is considered that there is a case where only the operation of the privileged user (eg "postgres") is audited and the application user's operation is not audited.

In pgaudit (advanced branch), filtering by "user" was possible.
Even "refactored branch", please implement filtering of "user".

[MasahikoSawada]

Commit 3c7b2ec should satisfies it. Please try it.

[harada-toshi]

Thank you very much.
We were able to confirm the output control of the audit record by the role name using "commit ID = 3c7b2ec".

However, with this commit ID, the role name does not seem to be output in the audit log.
Is output of role name a future task?

pgaudit config.

[option]
log_level = 'NOTICE'
log_catalog = on
log_for_test = on

[rule]
        audit_role = 'postgres'

Output audit log

$ psql foo_db -U foo
psql (9.6.2)
Type "help" for help.

foo_db=> SELECT 1;
 ?column?
----------
        1
(1 row)

foo_db=> SELECT * FROM test;
 id | data
----+------
(0 rows)

foo_db=> \q
[nuko@localhost pgaudit]$ psql foo_db -U postgre
psql: FATAL:  role "postgre" does not exist
[nuko@localhost pgaudit]$ psql foo_db -U postgres
psql (9.6.2)
Type "help" for help.

foo_db=# SELECT 1;
NOTICE:  AUDIT: SESSION,1,1,READ,,foo_db,[local],psql,,,,,SELECT,,,SELECT 1;,<not logged>
 ?column?
----------
        1
(1 row)

foo_db=# SELECT * FROM test;
NOTICE:  AUDIT: SESSION,2,1,READ,,foo_db,[local],psql,,,,,SELECT,TABLE,public.test,SELECT * FROM test;,<not logged>
 id | data
----+------
(0 rows)

[MasahikoSawada]

However, with this commit ID, the role name does not seem to be output in the audit log. Is output of role name a future task?

Since the user name used for regression test depends on testing environment, I made the user name not to be logged when log_for_test is set. You can confirm it without log_for_test parameter.

BTW, After though I suspect whether current implementation satisfies our original purpose. In current implementation I use MyProcPort->user_name as a user name being used for audit_role filtering, which is the same as what advanced branches doing. It means that pgaudit filters audit log by the user name which is used when authentication. So it can not handle the case where the user uses SET ROLE and SET [SESSION|LOCAL] AUTHORIZATION. Is it expected behavior?

[harada-toshi]

Since the user name used for regression test depends on testing environment, I made the user name not to be logged when log_for_test is set. You can confirm it without log_for_test parameter.

All right.
I excluded 'log_fo_test' and confirmed that the user name was outputted.

As for the question of the second half, I will reply with another comment.

[canlar]

Hi,

i am testing pgaudit from ntt. If i add a role/user to audit_role parameter in the pgaudit.conf file, so i must restart the postgres DB to audit this user.
Reload of config Parameter file is not working.
Next problem is I will only log users if user member of a role (maybe admin or superuser role).

Do you have an idea what can i do to audit user when i add new user in the pgaudit,conf without restart of postgres database ?