V-72947

control "V-72947" do
  title "PostgreSQL must be able to generate audit records when
  privileges/permissions are retrieved."
  desc  "Under some circumstances, it may be useful to monitor who/what is
  reading privilege/permission/role information. Therefore, it must be possible
  to configure auditing to do this. PostgreSQLs typically make such information
  available through views or functions.
  This requirement addresses explicit requests for privilege/permission/role
  membership information. It does not refer to the implicit retrieval of
  privileges/permissions/role memberships that PostgreSQL continually performs
  to determine if any and every action on the database is permitted."
  impact 0.5
  tag "severity": "medium"
  tag "gtitle": "SRG-APP-000091-DB-000066"
  tag "gid": "V-72947"
  tag "rid": "SV-87599r1_rule"
  tag "stig_id": "PGS9-00-005500"
  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\"), check if
  pgaudit is enabled by running the following SQL:
  $ sudo su - postgres
  $ psql -c \"SHOW shared_preload_libraries\"
  If pgaudit is not found in the results, this is a finding.
  Next, as the database administrator (shown here as \"postgres\"), list all
  role memberships for the database:
  $ sudo su - postgres
$ psql -c \"\\du\"
  Next, verify the query was logged:
  $ sudo su - postgres
  $ cat ${PGDATA?}/pg_log/<latest_log>
  < 2016-01-28 19:43:12.126 UTC postgres postgres: >LOG: AUDIT:
  SESSION,1,1,READ,SELECT,,,\"SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
  FROM pg_catalog.pg_auth_members m
  JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
  WHERE m.member = r.oid) as memberof
  , r.rolreplication
  , r.rolbypassrls
  FROM pg_catalog.pg_roles r
  ORDER BY 1;\",<none>
  If audit records are not produced, 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_catalog = 'on'
  pgaudit.log = 'read'
  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}"

  sql.query(message_sql, [PG_DB])

  describe sql.query('SHOW shared_preload_libraries;', [PG_DB]) do
    its('output') { should include 'pgaudit' }
  end

  command_sql = "psql -d #{PG_DB} -A -t -E -c '\\du'"
  output = command_query = command(command_sql).stdout.strip
  query = output.match(/(\*+ QUERY \*+)\n(.*;)/m)
  query_line = query ? query[2].split("\n").first : 'SELECT'
  command_message = "LOG:\\s+AUDIT:\\s+SESSION,[0-9]+,[0-9]+,READ,"\
    "SELECT(,.*){2},\"?#{query_line}"
  command_regex = Regexp.new(command_message)

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