V-72883

Severity: Medium

Generated

2019-05-20 15:48:11.984914

Status

Passed

PostgreSQL must enforce discretionary access control policies, as defined by the data owner, over defined subjects and objects.

NIST 800-53

STIG # Description Result
AC-3 AC-3: Access Enforcement passed

Guidance

Discretionary Access Control (DAC) is based on the notion that individual users are “owners” of objects and therefore have discretion over who should be authorized to access the object and in which mode (e.g., read or write). Ownership is usually acquired as a consequence of creating the object or via specified ownership assignment. DAC allows the owner to determine who will have access to objects they control. An example of DAC includes user-controlled table permissions. When discretionary access control policies are implemented, subjects are not constrained with regard to what actions they can take with information for which they have already been granted access. Thus, subjects that have been granted access to information are not prevented from passing (i.e., the subjects have the discretion to pass) the information to other subjects or objects. A subject that is constrained in its operation by Mandatory Access Control policies is still able to operate under the less rigorous constraints of this requirement. Thus, while Mandatory Access Control imposes constraints preventing a subject from passing information to another subject operating at a different sensitivity level, this requirement permits the subject to pass the information to any subject at the same sensitivity level. The policy is bounded by the information system boundary. Once the information is passed outside of the control of the information system, additional means may be required to ensure the constraints remain in effect. While the older, more traditional definitions of discretionary access control require i dentity-based access control, that limitation is not required for this use of discretionary access control.

Check

Review system documentation to identify the required discretionary access control (DAC).

Review the security configuration of the database and PostgreSQL. If applicable, review the security configuration of the application(s) using the database.

If the discretionary access control defined in the documentation is not implemented in the security configuration, this is a finding.

If any database objects are found to be owned by users not authorized to own database objects, this is a finding.

To check the ownership of objects in the database, as the database administrator, run the following: $ sudo su - postgres $ psql -c “\dn .“ $ psql -c “\dt .“ $ psql -c “\ds .“ $ psql -c “\dv .“ $ psql -c “\df+ .“ If any role is given privileges to objects it should not have, this is a finding.

Fix

Implement the organization’s DAC policy in the security configuration of the database and PostgreSQL, and, if applicable, the security configuration of the application(s) using the database. To GRANT privileges to roles, as the database administrator (shown here as “postgres”), run statements like the following examples: $ sudo su - postgres $ psql -c “CREATE SCHEMA test” $ psql -c “GRANT CREATE ON SCHEMA test TO bob” $ psql -c “CREATE TABLE test.test_table(id INT)” $ psql -c “GRANT SELECT ON TABLE test.test_table TO bob” To REVOKE privileges to roles, as the database administrator (shown here as “postgres”), run statements like the following examples: $ psql -c “REVOKE SELECT ON TABLE test.test_table FROM bob” $ psql -c “REVOKE CREATE ON SCHEMA test FROM bob”

Test Results

  Result
PostgreSQL query: SELECT n.nspname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_namespace n WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('vcap','crunchy') AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, p.proname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('t','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('s','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('v','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query with errors: SELECT n.nspname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_namespace n WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'; output should match /FATAL:\s+database "template0" is not currently accepting connections/ passed
PostgreSQL query with errors: SELECT n.nspname, p.proname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'; output should match /FATAL:\s+database "template0" is not currently accepting connections/ passed
PostgreSQL query with errors: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('t','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should match /FATAL:\s+database "template0" is not currently accepting connections/ passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('v','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query with errors: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('s','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should match /FATAL:\s+database "template0" is not currently accepting connections/ passed
PostgreSQL query with errors: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('v','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should match /FATAL:\s+database "template0" is not currently accepting connections/ passed
PostgreSQL query: SELECT n.nspname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_namespace n WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, p.proname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('t','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('s','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('v','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_namespace n WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, p.proname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('t','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('s','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('v','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_namespace n WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, p.proname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('t','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('s','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_namespace n WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, p.proname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('t','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('s','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('v','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_namespace n WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, p.proname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('t','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('s','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('v','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_namespace n WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, p.proname, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('t','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('s','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed
PostgreSQL query: SELECT n.nspname, c.relname, c.relkind, pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('v','s','') AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN ('testuser','dashboard','replication','vcap','crunchy') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'; output should eq "" passed

Code

control "V-72883" do
  title "PostgreSQL must enforce discretionary access control policies, as
  defined by the data owner, over defined subjects and objects."
  desc  "Discretionary Access Control (DAC) is based on the notion that
  individual users are \"owners\" of objects and therefore have discretion over
  who should be authorized to access the object and in which mode (e.g., read or
  write). Ownership is usually acquired as a consequence of creating the object
  or via specified ownership assignment. DAC allows the owner to determine who
  will have access to objects they control. An example of DAC includes
  user-controlled table permissions.
  When discretionary access control policies are implemented, subjects are not
  constrained with regard to what actions they can take with information for
  which they have already been granted access. Thus, subjects that have been
  granted access to information are not prevented from passing (i.e., the
  subjects have the discretion to pass) the information to other subjects or
  objects.
  A subject that is constrained in its operation by Mandatory Access Control
  policies is still able to operate under the less rigorous constraints of this
  requirement. Thus, while Mandatory Access Control imposes constraints
  preventing a subject from passing information to another subject operating at
  a different sensitivity level, this requirement permits the subject to pass
  the information to any subject at the same sensitivity level.
  The policy is bounded by the information system boundary. Once the information
  is passed outside of the control of the information system, additional means
  may be required to ensure the constraints remain in effect. While the older,
  more traditional definitions of discretionary access control require i
  dentity-based access control, that limitation is not required for this use of
  discretionary access control."
  impact 0.5
  tag "severity": "medium"
  tag "gtitle": "SRG-APP-000328-DB-000301"
  tag "gid": "V-72883"
  tag "rid": "SV-87535r1_rule"
  tag "stig_id": "PGS9-00-002200"
  tag "cci": "CCI-002165"
  tag "nist": ["AC-3 (4)", "Rev_4"]
  tag "check": "Review system documentation to identify the required
  discretionary access control (DAC).

  Review the security configuration of the database and PostgreSQL. If
  applicable, review the security configuration of the application(s) using the
  database.

  If the discretionary access control defined in the documentation is not
  implemented in the security configuration, this is a finding.

  If any database objects are found to be owned by users not authorized to own
  database objects, this is a finding.

  To check the ownership of objects in the database, as the database
  administrator, run the following:
  $ sudo su - postgres
  $ psql -c \"\\dn *.*\"
  $ psql -c \"\\dt *.*\"
  $ psql -c \"\\ds *.*\"
  $ psql -c \"\\dv *.*\"
  $ psql -c \"\\df+ *.*\"
  If any role is given privileges to objects it should not have, this is a
  finding."
  tag "fix": "Implement the organization's DAC policy in the security
  configuration of the database and PostgreSQL, and, if applicable, the security
  configuration of the application(s) using the database.
  To GRANT privileges to roles, as the database administrator (shown here as
  \"postgres\"), run statements like the following examples:
  $ sudo su - postgres
  $ psql -c \"CREATE SCHEMA test\"
  $ psql -c \"GRANT CREATE ON SCHEMA test TO bob\"
  $ psql -c \"CREATE TABLE test.test_table(id INT)\"
  $ psql -c \"GRANT SELECT ON TABLE test.test_table TO bob\"
  To REVOKE privileges to roles, as the database administrator (shown here as
  \"postgres\"), run statements like the following examples:
  $ psql -c \"REVOKE SELECT ON TABLE test.test_table FROM bob\"
  $ psql -c \"REVOKE CREATE ON SCHEMA test FROM bob\""

  object_types = %w(t s v) # tables, sequences views
  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_query = sql.query(databases_sql, [PG_DB])
  databases = databases_query.lines

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

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

    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

    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

    object_types.each do |type|
      objects_sql = "SELECT n.nspname, c.relname, c.relkind, "\
        "pg_catalog.pg_get_userbyid(n.nspowner) FROM pg_catalog.pg_class c "\
        "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "\
        "WHERE c.relkind IN ('#{type}','s','') "\
        "AND pg_catalog.pg_get_userbyid(n.nspowner) NOT IN (#{role_list}) "\
        "AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' "\
        "AND n.nspname !~ '^pg_toast';"

      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
    end
  end
end