Re: pgaudit - an auditing extension for PostgreSQL

Hello,

As I said in the previous mail, I looked into the latest PCI DSS 3.0 to find 
out whether and how pgaudit fulfills the requirements related to auditing. 
I believe that even the initial version of pgaudit needs to have enough 
functionalities to meet the requirements of some well-known standard, in 
order to demonstrate that PostgreSQL provides a really useful auditing 
feature.  I chose PCI DSS because it seems popular worldwide.

Most requirement items are met, but some are not or I'm not sure if and/or 
how.  I only listed items which I'd like to ask for opinions.  I'm sorry I 
can't have good suggestions, but I hope this report will lead to good 
discussion and better auditing feature we can boast of.  Of course, I'll 
suggest any idea when I think of something.


[requirement]
3.4 Render PAN unreadable anywhere it
is stored (including on portable digital
media, backup media, and in logs) by
using any of the following approaches:
...
3.4.d Examine a sample of audit logs to confirm that the PAN is
rendered unreadable or removed from the logs.

[my comment]
Do the audit log entries always hide the actual bind parameter values (with 
$1, $2, etc.) if the application uses parameterized SQL statements?  Should 
we advise users to use parameterized statements in the pgaudit 
documentation? (I think so)



[requirement]
10.2.2 Verify all actions taken by any individual with root or
administrative privileges are logged.
10.2.6 Verify the following are logged:
. Initialization of audit logs
. Stopping or pausing of audit logs.

[my comment]
The database superuser can hide his activity by "SET pgaudit.log = '';", but 
this SET is audit-logged.  Therefore, I think these requirements is met 
because the fact that the superuser's suspicious behavior (hiding activity) 
is recorded.  Do you think this interpretation is valid?



[requirement]
10.2.3 Verify access to all audit trails is logged.

Malicious users often attempt to alter audit logs to
hide their actions, and a record of access allows
an organization to trace any inconsistencies or
potential tampering of the logs to an individual
account. Having access to logs identifying
changes, additions, and deletions can help retrace
steps made by unauthorized personnel.

[my comment]
I'm totally unsure how this can be fulfilled.



[requirement]
10.2.4 Verify invalid logical access attempts are logged.

Malicious individuals will often perform multiple
access attempts on targeted systems. Multiple
invalid login attempts may be an indication of an
unauthorized user’s attempts to “brute force” or
guess a password.

[my comment]
Login attempts also need to be audit-logged to meet this requirement.



[requirement]
10.2.5.a Verify use of identification and authentication
mechanisms is logged.

Without knowing who was logged on at the time of
an incident, it is impossible to identify the
accounts that may have been used. Additionally,
malicious users may attempt to manipulate the
authentication controls with the intent of
bypassing them or impersonating a valid account.

[my comment]
Can we consider this is met because pgaudit records the session user name?



[requirement]
10.3 Record at least the following audit
trail entries for all system components for
each event:
10.3.4 Verify success or failure indication is included in log
entries.
10.3.5 Verify origination of event is included in log entries.

[my comment]
This doesn't seem to be fulfilled because the failure of SQL statements and 
the client address are not part of the audit log entry.



[requirement]
10.5 Secure audit trails so they cannot
be altered.
10.5 Interview system administrators and examine system
configurations and permissions to verify that audit trails are
secured so that they cannot be altered as follows:
10.5.1 Only individuals who have a job-related need can view
audit trail files.
Adequate protection of the audit logs includes
strong access control (limit access to logs based
on “need to know” only), and use of physical or
network segregation to make the logs harder to
find and modify.
Promptly backing up the logs to a centralized log
server or media that is difficult to alter keeps the
logs protected even if the system generating the
logs becomes compromised.
10.5.2 Protect audit trail files from
unauthorized modifications.

[my comment]
I don't know how to achieve these, because the DBA (who starts/stops the 
server) can modify and delete server log files without any record.



[requirement]
10.6 Review logs and security events for
all system components to identify
anomalies or suspicious activity.
Note: Log harvesting, parsing, and
alerting tools may be used to meet this
Requirement.
The log review process does not have to be
manual. The use of log harvesting, parsing, and
alerting tools can help facilitate the process by
identifying log events that need to be reviewed.

[my comment]
What commercial and open source products are well known as the "log 
harvesting, parsing, and alerting tool"?  Is it possible and reasonably easy 
to integrate pgaudit with those tools?  The purpose of audit logging feature 
is not recording facts, but to enable timely detection of malicious actions. 
So, I think the ease of integration with those tools must be evaluated.  But 
I don't know about such tools.

I feel the current output format of pgaudit is somewhat difficult to treat:

* The audit log entries are mixed with other logs in the server log files, 
so the user has to extract the audit log lines from the server log files and 
save them elsewhere.  I think it is necessary to store audit logs in 
separate files.

* Does the command text need "" around it in case it contains commas?


Regards
MauMau