V-72921

control "V-72921" do
  title "PostgreSQL must generate audit records when unsuccessful attempts to
  access security objects occur."
  desc  "Changes to the security configuration must be tracked.
  This requirement applies to situations where security data is retrieved or
  modified via data manipulation operations, as opposed to via specialized
  security functionality.
  In an SQL environment, types of access include, but are not necessarily
  limited to:
  SELECT
  INSERT
  UPDATE
  DELETE
  EXECUTE
  To aid in diagnosis, it is necessary to keep track of failed attempts in
  addition to the successful ones."
  impact 0.5
  tag "severity": "medium"
  tag "gtitle": "SRG-APP-000492-DB-000333"
  tag "gid": "V-72921"
  tag "rid": "SV-87573r1_rule"
  tag "stig_id": "PGS9-00-004500"
  tag "cci": "CCI-000172"
  tag "nist": ["AU-12 c", "Rev_4"]
  tag "check": "Note: The following instructions use the PGDATA environment
  variable. See supplementary content APPENDIX-F for instructions on configuring
  PGDATA.
  First, as the database administrator (shown here as \"postgres\"), setup a
  test schema and revoke users privileges from using it by running the following
  SQL:
  $ sudo su - postgres
  $ psql -c \"CREATE SCHEMA stig_test_schema AUTHORIZATION postgres\"
  $ psql -c \"REVOKE ALL ON SCHEMA stig_test_schema FROM public\"
  $ psql -c \"GRANT ALL ON SCHEMA stig_test_schema TO postgres\"
  Next, create a test table, insert a value into that table for the following
  checks by running the following SQL:
  $ psql -c \"CREATE TABLE stig_test_schema.stig_test_table(id INT)\"
  $ psql -c \"INSERT INTO stig_test_schema.stig_test_table(id) VALUES (0)\"
  #### CREATE
Attempt to CREATE a table in the stig_test_schema schema with a
  role that does not have privileges by running the following SQL:
  psql -c \"CREATE ROLE bob; SET ROLE bob; CREATE TABLE
  stig_test_schema.test_table(id INT);\"
  ERROR: permission denied for schema stig_test_schema
  Next, as a database administrator (shown here as \"postgres\"), verify that
  the denial was logged:
  $ sudo su - postgres
  $ cat ${PGDATA?}/pg_log/<latest_log>
  < 2016-03-09 09:55:19.423 EST postgres 56e0393f.186b postgres: >ERROR:
  permission denied for schema stig_test_schema at character 14
  < 2016-03-09 09:55:19.423 EST postgres 56e0393f.186b postgres: >STATEMENT:
  CREATE TABLE stig_test_schema.test_table(id INT);
  If the denial is not logged, this is a finding.
  #### INSERT
  As role bob, attempt to INSERT into the table created earlier, stig_test_table
  by running the following SQL:
  $ sudo su - postgres
  $ psql -c \"SET ROLE bob; INSERT INTO stig_test_schema.stig_test_table(id)
  VALUES (0);\"
  Next, as a database administrator (shown here as \"postgres\"), verify that
  the denial was logged:
  $ sudo su - postgres
  $ cat ${PGDATA?}/pg_log/<latest_log>
< 2016-03-09 09:58:30.709 EST postgres
  56e0393f.186b postgres: >ERROR: permission denied for schema stig_test_schema
  at character 13
  < 2016-03-09 09:58:30.709 EST postgres 56e0393f.186b postgres: >STATEMENT:
  INSERT INTO stig_test_schema.stig_test_table(id) VALUES (0);
  If the denial is not logged, this is a finding.
  #### SELECT
  As role bob, attempt to SELECT from the table created earlier, stig_test_table
  by running the following SQL:
  $ sudo su - postgres
  $ psql -c \"SET ROLE bob; SELECT * FROM stig_test_schema.stig_test_table;\"
  Next, as a database administrator (shown here as \"postgres\"), verify that
  the denial was logged:
  $ sudo su - postgres
  $ cat ${PGDATA?}/pg_log/<latest_log>
  < 2016-03-09 09:57:58.327 EST postgres 56e0393f.186b postgres: >ERROR:
  permission denied for schema stig_test_schema at character 15
  < 2016-03-09 09:57:58.327 EST postgres 56e0393f.186b postgres: >STATEMENT:
  SELECT * FROM stig_test_schema.stig_test_table;
  If the denial is not logged, this is a finding.
  #### ALTER
  As role bob, attempt to ALTER the table created earlier, stig_test_table by
  running the following SQL:
  $ sudo su - postgres
  $ psql -c \"SET ROLE bob; ALTER TABLE stig_test_schema.stig_test_table ADD
  COLUMN name TEXT;\"
  Next, as a database administrator (shown here as \"postgres\"), verify that
  the denial was logged:
  $ sudo su - postgres
  $ cat ${PGDATA?}/pg_log/<latest_log>
  < 2016-03-09 10:03:43.765 EST postgres 56e0393f.186b postgres: >STATEMENT:
  ALTER TABLE stig_test_schema.stig_test_table ADD COLUMN name TEXT;
  If the denial is not logged, this is a finding.
  #### UPDATE
  As role bob, attempt to UPDATE a row created earlier, stig_test_table by
  running the following SQL:
  $ sudo su - postgres
  $ psql -c \"SET ROLE bob; UPDATE stig_test_schema.stig_test_table SET id=1
  WHERE id=0;\"
  Next, as a database administrator (shown here as \"postgres\"), verify that
  the denial was logged:
  $ sudo su - postgres
  $ cat ${PGDATA?}/pg_log/<latest_log>
  < 2016-03-09 10:08:27.696 EST postgres 56e0393f.186b postgres: >ERROR:
  permission denied for schema stig_test_schema at character 8
  < 2016-03-09 10:08:27.696 EST postgres 56e0393f.186b postgres: >STATEMENT:
  UPDATE stig_test_schema.stig_test_table SET id=1 WHERE id=0;
  If the denial is not logged, this is a finding.
  #### DELETE
  As role bob, attempt to DELETE a row created earlier, stig_test_table by
  running the following SQL:
  $ sudo su - postgres
  $ psql -c \"SET ROLE bob; DELETE FROM stig_test_schema.stig_test_table
  WHERE id=0;\"
  Next, as a database administrator (shown here as \"postgres\"), verify that
  the denial was logged:
  $ sudo su - postgres
  $ cat ${PGDATA?}/pg_log/<latest_log>
  < 2016-03-09 10:09:29.607 EST postgres 56e0393f.186b postgres: >ERROR:
  permission denied for schema stig_test_schema at character 13
  < 2016-03-09 10:09:29.607 EST postgres 56e0393f.186b postgres: >STATEMENT:
  DELETE FROM stig_test_schema.stig_test_table WHERE id=0;
  If the denial is not logged, this is a finding.
  #### PREPARE
  As role bob, attempt to execute a prepared system using PREPARE by running the
  following SQL:
  $ sudo su - postgres
  $ psql -c \"SET ROLE bob; PREPARE stig_test_plan(int) AS SELECT id FROM
  stig_test_schema.stig_test_table WHERE id=$1;\"
  Next, as a database administrator (shown here as \"postgres\"), verify that
  the denial was logged:
  $ sudo su - postgres
  $ cat ${PGDATA?}/pg_log/<latest_log>
  < 2016-03-09 10:16:22.628 EST postgres 56e03e02.18e4 postgres: >ERROR:
  permission denied for schema stig_test_schema at character 46
  < 2016-03-09 10:16:22.628 EST postgres 56e03e02.18e4 postgres: >STATEMENT:
  PREPARE stig_test_plan(int) AS SELECT id FROM stig_test_schema.stig_test_table
  WHERE id=$1;
  If the denial is not logged, this is a finding.
  #### DROP
  As role bob, attempt to DROP the table created earlier stig_test_table by
  running the following SQL:
  $ sudo su - postgres
  $ psql -c \"SET ROLE bob; DROP TABLE stig_test_schema.stig_test_table;\"
  Next, as a database administrator (shown here as \"postgres\"), verify that
  the denial was logged:
  $ sudo su - postgres
  $ cat ${PGDATA?}/pg_log/<latest_log>
  < 2016-03-09 10:18:55.255 EST postgres 56e03e02.18e4 postgres: >ERROR:
  permission denied for schema stig_test_schema
  < 2016-03-09 10:18:55.255 EST postgres 56e03e02.18e4 postgres: >STATEMENT:
  DROP TABLE stig_test_schema.stig_test_table;
  If the denial is not logged, this is a finding."
  tag "fix": "Configure PostgreSQL to produce audit records when unsuccessful
  attempts to access security objects occur.
  All denials are logged if logging is enabled. To ensure that logging is
  enabled, review supplementary content APPENDIX-C for instructions on enabling
  logging."

  sql = postgres_session(PG_DBA, PG_DBA_PASSWORD, PG_HOST)
  log_directory_query = sql.query('SHOW log_directory;', [PG_DB])
  log_directory = log_directory_query.output
  current_log_command = "ls -1t #{log_directory}/*.pg_log | head -1"
  current_log = command(current_log_command).stdout.strip
  control = File.basename(__FILE__, File.extname(__FILE__))
  message = "starting tests for #{control}"
  message_sql = "DO language plpgsql $$ BEGIN "\
    "RAISE LOG '#{message}'; END $$;"
  start = Time.now.strftime('%Y-%m-%d %H:%M')
  get_logs = "sed -nre '/#{start}.*LOG:\\s+#{message}/,$p' #{current_log}"

  create_schema = "CREATE SCHEMA stig_test_schema AUTHORIZATION #{PG_OWNER};"
  revoke_schema = 'REVOKE ALL ON SCHEMA stig_test_schema FROM public;'
  grant_schema = "GRANT ALL ON SCHEMA stig_test_schema TO #{PG_DBA};"
  create_table = 'CREATE TABLE stig_test_schema.stig_test_table(id INT);'
  insert = 'INSERT INTO stig_test_schema.stig_test_table(id) VALUES (0);'
  drop_role = 'DROP ROLE bob;'
  drop_table = 'DROP TABLE stig_test_schema.stig_test_table;'
  drop_schema = 'DROP SCHEMA stig_test_schema;'
  command = 'CREATE ROLE bob; SET ROLE bob; '\
    'CREATE TABLE stig_test_schema.test_table(id INT);'
  error = 'permission denied for schema stig_test_schema'

  sql.query(message_sql, [PG_DB])
  sql.query(create_schema, [PG_DB])
  sql.query(revoke_schema, [PG_DB])
  sql.query(grant_schema, [PG_DB])
  sql.query(create_table, [PG_DB])
  sql.query(insert, [PG_DB])

  describe sql.query(command, [PG_DB]) do
    it { should match /ERROR:\s+#{error}/ }
  end

  sql.query(drop_role, [PG_DB])
  sql.query(drop_table, [PG_DB])
  sql.query(drop_schema, [PG_DB])

  describe command(get_logs) do
    its('stdout') { should match /ERROR:\s+#{error}/ }
    its('stdout') { should match /STATEMENT:\s+#{Regexp.escape(command)}/ }
  end
end