V-73017

control "V-73017" do
  title "PostgreSQL must enforce access restrictions associated with changes to the
configuration of PostgreSQL or database(s)."
  desc  "Failure to provide logical access restrictions associated with changes to
configuration may have significant effects on the overall security of the system.

When dealing with access restrictions pertaining to change control, it should be
noted that any changes to the hardware, software, and/or firmware components of the
information system can potentially have significant effects on the overall security
of the system.

Accordingly, only qualified and authorized individuals should be allowed to obtain
access to system components for the purposes of initiating changes, including
upgrades and modifications."
  impact 0.5
  tag "severity": "medium"
  tag "gtitle": "SRG-APP-000380-DB-000360"
  tag "gid": "V-73017"
  tag "rid": "SV-87669r1_rule"
  tag "stig_id": "PGS9-00-009600"
  tag "cci": "CCI-001813"
  tag "nist": ["CM-5 (1)", "Rev_4"]
  tag "check": "To list all the permissions of individual roles, as the database
administrator (shown here as \"postgres\"), run the following SQL:

$ sudo su - postgres
$ psql -c \"\\du

If any role has SUPERUSER that should not, this is a finding.

Next, list all the permissions of databases and schemas by running the following SQL:

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

If any database or schema has update (\"W\") or create (\"C\") privileges and should
not, this is a finding."
  tag "fix": "Configure PostgreSQL to enforce access restrictions associated with
changes to the configuration of PostgreSQL or database(s).

Use ALTER ROLE to remove accesses from roles:

$ psql -c \"ALTER ROLE <role_name> NOSUPERUSER\"

Use REVOKE to remove privileges from databases and schemas:

$ psql -c \"REVOKE ALL PRIVILEGES ON <table> FROM <role_name>;"

  sql = postgres_session(PG_DBA, PG_DBA_PASSWORD, PG_HOST)

  roles_sql = 'SELECT r.rolname FROM pg_catalog.pg_roles r;'
  roles_query = sql.query(roles_sql, [PG_DB])
  roles = roles_query.lines

  roles.each do |role|
    unless PG_SUPERUSERS.include?(role)
      superuser_sql = "SELECT r.rolsuper FROM pg_catalog.pg_roles r "\
        "WHERE r.rolname = '#{role}';"

      describe sql.query(superuser_sql, [PG_DB]) do
        its('output') { should_not eq 't' }
      end
    end
  end

  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('|')

  schemas_sql = "SELECT n.nspname FROM pg_catalog.pg_namespace n "\
    "WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema';"

  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
    database_acl = "^((((#{role_list})=[#{PG_DATABASE_GRANTED_PRIVILEGES}]+|"\
      "=[#{PG_DATABASE_PUBLIC_PRIVILEGES}]+)\/\\w+,?)+|)$"
    schema_acl = "^((((#{role_list})=[#{PG_SCHEMA_GRANTED_PRIVILEGES}]+|"\
      "=[#{PG_SCHEMA_PUBLIC_PRIVILEGES}]+)\/\\w+,?)+|)$"

    database_acl_regex = Regexp.new(database_acl)
    schema_acl_regex = Regexp.new(schema_acl)

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

    datacl_sql = "SELECT pg_catalog.array_to_string(datacl, E',') "\
      "FROM pg_catalog.pg_database WHERE datname = '#{database}';"

    describe.one do
      describe sql.query(datacl_sql, [PG_DB]) do
        its('output') { should match database_acl_regex }
      end

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

    schemas_query = sql.query(schemas_sql, [database])
    schemas = schemas_query.lines

    schemas.each do |schema|
      nspacl_sql = "SELECT pg_catalog.array_to_string(n.nspacl, E',') "\
        "FROM pg_catalog.pg_namespace n WHERE n.nspname = '#{schema}';"

      describe.one do
        describe sql.query(nspacl_sql, [database]) do
          its('output') { should match schema_acl_regex }
        end

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