STIGQter: STIG Summary: PostgreSQL 9.x Security Technical Implementation Guide Version: 2 Release: 1 Benchmark Date: 23 Oct 2020:

PostgreSQL must enforce approved authorizations for logical access to information and system resources in accordance with applicable access control policies.

DISA Rule

SV-214057r508027_rule

Vulnerability Number

V-214057

Group Title

SRG-APP-000033-DB-000084

Rule Version

PGS9-00-000900

Severity

CAT II

CCI(s)

• CCI-000213 - The information system enforces approved authorizations for logical access to information and system resources in accordance with applicable access control policies.

Weight

10

Fix Recommendation

Create and/or maintain documentation of each group role's appropriate permissions on database objects.

Implement these permissions in the database, and remove any permissions that exceed those documented.

- - - - -

The following are examples of how to use role privileges in PostgreSQL to enforce access controls. For a complete list of privileges, see the official documentation: https://www.postgresql.org/docs/current/static/sql-createrole.html

#### Roles Example 1

The following example demonstrates how to create an admin role with CREATEDB and CREATEROLE privileges.

As the database administrator (shown here as "postgres"), run the following SQL:

$ sudo su - postgres

$ psql -c "CREATE ROLE admin WITH CREATEDB CREATEROLE"

#### Roles Example 2

The following example demonstrates how to create a role with a password that expires and makes the role a member of the "admin" group.

As the database administrator (shown here as "postgres"), run the following SQL:

$ sudo su - postgres

$ psql -c "CREATE ROLE joe LOGIN ENCRYPTED PASSWORD 'stig2016!' VALID UNTIL '2016-09-20' IN ROLE admin"

#### Roles Example 3

The following demonstrates how to revoke privileges from a role using REVOKE.

As the database administrator (shown here as "postgres"), run the following SQL:

$ sudo su - postgres

$ psql -c "REVOKE admin FROM joe"

#### Roles Example 4

The following demonstrates how to alter privileges in a role using ALTER.

As the database administrator (shown here as "postgres"), run the following SQL:

$ sudo su - postgres

$ psql -c "ALTER ROLE joe NOLOGIN"

The following are examples of how to use grant privileges in PostgreSQL to enforce access controls on objects. For a complete list of privileges, see the official documentation: https://www.postgresql.org/docs/current/static/sql-grant.html

#### Grant Example 1

The following example demonstrates how to grant INSERT on a table to a role.

As the database administrator (shown here as "postgres"), run the following SQL:

$ sudo su - postgres

$ psql -c "GRANT SELECT ON stig_test TO joe"

#### Grant Example 2

The following example demonstrates how to grant ALL PRIVILEGES on a table to a role.

As the database administrator (shown here as "postgres"), run the following SQL:

$ sudo su - postgres

$ psql -c "GRANT ALL PRIVILEGES ON stig_test TO joe"

#### Grant Example 3

The following example demonstrates how to grant a role to a role.

As the database administrator (shown here as "postgres"), run the following SQL:

$ sudo su - postgres

$ psql -c "GRANT admin TO joe"

#### Revoke Example 1

The following example demonstrates how to revoke access from a role.

As the database administrator (shown here as "postgres"), run the following SQL:

$ sudo su - postgres

$ psql -c "REVOKE admin FROM joe"

To change authentication requirements for the database, as the database administrator (shown here as "postgres"), edit pg_hba.conf:

$ sudo su - postgres

$ vi ${PGDATA?}/pg_hba.conf

Edit authentication requirements to the organizational requirements. See the official documentation for the complete list of options for authentication: http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

After changes to pg_hba.conf, reload the server:

# SYSTEMD SERVER ONLY
$ sudo systemctl reload postgresql-${PGVER?}

# INITD SERVER ONLY
$ sudo service postgresql-${PGVER?} reload

Check Contents

From the system security plan or equivalent documentation, determine the appropriate permissions on database objects for each kind (group role) of user. If this documentation is missing, this is a finding.

First, as the database administrator (shown here as "postgres"), check the privileges of all roles in the database by running the following SQL:

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

Review all roles and their associated privileges. If any roles' privileges exceed those documented, this is a finding.

Next, as the database administrator (shown here as "postgres"), check the configured privileges for tables and columns by running the following SQL:

$ sudo su - postgres
$ psql -c '\dp'

Review all access privileges and column access privileges list. If any roles' privileges exceed those documented, this is a finding.

Next, as the database administrator (shown here as "postgres"), check the configured authentication settings in pg_hba.conf:

$ sudo su - postgres
$ cat ${PGDATA?}/pg_hba.conf

Review all entries and their associated authentication methods. If any entries do not have their documented authentication requirements, this is a finding.

Vulnerability Number

V-214057

Documentable

False

Rule Version

PGS9-00-000900

Severity Override Guidance

From the system security plan or equivalent documentation, determine the appropriate permissions on database objects for each kind (group role) of user. If this documentation is missing, this is a finding.

First, as the database administrator (shown here as "postgres"), check the privileges of all roles in the database by running the following SQL:

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

Review all roles and their associated privileges. If any roles' privileges exceed those documented, this is a finding.

Next, as the database administrator (shown here as "postgres"), check the configured privileges for tables and columns by running the following SQL:

$ sudo su - postgres
$ psql -c '\dp'

Review all access privileges and column access privileges list. If any roles' privileges exceed those documented, this is a finding.

Next, as the database administrator (shown here as "postgres"), check the configured authentication settings in pg_hba.conf:

$ sudo su - postgres
$ cat ${PGDATA?}/pg_hba.conf

Review all entries and their associated authentication methods. If any entries do not have their documented authentication requirements, this is a finding.

Check Content Reference

M

Target Key

3994

Comments