Detections
Analytics

4.2 Ensure excessive administrative privileges are revoked

Warning! Audit Deprecated

This audit has been deprecated and will be removed in a future update.

View Next Audit Version

Information

With respect to PostgreSQL administrative SQL commands, only superusers should have elevated privileges. PostgreSQL regular, or application, users should not possess the ability to create roles, create new databases, manage replication, or perform any other action deemed privileged. Typically, regular users should only be granted the minimal set of privileges commensurate with managing the application:

 DDL (create table, create view, create index, etc.)

 DML (select, insert, update, delete)

 Further, it has become best practice to create separate roles for DDL and DML. Given an application called 'payroll', one would create the following users:

 payroll_owner

 payroll_user

 Any DDL privileges would be granted to the payroll_owner account only, while DML privileges would be given to the payroll_user account only. This prevents accidental creation/altering/dropping of database objects by application code that run as the payroll_user account.

 Rationale:

 By not restricting global administrative commands to superusers only, regular users granted excessive privileges may execute administrative commands with unintended and undesirable results.

 NOTE: Nessus has provided the target output to assist in reviewing the benchmark to ensure target compliance.

Solution

If any regular or application users have been granted excessive administrative rights, those privileges should be removed immediately via the PostgreSQL ALTER ROLE SQL command. Using the same example above, the following SQL statements revoke all unnecessary elevated administrative privileges from the regular user appuser:

 $ whoami
 postgres
 $ psql -c 'ALTER ROLE appuser NOSUPERUSER;'
 ALTER ROLE
 $ psql -c 'ALTER ROLE appuser NOCREATEROLE;'
 ALTER ROLE
 $ psql -c 'ALTER ROLE appuser NOCREATEDB;'
 ALTER ROLE
 $ psql -c 'ALTER ROLE appuser NOREPLICATION;'
 ALTER ROLE
 $ psql -c 'ALTER ROLE appuser NOBYPASSRLS;'
 ALTER ROLE
 $ psql -c 'ALTER ROLE appuser NOINHERIT;'
 ALTER ROLE

 Verify the appuser now passes your check by having no defined Attributes:

 $ whoami
 postgres
 $ psql -c '\du appuser'
 List of roles
 Role name | Attributes | Member of
 ----------+------------+-----------
 appuser | | {}

 References:

 https://www.postgresql.org/docs/12/static/sql-revoke.html

 https://www.postgresql.org/docs/12/static/sql-createrole.html

 https://www.postgresql.org/docs/12/static/sql-alterrole.html

See Also

https://workbench.cisecurity.org/files/2536

Item Details

References: CSCv6|5.1, CSCv7|5.1

Plugin: PostgreSQLDB

Control ID: 4ea2bd1481bc7015313d22acc696888cb8d8868825c7eb712a39f0ce34cd6e47