{"id":2373,"date":"2021-10-12T09:16:41","date_gmt":"2021-10-12T09:16:41","guid":{"rendered":"https:\/\/test.opensource-db.in\/wp1\/?p=2373"},"modified":"2021-10-12T11:09:32","modified_gmt":"2021-10-12T11:09:32","slug":"postgresql-14-new-features-security","status":"publish","type":"post","link":"https:\/\/test.opensource-db.in\/wp1\/postgresql-14-new-features-security\/","title":{"rendered":"PostgreSQL 14 New Features \u2013 Security"},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

It is a broad term that includes a multitude of processes, tools, and methodologies that ensure security within a database environment.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

SCRAM as the default authentication<\/strong><\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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

postgres=# SELECT * FROM pg_settings where name='password_encryption';\n-[ RECORD 1 ]---+------------------------------------------------\nname            | password_encryption\nsetting         | scram-sha-256\nunit            | \ncategory        | Connections and Authentication \/ Authentication\nshort_desc      | Chooses the algorithm for encrypting passwords.\nextra_desc      | \ncontext         | user\nvartype         | enum\nsource          | configuration file\nmin_val         | \nmax_val         | \nenumvals        | {md5,scram-sha-256}\nboot_val        | scram-sha-256\nreset_val       | scram-sha-256\nsourcefile      | \/opt\/postgres\/data\/conf.d\/0000-tpa.conf\nsourceline      | 98\npending_restart | f<\/code><\/pre>\n\n\n\n
Note: Till PG13, the default value (boot_val) for password_encryption was “md5<\/strong>“<\/p>\n\n\n\n
Predefined roles<\/strong><\/p>\n\n\n\n
The whole gamut of access control (ACL) in PostgreSQL is likely surrounded by GRANT, REVOKE, ALTER DEFAULT PRIVILEGES, SCHEMA & DATABASE PERMISSIONS.<\/p>\n\n\n\n
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<\/strong> and pg_write_all_data<\/strong>. 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!<\/p>\n\n\n\n
GRANT pg_read_all_data TO readonly_user;\nGRANT pg_write_all_data TO dml_user;<\/code><\/pre>\n\n\n\n
CIS PostgreSQL Benchmark<\/strong><\/p>\n\n\n\n
CIS PostgreSQL Benchmark by the Center for Internet Security<\/a>, 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.<\/p>\n\n\n\n
What is a CIS Benchmark?<\/em><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The CIS Benchmark tests are broadly classified into the following:<\/p>\n\n\n\n