V-72855

control "V-72855" do
  title "PostgreSQL must limit privileges to change functions and triggers, and
  links to software external to PostgreSQL."
  desc  "If the system were to allow any user to make changes to software
  libraries, those changes might be implemented without undergoing the
  appropriate testing and approvals that are part of a robust change management
  process.  Accordingly, only qualified and authorized individuals must be
  allowed to obtain access to information system components for purposes of
  initiating changes, including upgrades and modifications.  nmanaged changes
  that occur to the database code can lead to unauthorized or compromised
  installations."
  impact 0.5
  tag "severity": "medium"
  tag "gtitle": "SRG-APP-000133-DB-000179"
  tag "gid": "V-72855"
  tag "rid": "SV-87507r1_rule"
  tag "stig_id": "PGS9-00-000710"
  tag "cci": "CCI-001499"
  tag "nist": ["CM-5 (6)", "Rev_4"]

  tag "check": "Only owners of objects can change them. To view all functions,
  triggers, and trigger procedures, their ownership and source, as the database
  administrator (shown here as \"postgres\") run the following SQL:

  $ sudo su - postgres
  $ psql -x -c \"\\df+\"

  Only the OS database owner user (shown here as \"postgres\") or a PostgreSQL
  superuser can change links to external software.

  As the database administrator (shown here as \"postgres\"), check the permissions
  of configuration files for the database:

  $ sudo su - postgres
  $ ls -la ${PGDATA?}

  If any files are not owned by the database owner or have permissions allowing
  others to modify (write) configuration files, this is a finding."

  tag "fix": "To change ownership of an object, as the database administrator
  (shown here as \"postgres\"), run the following SQL:

  $ sudo su – postgres\
  $ psql -c \"ALTER FUNCTION function_name OWNER TO new_role_name\"

  To change ownership of postgresql.conf, as the database administrator (shown
  here as \"postgres\"), run the following commands:

  $ sudo su - postgres
  $ chown postgres:postgres ${PGDATA?}/postgresql.conf
  $ chmod 0600 ${PGDATA?}/postgresql.conf

  To remove superuser from a role, as the database administrator (shown here as
  \"postgres\"), run the following SQL:

  $ sudo su - postgres
  $ psql -c \"ALTER ROLE rolename WITH NOSUPERUSER\""

  sql = postgres_session(PG_DBA, PG_DBA_PASSWORD, PG_HOST)

  binding_users = []
  if not PG_BINDINGS_DB.empty? and not PG_BINDING_USERS_SQL.empty?
    binding_users_query = sql.query(PG_BINDING_USERS_SQL, [PG_BINDINGS_DB])
    binding_users = binding_users_query.lines
  end

  authorized_owners = PG_OBJECT_OWNERS + binding_users
  owners = authorized_owners.map { |e| "'#{e}'" }.join(',')
  superusers = PG_SUPERUSERS.map { |e| "'#{e}'" }.join(',')

  databases_sql = 'SELECT datname FROM pg_catalog.pg_database;'
  databases = sql.query(databases_sql, [PG_DB]).lines

  databases.each do |database|
    role_list = database == 'postgres' ? superusers : owners
    functions_sql = "SELECT n.nspname, p.proname, "\
      "pg_catalog.pg_get_userbyid(p.proowner) FROM pg_catalog.pg_proc p "\
      "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace "\
      "WHERE pg_catalog.pg_function_is_visible(p.oid) "\
      "AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' "\
      "AND pg_catalog.pg_get_userbyid(p.proowner) NOT IN (#{role_list});"

    connection_error = "FATAL:\\s+database \"#{database}\" is not currently "\
      "accepting connections"
    connection_error_regex = Regexp.new(connection_error)

    describe.one do
      describe sql.query(functions_sql, [database]) do
        its('output') { should eq '' }
      end

      describe sql.query(functions_sql, [database]) do
        it { should match connection_error_regex }
      end
    end
  end

  describe directory(PG_DATA) do
    it { should be_directory }
    it { should be_owned_by PG_OWNER }
    its('mode') { should cmp '0700' }
  end

  find_insecure_files = "find -L #{PG_DATA} -type f \\( "\
    "! -user #{PG_OWNER} -or -perm -g=w -or -perm -o=w \\)"

  describe command(find_insecure_files) do
    its('stdout') { should eq '' }
    its('exit_status') { should cmp 0 }
  end
end