PostgreSQL must produce audit records containing sufficient information to establish the outcome (success or failure) of the events.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-72843	PGS9-00-000200	SV-87495r3_rule		Medium

Description
Information system auditing capability is critical for accurate forensic analysis. Without information about the outcome of events, security personnel cannot make an accurate assessment as to whether an attack was successful or if changes were made to the security state of the system. Event outcomes can include indicators of event success or failure and event-specific results (e.g., the security state of the information system after the event occurred). As such, they also provide a means to measure the impact of an event and help authorized personnel to determine the appropriate response.

Description

Information system auditing capability is critical for accurate forensic analysis. Without information about the outcome of events, security personnel cannot make an accurate assessment as to whether an attack was successful or if changes were made to the security state of the system. Event outcomes can include indicators of event success or failure and event-specific results (e.g., the security state of the information system after the event occurred). As such, they also provide a means to measure the impact of an event and help authorized personnel to determine the appropriate response.

STIG	Date
PostgreSQL 9.x Security Technical Implementation Guide	2019-09-26

Details

Check Text ( C-72977r3_chk )
Note: The following instructions use the PGDATA environment variable. See supplementary content APPENDIX-F for instructions on configuring PGDATA. As a database administrator (shown here as "postgres"), create a table, insert a value, alter the table and update the table by running the following SQL: CREATE TABLE stig_test(id INT); INSERT INTO stig_test(id) VALUES (0); ALTER TABLE stig_test ADD COLUMN name text; UPDATE stig_test SET id = 1 WHERE id = 0; Next, as a user without access to the stig_test table, run the following SQL: INSERT INTO stig_test(id) VALUES (1); ALTER TABLE stig_test DROP COLUMN name; UPDATE stig_test SET id = 0 WHERE id = 1; The prior SQL should generate errors: ERROR: permission denied for relation stig_test ERROR: must be owner of relation stig_test ERROR: permission denied for relation stig_test Now, as the database administrator, drop the test table by running the following SQL: DROP TABLE stig_test; Now verify the errors were logged: $ sudo su - postgres $ cat ${PGDATA?}/pg_log/$PGDATA/ < 2016-02-23 14:51:31.103 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >LOG: AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE stig_test(id INT);, < 2016-02-23 14:51:44.835 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >LOG: AUDIT: SESSION,2,1,WRITE,INSERT,,,INSERT INTO stig_test(id) VALUES (0);, < 2016-02-23 14:53:25.805 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >LOG: AUDIT: SESSION,3,1,DDL,ALTER TABLE,,,ALTER TABLE stig_test ADD COLUMN name text;, < 2016-02-23 14:53:54.381 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >LOG: AUDIT: SESSION,4,1,WRITE,UPDATE,,,UPDATE stig_test SET id = 1 WHERE id = 0;, < 2016-02-23 14:54:20.832 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >ERROR: permission denied for relation stig_test < 2016-02-23 14:54:20.832 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >STATEMENT: INSERT INTO stig_test(id) VALUES (1); < 2016-02-23 14:54:41.032 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >ERROR: must be owner of relation stig_test < 2016-02-23 14:54:41.032 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >STATEMENT: ALTER TABLE stig_test DROP COLUMN name; < 2016-02-23 14:54:54.378 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >ERROR: permission denied for relation stig_test < 2016-02-23 14:54:54.378 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >STATEMENT: UPDATE stig_test SET id = 0 WHERE id = 1; < 2016-02-23 14:55:23.723 EDT psql postgres postgres 570bf307.3b0a 2016-04-11 14:55:03 EDT [local] >LOG: AUDIT: SESSION,1,1,DDL,DROP TABLE,,,DROP TABLE stig_test;, If audit records exist without the outcome of the event that occurred, this is a finding.

Check Text ( C-72977r3_chk )

Note: The following instructions use the PGDATA environment variable. See supplementary content APPENDIX-F for instructions on configuring PGDATA.

As a database administrator (shown here as "postgres"), create a table, insert a value, alter the table and update the table by running the following SQL:

CREATE TABLE stig_test(id INT);
INSERT INTO stig_test(id) VALUES (0);
ALTER TABLE stig_test ADD COLUMN name text;
UPDATE stig_test SET id = 1 WHERE id = 0;

Next, as a user without access to the stig_test table, run the following SQL:

INSERT INTO stig_test(id) VALUES (1);
ALTER TABLE stig_test DROP COLUMN name;
UPDATE stig_test SET id = 0 WHERE id = 1;

The prior SQL should generate errors:

ERROR: permission denied for relation stig_test
ERROR: must be owner of relation stig_test
ERROR: permission denied for relation stig_test

Now, as the database administrator, drop the test table by running the following SQL:

DROP TABLE stig_test;

Now verify the errors were logged:

$ sudo su - postgres
$ cat ${PGDATA?}/pg_log/$PGDATA/
< 2016-02-23 14:51:31.103 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >LOG: AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE stig_test(id INT);,
< 2016-02-23 14:51:44.835 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >LOG: AUDIT: SESSION,2,1,WRITE,INSERT,,,INSERT INTO stig_test(id) VALUES (0);,
< 2016-02-23 14:53:25.805 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >LOG: AUDIT: SESSION,3,1,DDL,ALTER TABLE,,,ALTER TABLE stig_test ADD COLUMN name text;,
< 2016-02-23 14:53:54.381 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >LOG: AUDIT: SESSION,4,1,WRITE,UPDATE,,,UPDATE stig_test SET id = 1 WHERE id = 0;,
< 2016-02-23 14:54:20.832 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >ERROR: permission denied for relation stig_test
< 2016-02-23 14:54:20.832 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >STATEMENT: INSERT INTO stig_test(id) VALUES (1);
< 2016-02-23 14:54:41.032 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >ERROR: must be owner of relation stig_test
< 2016-02-23 14:54:41.032 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >STATEMENT: ALTER TABLE stig_test DROP COLUMN name;
< 2016-02-23 14:54:54.378 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >ERROR: permission denied for relation stig_test
< 2016-02-23 14:54:54.378 EDT psql postgres postgres 570bf22a.3af2 2016-04-11 14:51:22 EDT [local] >STATEMENT: UPDATE stig_test SET id = 0 WHERE id = 1;
< 2016-02-23 14:55:23.723 EDT psql postgres postgres 570bf307.3b0a 2016-04-11 14:55:03 EDT [local] >LOG: AUDIT: SESSION,1,1,DDL,DROP TABLE,,,DROP TABLE stig_test;,

If audit records exist without the outcome of the event that occurred, this is a finding.

Fix Text (F-79285r5_fix)
Using pgaudit PostgreSQL can be configured to audit various facets of PostgreSQL. See supplementary content APPENDIX-B for documentation on installing pgaudit. All errors, denials and unsuccessful requests are logged if logging is enabled. See supplementary content APPENDIX-C for documentation on enabling logging. 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. With pgaudit and logging enabled, set the following configuration settings in postgresql.conf, as the database administrator (shown here as "postgres"), to the following: $ sudo su - postgres $ vi ${PGDATA?}/postgresql.conf pgaudit.log_catalog='on' pgaudit.log_level='log' pgaudit.log_parameter='on' pgaudit.log_statement_once='off' pgaudit.log='all, -misc' Next, tune the following logging configurations in postgresql.conf: $ sudo su - postgres $ vi ${PGDATA?}/postgresql.conf log_line_prefix = '< %m %u %d %e: >' log_error_verbosity = default Last, as the system administrator, restart PostgreSQL: # SYSTEMD SERVER ONLY $ sudo systemctl reload postgresql-${PGVER?} # INITD SERVER ONLY $ sudo service postgresql-${PGVER?} reload

Fix Text (F-79285r5_fix)

Using pgaudit PostgreSQL can be configured to audit various facets of PostgreSQL. See supplementary content APPENDIX-B for documentation on installing pgaudit.

All errors, denials and unsuccessful requests are logged if logging is enabled. See supplementary content APPENDIX-C for documentation on enabling logging.

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.

With pgaudit and logging enabled, set the following configuration settings in postgresql.conf, as the database administrator (shown here as "postgres"), to the following:

$ sudo su - postgres
$ vi ${PGDATA?}/postgresql.conf
pgaudit.log_catalog='on'
pgaudit.log_level='log'
pgaudit.log_parameter='on'
pgaudit.log_statement_once='off'
pgaudit.log='all, -misc'

Next, tune the following logging configurations in postgresql.conf:

$ sudo su - postgres
$ vi ${PGDATA?}/postgresql.conf
log_line_prefix = '< %m %u %d %e: >'
log_error_verbosity = default

Last, as the system administrator, restart PostgreSQL:

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

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