V-72855

Severity: Medium

Generated

2019-05-20 15:48:11.984914

Status

Passed

PostgreSQL must limit privileges to change functions and triggers, and links to software external to PostgreSQL.

NIST 800-53

STIG # Description Result
CM-5 CM-5: Access Restrictions For Change passed

Guidance

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.

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.

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”

Test Results

  Result
PostgreSQL query: 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 ('vcap','crunchy'); output should eq "" passed
PostgreSQL query with errors: 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 ('testuser','dashboard','replication','vcap','crunchy'); output should match /FATAL:\s+database "template0" is not currently accepting connections/ passed
PostgreSQL query: 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 ('testuser','dashboard','replication','vcap','crunchy'); output should eq "" passed
PostgreSQL query: 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 ('testuser','dashboard','replication','vcap','crunchy'); output should eq "" passed
PostgreSQL query: 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 ('testuser','dashboard','replication','vcap','crunchy'); output should eq "" passed
PostgreSQL query: 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 ('testuser','dashboard','replication','vcap','crunchy'); output should eq "" passed
PostgreSQL query: 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 ('testuser','dashboard','replication','vcap','crunchy'); output should eq "" passed
PostgreSQL query: 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 ('testuser','dashboard','replication','vcap','crunchy'); output should eq "" passed
Directory /var/vcap/store/postgresql/data should be directory passed
Directory /var/vcap/store/postgresql/data should be owned by "vcap" passed
Directory /var/vcap/store/postgresql/data mode should cmp == "0700" passed
Command: `find -L /var/vcap/store/postgresql/data -type f \( ! -user vcap -or -perm -g=w -or -perm -o=w \)` stdout should eq "" passed
Command: `find -L /var/vcap/store/postgresql/data -type f \( ! -user vcap -or -perm -g=w -or -perm -o=w \)` exit_status should cmp == 0 passed

Code

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