Postgres Security Checklist from the Center for Internet Security

The Center for Internet Security (CIS) releases security benchmarks to cover a wide variety of infrastructure used in modern applications, including databases, operating systems, cloud services, containerized services, and even networking. Since 2016 Crunchy Data has collaborated with CIS to provide this security resource for those deploying Postgres. The output of this collaboration is a checklist for folks to follow and improve the security posture of Postgres deployments.

The PostgreSQL CIS Benchmark™ for PostgreSQL 17 was just recently released.

The Center for Internet Security

The Center for Internet Security (CIS) is a nonprofit organization that collaborates with government and commercial entities to develop best practices for securing IT systems and data. CIS Benchmarks are community driven and help provide configuration recommendations in the form of security checklists. CIS allows public contributions, reviews, and an open discussion forum on the benchmarks to make sure they meet broader community standards.

The CIS Benchmark for Postgres is a free, community supported, security checklist for Postgres.

Getting started with the Postgres benchmark

The CIS Benchmark for Postgres is a freely available pdf for non-commercial use with recommendations alongside Postgres configurations. The pdf is 200+ pages of descriptions, rational, and sample code to verify Postgres configurations.

In addition to manual verification, to standardize on this benchmark, teams incorporate these settings into their infrastructure deployment tools. Using infrastructure-as-code tools with the benchmarks ensure deployments across an organization meet these security specifications.

For commercial use of CIS Benchmarks, CIS has membership and tools to automatically run the benchmarks.

What is in the CIS Postgres benchmark security checklist?

The benchmark covers a variety of topics for Postgres deployment and configurations, including:

• Postgres install and file permission settings
• Recommended settings for logs
• User access, role creation, passwords, and authorization
• Guidance for using key Postgres extensions like pg_audit, set_user, pg_crypto, and pgBackRest

The document is very hands on, in many cases, CIS provides specific scripts to do the security check. For example, this will look for PGPASSWORD stored environment variable, which is something to avoid:

# grep PGPASSWORD --no-messages /home/*/.{bashrc,profile,bash_profile} 
# grep PGPASSWORD --no-messages /root/.{bashrc,profile,bash_profile} 
# grep PGPASSWORD --no-messages /etc/environment

There are also several statements and queries to help with role and user validation. This SQL query creates a role tree that is pretty neat. It creates a view that shows all roles with login access, superuser configuration, and more:

CREATE 
OR REPLACE VIEW roletree AS WITH RECURSIVE roltree AS (
  SELECT 
    u.rolname AS rolname, 
    u.oid AS roloid, 
    u.rolcanlogin, 
    u.rolsuper, 
    '{}' :: name[] AS rolparents, 
    NULL :: oid AS parent_roloid, 
    NULL :: name AS parent_rolname 
  FROM 
    pg_catalog.pg_authid u 
    LEFT JOIN pg_catalog.pg_auth_members m on u.oid = m.member 
    LEFT JOIN pg_catalog.pg_authid g on m.roleid = g.oid 
  WHERE 
    g.oid IS NULL 
  UNION ALL 
  SELECT 
    u.rolname AS rolname, 
    u.oid AS roloid, 
    u.rolcanlogin, 
    u.rolsuper, 
    t.rolparents || g.rolname AS rolparents, 
    g.oid AS parent_roloid, 
    g.rolname AS parent_rolname 
  FROM 
    pg_catalog.pg_authid u 
    JOIN pg_catalog.pg_auth_members m on u.oid = m.member 
    JOIN pg_catalog.pg_authid g on m.roleid = g.oid 
    JOIN roltree t on t.roloid = g.oid
) 
SELECT 
  r.rolname, 
  r.roloid, 
  r.rolcanlogin, 
  r.rolsuper, 
  r.rolparents 
FROM 
  roltree r 
ORDER BY 
  1;

Updating the benchmark for new Postgres versions

Crunchy Data helps update the benchmark with every major Postgres version. Are new features added that should be in the benchmark? Or features to be wary of?

In this last release a couple notable changes were made:

• Addition of a recommendation for passwordcheck
• Addition of a recommendation for password complexity
• Revisions of the Logging, Monitoring, and Auditing section

Final notes

The CIS benchmark is a fantastic resource for anyone working with security around Postgres. If you need an even deeper security resource, we also work with the United States Department of Defense on a Postgres Security Technical Implementation Guide STIG.

Need help with Postgres security? Contact our team.