V-73017

Severity: Medium

Generated

2019-05-20 15:48:11.984914

Status

Passed

PostgreSQL must enforce access restrictions associated with changes to the configuration of PostgreSQL or database(s).

NIST 800-53

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

Guidance

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.

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.

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 NOSUPERUSER”

Use REVOKE to remove privileges from databases and schemas:

$ psql -c “REVOKE ALL PRIVILEGES ON

FROM ;

Test Results

  Result
PostgreSQL query: SELECT r.rolsuper FROM pg_catalog.pg_roles r WHERE r.rolname = 'pg_signal_backend'; output should not eq "t" passed
PostgreSQL query: SELECT r.rolsuper FROM pg_catalog.pg_roles r WHERE r.rolname = 'dashboard'; output should not eq "t" passed
PostgreSQL query: SELECT r.rolsuper FROM pg_catalog.pg_roles r WHERE r.rolname = 'pg_monitor'; output should not eq "t" passed
PostgreSQL query: SELECT r.rolsuper FROM pg_catalog.pg_roles r WHERE r.rolname = 'pg_read_all_settings'; output should not eq "t" passed
PostgreSQL query: SELECT r.rolsuper FROM pg_catalog.pg_roles r WHERE r.rolname = 'pg_read_all_stats'; output should not eq "t" passed
PostgreSQL query: SELECT r.rolsuper FROM pg_catalog.pg_roles r WHERE r.rolname = 'pg_stat_scan_tables'; output should not eq "t" passed
PostgreSQL query: SELECT r.rolsuper FROM pg_catalog.pg_roles r WHERE r.rolname = 'replication'; output should not eq "t" passed
PostgreSQL query: SELECT r.rolsuper FROM pg_catalog.pg_roles r WHERE r.rolname = 'haproxy'; output should not eq "t" passed
PostgreSQL query: SELECT r.rolsuper FROM pg_catalog.pg_roles r WHERE r.rolname = 'testuser'; output should not eq "t" passed
PostgreSQL query: SELECT pg_catalog.array_to_string(datacl, E',') FROM pg_catalog.pg_database WHERE datname = 'postgres'; output should match /^((((vcap|crunchy)=[CTc]+|=[c]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'public'; output should match /^((((vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(datacl, E',') FROM pg_catalog.pg_database WHERE datname = 'template0'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[CTc]+|=[c]+)\/\w+,?)+|)$/ passed
PostgreSQL query with errors: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = ''; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query with errors: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = ''; output should match /FATAL:\s+database "template0" is not currently accepting connections/ passed
PostgreSQL query with errors: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'psql: FATAL: database "template0" is not currently accepting connections'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query with errors: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'psql: FATAL: database "template0" is not currently accepting connections'; output should match /FATAL:\s+database "template0" is not currently accepting connections/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(datacl, E',') FROM pg_catalog.pg_database WHERE datname = 'crunchy_admin'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[CTc]+|=[c]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'public'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'broker'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(datacl, E',') FROM pg_catalog.pg_database WHERE datname = 'dashboard'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[CTc]+|=[c]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'public'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'dashboard'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(datacl, E',') FROM pg_catalog.pg_database WHERE datname = 'replicationdb'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[CTc]+|=[c]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'public'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(datacl, E',') FROM pg_catalog.pg_database WHERE datname = 'template1'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[CTc]+|=[c]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'public'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(datacl, E',') FROM pg_catalog.pg_database WHERE datname = 'testdb'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[CTc]+|=[c]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'public'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'tiger'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'tiger_data'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'topology'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(datacl, E',') FROM pg_catalog.pg_database WHERE datname = 'stig_test_db'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[CTc]+|=[c]+)\/\w+,?)+|)$/ passed
PostgreSQL query: SELECT pg_catalog.array_to_string(n.nspacl, E',') FROM pg_catalog.pg_namespace n WHERE n.nspname = 'public'; output should match /^((((testuser|dashboard|replication|vcap|crunchy)=[UC]+|=[U]+)\/\w+,?)+|)$/ passed

Code

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