PostgreSQL® Auditing Extension on Debian/Ubuntu

Categories:	PostgreSQL®
Tags:	pgAudit PostgreSQL®

The pgAudit extension allows for fine-grained auditing of access to a PostgreSQL^® database. Among other things, it is an important component of the recently published PostgreSQL^® STIG (“Security Technical Implementation Guide”) by the US Department of Defense. Debian packages created by us as part of the pkg-postgresql project are now available in the official PostgreSQL^® APT repository, as well as the developer branches of Debian and Ubuntu.

While PostgreSQL^® offers configurable logging by itself, the audit messages generated by pgAudit go significantly further and also cover most compliance guidelines. They are also written to the PostgreSQL^® log, but in a uniform format and, in contrast to conventional logging via e.g. log_statement, they are deterministic and comprehensive on the one hand, and targeted on the other. For example, it was previously possible to log executed SELECT commands to detect unwanted access to a specific table, but since this then applies to all SELECT commands, it is not manageable in practice. With pgAudit’s so-called object audit logging, it is possible to write only access to specific tables to the audit log by assigning appropriate permissions to an auditor role, e.g.:

Prepare Database

CREATE ROLE AUDITOR;
SET pgaudit.role = 'auditor';
CREATE TABLE account
(
 id INT,
 name TEXT,
 password TEXT,
 description TEXT
);
GRANT SELECT (password) ON public.account TO auditor;

Abfragen, die die Spalte password betreffen (und nur solche) erscheinen nun im Audit-Log:

SELECT id, name FROM account;
SELECT password FROM account;
AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,SELECT password FROM account

Log content

The first field is either SESSION or OBJECT for the corresponding audit logging type. The two subsequent fields are statement IDs, the fourth field is the query class (READ, WRITE, ROLE, DDL, etc.), followed by the command type and (if applicable) the object type and name; the last field is finally the command actually executed. Crucial for auditing is that the commands actually executed are logged, so that circumvention through deliberate obfuscation is not possible. An example of this from the pgAudit documentation is:

AUDIT: SESSION,1,1,FUNCTION,DO,,,"DO $$
BEGIN
 EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,1,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

As can be seen from the command used (lines 1-4), an attempt is made here to prevent the table name important_table from appearing in the log file, but pgAudit reliably logs the table name (field 7) as well as the actually executed CREATE TABLE statement. However, in the case of the conventional PostgreSQL^® log, this attempt is successful; only the entered command is logged here:

LOG: statement: DO $$
BEGIN
  EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

The pgAudit extension can, in principle, be used from PostgreSQL^® version 9.5 onwards. While the version 1.1 of pgAudit packaged by us officially only supports 9.6, the created Debian package can also be used with 9.5 thanks to an additional patch. Therefore, packages for both 9.6 and 9.5 are available on apt.postgresql.org for all supported Debian and Ubuntu versions.

PostgreSQL^® Auditing Extension on Debian/Ubuntu

Prepare Database

Log content

See also:

Michael Banck