Usually, the PostgreSQL Community releases minor patches on the Thursday of the second week of the second month of each quarter. This can vary depending on the nature of the fixes. For example, if the fixes include critical fixes as a postgres community response to security vulnerabilities or bugs that might affect data integrity. The previous minor release happened on February 14, 2019. In this case, the fysnc failure issue was corrected, along with some other enhancements and fixes.
The latest minor release, published on May 9, 2019, has some security fixes and bug fixes for these PostgreSQL Major versions.
Let’s take a look at some of the security fixes in this release.
CVE-2019-10130 (Applicable to PostgreSQL 9.5, 9.6, 10 and 11 versions)
When you ANALYZE a table in PostgreSQL, statistics of all the database objects are stored in pg_statistic . The query planner uses this statistical data is and it may contain some sensitive data, for example, min and max values of a column. Some of the planner’s selectivity estimators apply user-defined operators to values found in pg_statistic. There was a security fix : CVE-2017-7484 in the past, that restricted a leaky user-defined operator from disclosing some of the entries of a data column.
Starting from PostgreSQL 9.5, tables in PostgreSQL not only have SQL-standard privilege system but also row security policies. To keep it short and simple, you can restrict a user so that they can only access specific rows of a table. We call this RLS (Row-Level Security). CVE-2019-10130 is about restricting a user who has SQL permissions to read a column but is forbidden to read some rows due to RLS policy from discovering the restricted rows through a leaky operator. Through this patch, leaky operators to statistical data are only allowed when there is no relevant RLS policy. We shall see more about RLS in our future blog posts.
CVE-2019-10129 (Applicable to PostgreSQL 11 only)
This fixes the situation where a user could access arbitrary back end memory with correctly crafted DDLs. This is only applicable to PostgreSQL 11 version and no other major release has been impacted.
There have been a good number of fixes around partitioning in both PostgreSQL 10 and 11 versions that included a fix to behaviour of an UPDATE or an INSERT on certain scenarios, failure while ALTER INDEX .. ATTACH PARTITION, tuple routing in multi-level partitioned tables that have dropped attributes. etc. .
We should also mention a couple of interesting bugs addressed in this new release.
Bug # 15631 is about the catalog corruption when a temporary table on commit drop is created with an identity column. Due to this issue, PostgreSQL 11.1 would not allow the creation of temporary tables, and you might have observed this error message prior to this release:
# create temporary table foo ( bar int generated by default as identity ) on
# commit drop;
# CREATE TABLE
# psql postgres postgres
# create temporary table a (b varchar);
ERROR: could not open relation with OID 16388
LINE 1: create temporary table a (b varchar);
Another bug that got fixed is Bug # 15734 which is about a Walsender process crashing when executing SHOW ALL using replication protocol.
Some more fixes include:
CREATE VIEW to allow zero-column views.
2. Add missing support for the CREATE TABLE IF NOT EXISTS .. AS EXECUTE .. statement.
3. Fix incompatibility of GIN-index WAL records that were introduced in 11.2, 10.7, 9.6.12, 9.5.16, and 9.4.21 that affected replica servers running these versions reading in changes to GIN indexes from primary servers of older versions.
4. Make pg_verify_checksums verify that the data directory it’s pointed at is of the right PostgreSQL version.
5. Fixes for postgres_fdw where an UPDATE could lead to incorrect results or a crash.
6. Several memory leak fixes as well as fixes to management of dynamic shared memory.
The postgres community have fixed more than just the bug fixes and enhancements we’ve highlighted. We would recommend you to go through the release notes, which you can access by clicking the appropriate release in the beginning of this blog post. Let us know if you have any specific questions you’d like us to address.
We always recommend that you keep your PostgreSQL databases updated to the latest minor versions. Be aware that applying a minor release might need a restart after updating the new binaries.
Here is the sequence of steps you should follow to upgrade to the latest minor versions after thorough testing :
1. Shutdown the PostgreSQL database server
2. Install the updated binaries
3. Restart your PostgreSQL database server
Most of the time, you can choose to update the minor versions in a rolling fashion in a master-slave (replication) setup, because it avoids downtime for both reads and writes simultaneously. For a rolling style update, you could perform the update on one server after another. However, the best method that we’d almost always recommend is – shutdown, update and restart all instances at once.
If you are currently running your databases on PostgreSQL 9.3.x or earlier, we recommend that you to prepare a plan to upgrade your PostgreSQL databases to a supported version ASAP. We’ve previously published some blog posts about the various options for upgrading your PostgreSQL databases to a supported major version, and you can read our post archive here. Please subscribe to our blog posts so that you can see more interesting topics around PostgreSQL.