V-72927

control "V-72927" do
  title "PostgreSQL must generate audit records when unsuccessful attempts to
  modify security objects occur."
  desc  "Changes in the database objects (tables, views, procedures, functions)
  that record and control permissions, privileges, and roles granted to users
  and roles must be tracked. Without an audit trail, unauthorized changes to the
  security subsystem could go undetected. The database could be severely
  compromised or rendered inoperative.
  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-000496-DB-000335"
  tag "gid": "V-72927"
  tag "rid": "SV-87579r1_rule"
  tag "stig_id": "PGS9-00-004800"
  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.
  As the database administrator (shown here as \"postgres\"), create a test role
  by running the following SQL:
  $ sudo su - postgres
  $ psql -c \"CREATE ROLE bob\"
  Next, to test if audit records are generated from unsuccessful attempts at
  modifying security objects, run the following SQL:
  $ sudo su - postgres
  $ psql -c \"SET ROLE bob; UPDATE pg_authid SET rolsuper = 't' WHERE
  rolname = 'bob';\"
  Next, as the database administrator (shown here as \"postgres\"), verify that
  the denials were logged:
  $ sudo su - postgres
  $ cat ${PGDATA?}/pg_log/<latest_log>
  < 2016-03-17 10:34:00.017 EDT bob 56eabf52.b62 postgres: >ERROR: permission
  denied for relation pg_authid
  < 2016-03-17 10:34:00.017 EDT bob 56eabf52.b62 postgres: >STATEMENT:
  UPDATE pg_authid SET rolsuper = 't' WHERE rolname = 'bob';
  If denials are not logged, this is a finding."
  tag "fix": "Configure PostgreSQL to produce audit records when unsuccessful
  attempts to modify security objects occur.
  Unsuccessful attempts to modifying security objects can be logged if logging
  is enabled. To ensure that logging is enabled, review supplementary content A
  PPENDIX-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_role = 'CREATE ROLE bob;'
  drop_role = 'DROP ROLE bob;'
  command = "SET ROLE bob; "\
    "UPDATE pg_authid SET rolsuper = 't' WHERE rolname = 'bob';"
  error = 'permission denied for relation pg_authid'

  sql.query(message_sql, [PG_DB])
  sql.query(create_role, [PG_DB])

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

  sql.query(drop_role, [PG_DB])

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