4.3 Ensure excessive administrative privileges are revoked

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 | | {}

See Also

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

Item Details

Category: ACCESS CONTROL

References: 800-53|AC-3

Plugin: Unix

Control ID: 1126ff2b869904f631c370a4b10647a6e069c98dbf061f91be439473d9e02972