Using the CIS PostgreSQL Benchmark to Enhance Your Security

Crunchy Data recently announced the publication of the CIS PostgreSQL Benchmark by the Center for Internet Security, a nonprofit organization that provides publications around standards and best practices for securing technologies systems. This CIS PostgreSQL Benchmark builds on earlier work that Crunchy started when it helped to publish the PostgreSQL Security Technical Implementation Guide (PostgreSQL STIG) and provides guidance and steps to help secure your PostgreSQL databases.

What is a CIS Benchmark?

A CIS Benchmark is a set of guidelines and best practices for securely configuring a target system. Authoring a CIS Benchmark is a collaborative process as CIS involves considerable peer reviews and discussion before a major version is published, to ensure there is a general consensus on the best practices for deploying a secure system.

The CIS Benchmark contains a series of compliance recommendations that are designed to test the security of the system. Some of these recommendations are “scored” - if the system meets the requirements of a check, it receives points towards a final benchmark score (scores are from 1-100, with 100 being the best possible score). There are other compliance recommendations available that are not scored but are there for informational purposes and can help guide you towards best practices.

These recommendations can further be divided into pertaining to different configuration profiles. CIS Benchmarks define two different configuration profiles. The first, a Level 1 profile, is considered to be a “base security configuration” which has recommendations that are considered easier to set up and overall lower the potential attack surface of a system. In contrast, Level 2 profiles are designed for environments where security is of the utmost concern.

What’s in the CIS PostgreSQL Benchmark?

The CIS PostgreSQL Benchmark recommendations were developed by testing PostgreSQL 9.5 running on CentOS 6, though these recommendations will also apply to newer versions of PostgreSQL. Similar to the PostgreSQL STIG, the CIS PostgreSQL Benchmark provides recommendations in the following areas:

• Installing PostgreSQL and applying upgrades
• Directory and file permissions
• Connection settings
• Logging and auditing
• User authorization and access
• Replication and backup setup and configuration
• General PostgreSQL runtime settings

At present, the CIS PostgreSQL Benchmark only contains a Level 1 configuration profile, which as described in the Benchmark documentation, is intended to:

• Be practical and prudent;
• Provide a clear security benefit; and
• Not inhibit the utility of the technology beyond acceptable means.

What’s in a Recommendation?

Recommendations are first grouped together by general categories (e.g. “User Access & Authorization”) and then subdivided into their specific parts. Each recommendation is broken up into the following:

Example: Ensuring Excessive DML Privileges Are Revoked

Data manipulation language, (aka “DML,” aka “writes,” aka INSERT/UPDATE/DELETE) is an incredibly powerful part of any database as it allows a user to change data. In a secure system DML access should only be given to users who actually need it to modify data in tables. For instance, if you have a user who’s only supposed to run analytical queries, chances are that user does not need to be able to modify the underlying data.

As part of auditing this recommendation, the CIS PostgreSQL Benchmark first says to take an inventory of all the users in the PostgreSQL cluster along with all of the schema/table combinations within the current database. You can accomplish this by running the following as a database superuser:

\du+ * -- display all users defined in the cluster postgres
\dt+ *.* -- display all schema.tables created in current database

As you go through this list, you may find some users and tables that you may want to remove altogether.

Once you have taken that inventory, you can then check which users have access to which tables, and which permissions they have been granted, i.e. SELECT, INSERT, UPDATE, DELETE. The following query builds out a matrix of user/table permissions:

SELECT t.schemaname, t.tablename, u.usename,
    has_table_privilege(u.usename, t.tablename, 'select') as select,
    has_table_privilege(u.usename, t.tablename, 'insert') as insert,
    has_table_privilege(u.usename, t.tablename, 'update') as update,
    has_table_privilege(u.usename, t.tablename, 'delete') as delete
FROM pg_tables t, pg_user u
WHERE t.schemaname not in ('information_schema','pg_catalog');

Depending on how big your database is, this could be a very long list.

If you want to check on user permissions for a specific table, you can run the following, substituting TABLE_NAME, and $USER_NAME for the schema/table/user names in question:

SELECT t.schemaname, t.tablename, u.usename,
    has_table_privilege(u.usename, t.tablename, 'select') as select,
    has_table_privilege(u.usename, t.tablename, 'insert') as insert,
    has_table_privilege(u.usename, t.tablename, 'update') as update, has_table_privilege(u.usename, t.tablename, 'delete') as delete
FROM pg_tables t, pg_user u
WHERE
    t.schemaname = '$SCHEMA_NAME'
    t.tablename = '$TABLE_NAME';

Based on your results, you may realize that there is a user that has unnecessary DML privileges. The CIS Benchmark’s remediation step for this recommendation is to revoke the extra DML privileges for the user from that table, which can be accomplished with the appropriate substitutions for TABLE_NAME, and $USER_NAME as such

REVOKE INSERT, UPDATE, DELETE ON TABLE $SCHEMA_NAME.$TABLE_NAME FROM $USER_NAME;

Additionally, this recommendation also provides remediation guidance based on CVE-2018-1058, which recommends revoking all creation privileges in the default “public” schema for all users:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

What’s Next?

Try it out! The CIS PostgreSQL Benchmark is freely available to help you secure your PostgreSQL deployments. This is only the beginning of our work on the CIS PostgreSQL benchmark: the Crunchy Data team is continuing to collaborate with CIS to further refine and improve upon the Benchmark.

If you’re interested how we have worked on applying security validations at scale, take a look at our open source PostgreSQL STIG Compliance Validator, which uses the InSpec tool to automated many of the same checks that the CIS PostgreSQL Benchmark handles.