Detections
Analytics

3.2 Ensure the PostgreSQL Audit Extension (pgAudit) is enabled - pgaudit installed

Information

The PostgreSQL Audit Extension (pgAudit) provides detailed session and/or object audit logging via the standard PostgreSQL logging facility. The goal of pgAudit is to provide PostgreSQL users with the capability to produce audit logs often required to comply with government, financial, or ISO certifications.

 Rationale:

 Basic statement logging can be provided by the standard logging facility with log_statement = all. This is acceptable for monitoring and other uses but does not provide the level of detail generally required for an audit. It is not enough to have a list of all the operations performed against the database, it must also be possible to find particular statements that are of interest to an auditor. The standard logging facility shows what the user requested, while pgAudit focuses on the details of what happened while the database was satisfying the request.
 When logging SELECT and DML statements, pgAudit can be configured to log a separate entry for each relation referenced in a statement. No parsing is required to find all statements that touch a particular table. In fact, the goal is that the statement text is provided primarily for deep forensics and should not be required for an audit.

Solution

To install and enable pgAudit, simply install the appropriate rpm from the PGDG repo:
 [root@centos7 ~]# whoami
 root
 [root@centos7 ~]# yum -y install pgaudit13_11
 Loaded plugins: fastestmirror, priorities
 Loading mirror speeds from cached hostfile
 * base: centos.mirrors.tds.net
 * extras: mirror.team-cymru.com
 * updates: mirror.sesp.northwestern.edu
 base | 3.6 kB 00:00:00
 extras | 3.4 kB 00:00:00
 pgdg10 | 3.6 kB 00:00:00
 pgdg11 | 3.6 kB 00:00:00
 pgdg94 | 3.6 kB 00:00:00
 pgdg95 | 3.6 kB 00:00:00
 pgdg96 | 3.6 kB 00:00:00
 updates | 3.4 kB 00:00:00
 (1/6): pgdg11/7/x86_64/primary_db | 184 kB 00:00:00
 (2/6): pgdg95/7/x86_64/primary_db | 245 kB 00:00:00
 (3/6): pgdg10/7/x86_64/primary_db | 229 kB 00:00:01
 (4/6): pgdg96/7/x86_64/primary_db | 248 kB 00:00:01
 (5/6): pgdg94/7/x86_64/primary_db | 254 kB 00:00:01
 (6/6): updates/7/x86_64/primary_db | 5.0 MB 00:00:08
 1425 packages excluded due to repository priority protections
 Resolving Dependencies
 --> Running transaction check
 ---> Package pgaudit13_11.x86_64 0:1.3.0-1.rhel7 will be installed
 --> Finished Dependency Resolution

 Dependencies Resolved

 =============================================================================
 Package Arch Version Repository Size
 =============================================================================
 Installing:
 pgaudit13_11 x86_64 1.3.0-1.rhel7 pgdg11 45 k

 Transaction Summary
 =============================================================================
 Install 1 Package

 Total download size: 45 k
 Installed size: 85 k
 Downloading packages:
 pgaudit13_11-1.3.0-1.rhel7.x86_64.rpm | 45 kB 00:00:00
 Running transaction check
 Running transaction test
 Transaction test succeeded
 Running transaction
 Installing : pgaudit13_11-1.3.0-1.rhel7.x86_64 1/1
 Verifying : pgaudit13_11-1.3.0-1.rhel7.x86_64 1/1

 Installed:
 pgaudit13_11.x86_64 0:1.3.0-1.rhel7

 Complete!
 pgAudit is now installed and ready to be configured. Next, we need to alter the postgresql.conf configuration file to:
 * enable pgAudit as an extension in the shared_preload_libraries parameter
 * indicate which classes of statements we want to log via the pgaudit.log parameter
 and, finally, restart the PostgreSQL service:
 $ vi ${PGDATA}/postgresql.conf
 Find the shared_preload_libraries entry, and add 'pgaudit' to it (preserving any existing entries):
 shared_preload_libraries = 'pgaudit'

 OR

 shared_preload_libraries = 'pgaudit,somethingelse'
 Now, add a new pgaudit-specific entry:
 # for this example we are logging the ddl and write operations
 pgaudit.log='ddl,write'
 Restart the PostgreSQL server for changes to take affect:
 [root@centos7 ~]# whoami
 root
 [root@centos7 ~]# systemctl restart postgresql-11
 [root@centos7 ~]# systemctl status postgresql-11|grep 'ago$'
 Active: active (running) since Thu 2019-05-30 13:33:37 EDT; 10s ago
 [root@centos7 ~]#

 Impact:

 Depending on settings, it is possible for pgAudit to generate an enormous volume of logging. Be careful to determine exactly what needs to be audit logged in your environment to avoid logging too much.

See Also

https://workbench.cisecurity.org/files/2407

Item Details

Category: AUDIT AND ACCOUNTABILITY

References: 800-53|AU-3, 800-53|AU-12, CSCv6|6, CSCv7|6.2

Plugin: PostgreSQLDB

Control ID: 88f8380d1109312d332fa89f25e33af2916a24c11f780ae432977dd30f7446a8