STIGQter STIGQter: STIG Summary: PostgreSQL 9.x Security Technical Implementation Guide Version: 2 Release: 1 Benchmark Date: 23 Oct 2020:

PostgreSQL must provide the means for individuals in authorized roles to change the auditing to be performed on all application components, based on all selectable event criteria within organization-defined time thresholds.

DISA Rule

SV-214134r508027_rule

Vulnerability Number

V-214134

Group Title

SRG-APP-000353-DB-000324

Rule Version

PGS9-00-010000

Severity

CAT II

CCI(s)

Weight

10

Fix Recommendation

Note: The following instructions use the PGDATA and PGVER environment variables. See supplementary content APPENDIX-F for instructions on configuring PGDATA and APPENDIX-H for PGVER.

To ensure that logging is enabled, review supplementary content APPENDIX-C for instructions on enabling logging. 

For audit logging we suggest using pgaudit. For instructions on how to setup pgaudit, see supplementary content APPENDIX-B. 

As a superuser (postgres), any pgaudit parameter can be changed in postgresql.conf. Configurations can only be changed by a superuser. 

### Example: Change Auditing To Log Any ROLE Statements 

Note: This will override any setting already configured. 

Alter the configuration to do role-based logging: 

$ sudo su - postgres 
$ vi ${PGDATA?}/postgresql.conf 

Add the following parameters (or edit existing parameters): 

pgaudit.log = 'role' 

Now, as the system administrator, reload the server with the new configuration: 

# SYSTEMD SERVER ONLY 
$ sudo systemctl reload postgresql-${PGVER?}

# INITD SERVER ONLY 
$ sudo service postgresql-${PGVER?} reload 

### Example: Set An Auditing Role And Grant Privileges 

An audit role can be configured and granted privileges to specific tables and columns that need logging. 

##### Create Test Table 

$ sudo su - postgres 
$ psql -c "CREATE TABLE public.stig_audit_example(id INT, name TEXT, password TEXT);" 

##### Define Auditing Role 

As PostgreSQL superuser (such as postgres), add the following to postgresql.conf or any included configuration files. 

$ sudo su - postgres 
$ vi ${PGDATA?}/postgresql.conf 

Add the following parameters (or edit existing parameters): 

pgaudit.role = 'auditor' 

Now, as the system administrator, reload the server with the new configuration: 

# SYSTEMD SERVER ONLY 
$ sudo systemctl reload postgresql-${PGVER?}

# INITD SERVER ONLY 
$ sudo service postgresql-${PGVER?} reload 

Next in PostgreSQL create a new role: 

postgres=# CREATE ROLE auditor; 
postgres=# GRANT select(password) ON public.stig_audit_example TO auditor; 

Note: This role is created with NOLOGIN privileges by default. 

Now any SELECT on the column password will be logged: 

$ sudo su - postgres 
$ psql -c "SELECT password FROM public.stig_audit_example;" 
$ cat ${PGDATA?}/pg_log/<latest_log> 
< 2016-01-28 16:46:09.038 UTC bob postgres: >LOG: AUDIT: OBJECT,6,1,READ,SELECT,TABLE,public.stig_audit_example,SELECT password FROM stig_audit_example;,<none> 

## Change Configurations During A Specific Timeframe 

Deploy PostgreSQL that allows audit configuration changes to take effect within the timeframe required by the application owner and without involving actions or events that the application owner rules unacceptable. 

Crontab can be used to do this. 

For a specific audit role: 

# Grant specific audit privileges to an auditing role at 5 PM every day of the week, month, year at the 0 minute mark. 
0 5 * * * postgres /usr/bin/psql -c "GRANT select(password) ON public.stig_audit_example TO auditor;" 
# Revoke specific audit privileges to an auditing role at 5 PM every day of the week, month, year at the 0 minute mark. 
0 17 * * * postgres /usr/bin/psql -c "REVOKE select(password) ON public.stig_audit_example FROM auditor;"

Check Contents

First, as the database administrator, check if pgaudit is present in shared_preload_libraries:

$ sudo su - postgres
$ psql -c "SHOW shared_preload_libraries"

If pgaudit is not present in the result from the query, this is a finding.

Vulnerability Number

V-214134

Documentable

False

Rule Version

PGS9-00-010000

Severity Override Guidance

First, as the database administrator, check if pgaudit is present in shared_preload_libraries:

$ sudo su - postgres
$ psql -c "SHOW shared_preload_libraries"

If pgaudit is not present in the result from the query, this is a finding.

Check Content Reference

M

Target Key

3994

Comments