PostgreSQL 14 New Features – Security

Database security refers to the collective measures used to protect and secure a database or database management software from illegitimate use and malicious cyber threats and attacks. Database security procedures are aimed at protecting not just the data inside the database, but across the platform that accesses it from intrusion, misuse of data, and damage.

It is a broad term that includes a multitude of processes, tools, and methodologies that ensure security within a database environment.

The PostgreSQL Global Development Group (PGDG) takes security seriously, allowing the users to place their trust around PostgreSQL. Their approach covers fail-safe configuration options, a secure and robust database server as well as good integration with other security infrastructure software.

PostgreSQL security updates are primarily made available as minor version upgrades. You are always advised to use the latest minor version available, as it will likely also contain other non-security-related fixes. All known security issues are always fixed in the next major release when it comes out.

PostgreSQL 14 simplifies the process of assigning read-only, and write-only capabilities to users on tables, views, and schemas using the predefined roles pg read all data and pg write all data. Additionally, this version changes the default password management and authentication system for new PostgreSQL instances to the standards-compliant SCRAM-SHA-256 password management and authentication system.

SCRAM as the default authentication

In cryptography, the Salted Challenge Response Authentication Mechanism (SCRAM) is a family of modern, password-based challenge-response authentication mechanisms providing authentication of a user to a server.

SCRAM-SHA-256 authentication was introduced in PostgreSQL 10 and has now been made the default in PostgreSQL 14. The previous default MD5 authentication has had some weaknesses that have been exploited in the past. SCRAM is much more powerful, and it allows for easier regulatory compliance for data security.

SCRAM is a defined standard (RFC5802); PostgreSQL implements SCRAM-SHA-256 (RFC7677), with the notable difference in that it uses the SHA-256 hashing function instead of SHA-1

postgres=# SELECT * FROM pg_settings where name='password_encryption';
-[ RECORD 1 ]---+------------------------------------------------
name            | password_encryption
setting         | scram-sha-256
unit            | 
category        | Connections and Authentication / Authentication
short_desc      | Chooses the algorithm for encrypting passwords.
extra_desc      | 
context         | user
vartype         | enum
source          | configuration file
min_val         | 
max_val         | 
enumvals        | {md5,scram-sha-256}
boot_val        | scram-sha-256
reset_val       | scram-sha-256
sourcefile      | /opt/postgres/data/conf.d/0000-tpa.conf
sourceline      | 98
pending_restart | f

Note: Till PG13, the default value (boot_val) for password_encryption was “md5“

Predefined roles

The whole gamut of access control (ACL) in PostgreSQL is likely surrounded by GRANT, REVOKE, ALTER DEFAULT PRIVILEGES, SCHEMA & DATABASE PERMISSIONS.

PostgreSQL 14 simplifies the process of creating READ-only, and WRITE-only access controls to roles on database objects using the predefined roles. Two predefined roles have been added in pg_read_all_data and pg_write_all_data. These roles make the DBA job easy when it comes to granting read-only access for a user to all tables, views, and schemas in the database. This role will have read access by default to any new tables that are created. This role will have read access by default to any new tables that are created. The pg_write_all_data role makes it convenient to create roles with “almost” superuser privileges. So, beware of it!

GRANT pg_read_all_data TO readonly_user;
GRANT pg_write_all_data TO dml_user;

CIS PostgreSQL Benchmark

CIS PostgreSQL Benchmark by the Center for Internet Security, a nonprofit organization that provides publications around standards and best practices for securing technologies systems. Early this year in March, CIS PostgreSQL 13 Benchmarks were published. The CIS PG14 Benchmarks would also be published in the near future.

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 tests are broadly classified into the following:

• PostgreSQL Installation and Patch management
• Operating system Directory and File Permissions
• Overall System and PostgreSQL Logging and Audit Trail using pgAudit extension
• PostgreSQL User management and Authorization
• PostgreSQL security related parameters and configuration
• Secure Data-in-transit using Replication and a few other special considerations

We highly recommend our customers be compliant with these benchmarks. Need help with CIS Benchmarking? Reach out to us for automated deployment and reporting of the CIS PostgreSQL benchmark tests on your databases. The detailed report would enable DBAs to secure the postgres instance and be compliant.

While we are on the topic of Security, I am happy to introduce Our Partner – Ownux.

Ownux is an Information Security Consultation firm specializing in the field of Penetration Testing of every channel which classifies different security areas of interest within an organization. We easily get inclined with your in-house team to work collaboratively and with fewer hassles to kick-start new projects and to bring the best out of the ongoing engagements.