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

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-72859	PGS9-00-000900	SV-87511r2_rule		Medium

Description
Authentication with a DoD-approved PKI certificate does not necessarily imply authorization to access PostgreSQL. To mitigate the risk of unauthorized access to sensitive information by entities that have been issued certificates by DoD-approved PKIs, all DoD systems, including databases, must be properly configured to implement access control policies. Successful authentication must not automatically give an entity access to an asset or security boundary. Authorization procedures and controls must be implemented to ensure each authenticated entity also has a validated and current authorization. Authorization is the process of determining whether an entity, once authenticated, is permitted to access a specific asset. Information systems use access control policies and enforcement mechanisms to implement this requirement. Access control policies include identity-based policies, role-based policies, and attribute-based policies. Access enforcement mechanisms include access control lists, access control matrices, and cryptography. These policies and mechanisms must be employed by the application to control access between users (or processes acting on behalf of users) and objects (e.g., devices, files, records, processes, programs, and domains) in the information system. This requirement is applicable to access control enforcement applications, a category that includes database management systems. If PostgreSQL does not follow applicable policy when approving access, it may be in conflict with networks or other applications in the information system. This may result in users either gaining or being denied access inappropriately and in conflict with applicable policy.

Description

Authentication with a DoD-approved PKI certificate does not necessarily imply authorization to access PostgreSQL. To mitigate the risk of unauthorized access to sensitive information by entities that have been issued certificates by DoD-approved PKIs, all DoD systems, including databases, must be properly configured to implement access control policies. Successful authentication must not automatically give an entity access to an asset or security boundary. Authorization procedures and controls must be implemented to ensure each authenticated entity also has a validated and current authorization. Authorization is the process of determining whether an entity, once authenticated, is permitted to access a specific asset. Information systems use access control policies and enforcement mechanisms to implement this requirement. Access control policies include identity-based policies, role-based policies, and attribute-based policies. Access enforcement mechanisms include access control lists, access control matrices, and cryptography. These policies and mechanisms must be employed by the application to control access between users (or processes acting on behalf of users) and objects (e.g., devices, files, records, processes, programs, and domains) in the information system. This requirement is applicable to access control enforcement applications, a category that includes database management systems. If PostgreSQL does not follow applicable policy when approving access, it may be in conflict with networks or other applications in the information system. This may result in users either gaining or being denied access inappropriately and in conflict with applicable policy.

STIG	Date
PostgreSQL 9.x Security Technical Implementation Guide	2017-12-27

Details

Check Text ( C-72993r1_chk )
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 Text ( C-72993r1_chk )

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.

Fix Text (F-79301r2_fix)
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

Fix Text (F-79301r2_fix)

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