V-72939

control "V-72939" do
  title "PostgreSQL must generate audit records when security objects are
  deleted."
  desc  "The removal of security objects from the database/PostgreSQL would
  seriously degrade a system's information assurance posture. If such an event
  occurs, it must be logged."
  impact 0.5
  tag "severity": "medium"
  tag "gtitle": "SRG-APP-000501-DB-000336"
  tag "gid": "V-72939"
  tag "rid": "SV-87591r1_rule"
  tag "stig_id": "PGS9-00-005200"
  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\"), create a
  test table stig_test, enable row level security, and create a policy by
  running the following SQL:
  $ sudo su - postgres
  $ psql -c \"CREATE TABLE stig_test(id INT)\"
  $ psql -c \"ALTER TABLE stig_test ENABLE ROW LEVEL SECURITY\"
  $ psql -c \"CREATE POLICY lock_table ON stig_test USING ('postgres' =
  current_user)\"
  Next, drop the policy and disable row level security:
  $ psql -c \"DROP POLICY lock_table ON stig_test\"
  $ psql -c \"ALTER TABLE stig_test DISABLE ROW LEVEL SECURITY\"
  Now, as the database administrator (shown here as \"postgres\"), verify the
  security objects deletions were logged:
  $ cat ${PGDATA?}/pg_log/<latest_log>
  2016-03-30 14:54:18.991 EDT postgres postgres LOG: AUDIT:
  SESSION,11,1,DDL,DROP POLICY,,,DROP POLICY lock_table ON stig_test;,<none>
  2016-03-30 14:54:42.373 EDT postgres postgres LOG: AUDIT:
  SESSION,12,1,DDL,ALTER TABLE,,,ALTER TABLE stig_test DISABLE ROW LEVEL
  SECURITY;,<none>
  If audit records are not produced when security objects are dropped, this is a
  finding."
  tag "fix": "Note: The following instructions use the PGDATA environment
  variable. See supplementary content APPENDIX-F for instructions on configuring
  PGDATA.
  Using pgaudit PostgreSQL can be configured to audit these requests. See
  supplementary content APPENDIX-B for documentation on installing pgaudit.
  With pgaudit installed the following configurations can be made:
  $ sudo su - postgres
  $ vi ${PGDATA?}/postgresql.conf
  Add the following parameters (or edit existing parameters):
  pgaudit.log = 'ddl'
  Now, as the system administrator, reload the server with the new configuration:
  # SYSTEMD SERVER ONLY
  $ sudo systemctl reload postgresql-9.5
  # INITD SERVER ONLY
  $ sudo service postgresql-9.5 reload"

  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_table =  'CREATE TABLE stig_test(id INT);'
  enable_rls = 'ALTER TABLE stig_test ENABLE ROW LEVEL SECURITY'
  create_policy = "CREATE POLICY lock_table ON stig_test USING ('#{PG_DBA}' = current_user);"
  drop_policy = 'DROP POLICY lock_table ON stig_test;'
  disable_rls = 'ALTER TABLE stig_test DISABLE ROW LEVEL SECURITY;'
  drop_table =  'DROP TABLE stig_test;'
  drop_policy_message = "LOG:\\s+AUDIT:\\s+SESSION,[0-9]+,[0-9]+,DDL,"\
    "DROP POLICY(,.*){2},#{drop_policy}"
  drop_policy_regex = Regexp.new(drop_policy_message)
  disable_rls_message = "LOG:\\s+AUDIT:\\s+SESSION,[0-9]+,[0-9]+,DDL,"\
    "ALTER TABLE(,.*){2},#{disable_rls}"
  disable_rls_regex = Regexp.new(disable_rls_message)

  sql.query(message_sql, [PG_DB])
  sql.query(create_table, [PG_DB])
  sql.query(enable_rls, [PG_DB])
  sql.query(create_policy, [PG_DB])
  sql.query(drop_policy, [PG_DB])
  sql.query(disable_rls, [PG_DB])
  sql.query(drop_table, [PG_DB])

  describe command(get_logs) do
    its('stdout') { should match drop_policy_regex }
    its('stdout') { should match disable_rls_regex }
  end
end