pg_sift 3.2.2: a security fix (with row-level security on a table, the row estimates of its partitions and inheritance children could reveal information about the rows a policy hides) and a fix for roles that the aggregate pushdown refused. Library only: install the new files and restart the server; the extension SQL does not change (it stays 3.2), so there is no ALTER EXTENSION step and no REINDEX.

Highlights

  • Security fix: row estimates of a partition or an inheritance child under row-level security. With row-level security on a table, the planner could estimate the rows of a partition or an inheritance child of it from the statistics of the sift index. Those statistics count the rows the policy hides, so the row counts shown by EXPLAIN, and the plan chosen, for a role under the policy could reveal information about the hidden rows (how many of them hold a given term). The estimate is the planner’s default now, as it already was for a table without partitions and as the documentation says (a role under a policy gets no estimate from the index). No data was modified, and the query results were not affected: the rows a policy hides were never returned. The refusal of sift.score() on such a table names the table under the policy (it said that a sift index is needed on every partition).
  • Fixed: permission denied for table from an aggregate that the normal plan answers. A role with SELECT on some columns of a table, or on a view and not on its table, got the error at run time when the plan was Custom Scan (SiftAgg), while sift.enable_agg = off answered. The scan checked table-level SELECT of the invoking role; it now leaves the privileges to PostgreSQL, which checks the columns, and the view owner’s rights, before the scan starts. Row-level security is still refused by the scan, and a view over a table with it keeps the normal plan.

Who is affected and what to do

  • Security fix (partitions and inheritance children under a policy): PostgreSQL 17 and 18 servers with a table that has ENABLE ROW LEVEL SECURITY and is partitioned (or has inheritance children) with a sift index on its partitions or children, read through the parent by a role that is not its owner and has no BYPASSRLS. Affected releases: 3.0.0 to 3.2.1 (3.2.0 and 3.2.1 tested; 3.0 and 3.1 by reading the code, which has the same check). Tables without partitions, tables without row-level security and servers that do not use it are not affected. Nothing is needed before upgrading beyond installing the new library (or not using row-level security with partitioned or inherited tables); no REINDEX, no change of the extension SQL.
  • Roles with column privileges or a view: a role that read an aggregate through column privileges or a view got the error above (the table-level check was in 3.2.0 and 3.2.1); a workaround until the update is SET sift.enable_agg = off for that role (ALTER ROLE ... SET sift.enable_agg = off).

Known issue of 3.2.0, 3.2.1 and 3.2.2: a role cannot drop anything

The event trigger function sift.table_paths_drop() is not SECURITY DEFINER in the 3.2 extension SQL, so in a database where pg_sift is installed, a DROP by a role that is neither a superuser nor the owner of the extension fails with permission denied for schema sift (any table, index or schema, also one without pg_sift). Nothing is lost; the statement fails. The SQL of the extension does not change in this release, so 3.2.2 does not fix it; ALTER EXTENSION pg_sift UPDATE to 3.3 does. Until then, run this once in each database that has the extension, as a superuser (and again after restoring a database from a dump, which creates the extension from the 3.2 script):

ALTER FUNCTION sift.table_paths_drop() SECURITY DEFINER SET search_path = pg_catalog, pg_temp;

Compatibility

  • PostgreSQL 17 and 18; PostgreSQL 19 is experimental. A drop-in replacement of 3.2.1: extension SQL 3.2, no change of the BM25, sift_col or sift_table formats. Install the new files (a library swap) and restart the server; going back to 3.2.1 needs nothing. docs/UPGRADING.md has the steps.

Packages

x86-64 packages for PostgreSQL 18 are attached below (SHA256SUMS covers every asset); other versions build from source with make USE_PGXS=1. The packages depend on liblz4 and libzstd.

  • Debian 13, Ubuntu 24.04 (PGDG postgresql-18): sudo apt install ./postgresql-18-pg-sift_3.2.2-1_amd64.deb
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresql18-server): sudo dnf install ./pg_sift_18-3.2.2-1.el9.x86_64.rpm
  • Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 3.2.2 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.2.2 release: no change to the storage format, the SQL interface or the shared core.

  • No change: the fixes of this release are in the PostgreSQL planner and the aggregate pushdown, which are not in this extension. No speed-up or slow-down is claimed.
  • Needs SQLite 3.38 or newer; Linux x86-64, glibc 2.34 or newer. Built on Debian 12, loaded into the SQLite of Debian 12, Ubuntu 24.04 and AlmaLinux 10 before release.
  • Storage format 2: tables of 3.2.1 open as they are, nothing to rebuild.

Download sift-sqlite-3.2.2-linux-x86_64.tar.gz (below, covered by SHA256SUMS with the packages), unpack it and .load ./sift from the sqlite3 shell (sha256sum --ignore-missing -c SHA256SUMS). Documentation: sqlite/README.md. Every change: sqlite/CHANGELOG.md.

MariaDB extension

pg_sift for MariaDB 3.2.2 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.2.2 release: no change to the index format, the SQL files or the shared core.

  • No change: the fixes of this release are in the PostgreSQL planner and the aggregate pushdown, which are not in this extension. No speed-up or slow-down is claimed.
  • One library per MariaDB series, because it calls the server’s own symbols and refuses to load into another series: 10.11, 11.4, 11.8, 12.1 and 13.0; Linux x86-64. Each was built in the stock mariadb:<series> image against that server’s headers and loaded into a server of the same image before release. BUILD-INFO in the tarball lists the server version and the glibc each library needs.
  • Index format 2: an index of 3.2.1 opens as it is, nothing to rebuild.

Download sift-mariadb-3.2.2-linux-x86_64.tar.gz (below, covered by SHA256SUMS with the packages), unpack it, copy lib/<series>/libsift_udf.so of your server’s series (SELECT VERSION()) into its plugin_dir, then SOURCE sql/functions.sql once per server and sql/sift.sql once per database (sha256sum --ignore-missing -c SHA256SUMS). Documentation: mariadb/README.md. Every change: mariadb/CHANGELOG.md.