V-72997

control "V-72997" do
  title "PostgreSQL must prohibit user installation of logic modules (functions,
  trigger procedures, views, etc.) without explicit privileged status."
  desc  "Allowing regular users to install software, without explicit privileges,
  creates the risk that untested or potentially malicious software will be installed
  on the system. Explicit privileges (escalated or administrative privileges) provide
  the regular user with explicit capabilities and control that exceed the rights of a
  regular user.

  PostgreSQL functionality and the nature and requirements of databases will vary; so
  while users are not permitted to install unapproved software, there may be instances
  where the organization allows the user to install approved software packages such as
  from an approved software repository. The requirements for production servers will
  be more restrictive than those used for development and research.

  PostgreSQL must enforce software installation by users based upon what types of
  software installations are permitted (e.g., updates and security patches to existing
  software) and what types of installations are prohibited (e.g., software whose
  pedigree with regard to being potentially malicious is unknown or suspect) by the
  organization).

  In the case of a database management system, this requirement covers stored
  procedures, functions, triggers, views, etc."

  impact 0.5
  tag "severity": "medium"
  tag "gtitle": "SRG-APP-000378-DB-000365"
  tag "gid": "V-72997"
  tag "rid": "SV-87649r1_rule"
  tag "stig_id": "PGS9-00-008400"
  tag "cci": "CCI-001812"
  tag "nist": ["CM-11 (2)", "Rev_4"]

  tag "check": "If PostgreSQL supports only software development, experimentation
and/or developer-level testing (that is, excluding production systems, integration
testing, stress testing, and user acceptance testing), this is not a finding.

Review PostgreSQL and database security settings with respect to non-administrative
users' ability to create, alter, or replace logic modules, to include but not
necessarily only stored procedures, functions, triggers, and views.

To list the privileges for all tables and schemas, as the database administrator
(shown here as \"postgres\"), run the following:

$ sudo su - postgres
$ psql -c \"\\dp\"
$ psql -c \"\\dn+\"

The privileges are as follows:

rolename=xxxx -- privileges granted to a role
=xxxx -- privileges granted to PUBLIC

r -- SELECT (\"read\")
w -- UPDATE (\"write\")
a -- INSERT (\"append\")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
* -- grant option for preceding privilege

/yyyy -- role that granted this privilege

If any such permissions exist and are not documented and approved, this is a
finding."
  tag "fix": "Document and obtain approval for any non-administrative users who
require the ability to create, alter or replace logic modules.

Implement the approved permissions. Revoke any unapproved permissions."

  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.join('|')
  superusers = PG_SUPERUSERS.join('|')

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

  databases.each do |database|
    role_list = database == 'postgres' ? superusers : owners
    object_acl = "^((((#{role_list})=[#{PG_OBJECT_GRANTED_PRIVILEGES}]+|"\
      "=[#{PG_OBJECT_PUBLIC_PRIVILEGES}]+)\\/\\w+,?)+|)$"
    schema_acl = "^((((#{role_list})=[#{PG_SCHEMA_GRANTED_PRIVILEGES}]+|"\
      "=[#{PG_SCHEMA_PUBLIC_PRIVILEGES}]+)\\/\\w+,?)+|)$"

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

    objects_sql = "SELECT n.nspname, c.relname, c.relkind, "\
      "pg_catalog.array_to_string(c.relacl, E',') FROM pg_catalog.pg_class c "\
      "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "\
      "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f') "\
      "AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)"\
      "AND pg_catalog.array_to_string(c.relacl, E',') !~ '#{object_acl}';"

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

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

    schemas_sql = "SELECT n.nspname, "\
      "pg_catalog.array_to_string(n.nspacl, E',') "\
      "FROM pg_catalog.pg_namespace n "\
      "WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'"\
      "AND pg_catalog.array_to_string(n.nspacl, E',') !~ '#{schema_acl}';"

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

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