V-72923

control "V-72923" do
  title "PostgreSQL must generate audit records when unsuccessful logons or
  connection attempts occur."
  desc  "For completeness of forensic analysis, it is necessary to track failed
  attempts to log on to PostgreSQL. While positive identification may not be
  possible in a case of failed authentication, as much information as possible
  about the incident must be captured."
  impact 0.5
  tag "severity": "medium"
  tag "gtitle": "SRG-APP-000503-DB-000351"
  tag "gid": "V-72923"
  tag "rid": "SV-87575r1_rule"
  tag "stig_id": "PGS9-00-004600"
  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.
  In this example the user joe will log into the Postgres database unsuccessfully:
  $ psql -d postgres -U joe
  As the database administrator (shown here as \"postgres\"), check pg_log for a
  FATAL connection audit trail:
  $ sudo su - postgres
  $ cat ${PGDATA?}/pg_log/postgresql-Tue.log
  < 2016-02-16 16:18:13.027 EST joe 56c65135.b5f postgres: >LOG: connection
  authorized: user=joe database=postgres
  < 2016-02-16 16:18:13.027 EST joe 56c65135.b5f postgres: >FATAL: role \"joe\"
  does not exist
  If an audit record is not generated each time a user (or other principal)
  attempts, but fails to log on or connect to PostgreSQL (including attempts
  where the user ID is invalid/unknown), 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.
  To ensure that logging is enabled, review supplementary content APPENDIX-C for
  instructions on enabling logging.
  If logging is enabled the following configurations must be made to log
  unsuccessful connections, date/time, username, and session identifier.
  First, as the database administrator (shown here as \"postgres\"), edit
  postgresql.conf:
  $ sudo su - postgres
  $ vi ${PGDATA?}/postgresql.conf
  Edit the following parameters:
  log_connections = on
  log_line_prefix = '< %m %u %c: >'
  Where:
  * %m is the time and date
  * %u is the username
  * %c is the session ID for the connection
  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}"

  user = 'joe'
  password = 'badpassword'

  sql.query(message_sql, [PG_DB])
  joe = postgres_session(user, password, PG_HOST)
  joe.query('SELECT now();', [PG_DB])

  describe command(get_logs) do
    its('stdout') { should match /FATAL:\s+[\w\s]+(user|role) "#{user}"/ }
  end
end