> > Unfortunately, pg_stats_ext is also different. The data for that view is > restricted to table owners (or roles that inherit privileges of the table > owner).
Ok, I took the RLS and permissions quals from pg_stats and pg_stats_ext and put them in the corresponding EXISTs tests. The queries could be written a bit more succinctly (ex. we only need to do the RLS checks once) but putting them in each EXISTS clause drives home the point that we're duplicating the filters in pg_stats/pg_stats_ext. So now, we avoid probing attributes for stats that we know were going to be filtered out, likewise for extended statistics objects. The queries don't consult pg_stats_exprs because that's just exposing different stats from the same pg_statistic_ext_data row, and we already know that the row is there or not.
From a217491ce3f4fc8e0bba2a743e8c2a2833749505 Mon Sep 17 00:00:00 2001 From: Corey Huinker <corey.huin...@gmail.com> Date: Thu, 21 Aug 2025 12:30:17 -0400 Subject: [PATCH v2] Have missing-stats query use security barrier views. Previously, the missing-stats query used pg_statistic and pg_statistic_ext_data, which meant that the queries would fail for non-superusers like pg_maintain as reported by Fujii Masao. Because the security barrier views will obscure certain statistics from the user, it is important that each EXISTS() test also apply the same filter in generating the list of attributes and extended stats to avoid false positives. This unfortunately means that we do not know if the columns the user can't see have stats or not, but the alternative is false positives. --- src/bin/scripts/vacuumdb.c | 62 +++++++++++++++++++++++++++----------- 1 file changed, 44 insertions(+), 18 deletions(-) diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 22093e50aa5..2b5e8ac91e8 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -972,36 +972,52 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, " WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n" " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n" " AND NOT a.attisdropped\n" + " AND pg_catalog.has_column_privilege(c.oid, a.attnum, 'select'::pg_catalog.text)\n" + " AND (c.relrowsecurity OPERATOR(pg_catalog.=) false\n" + " OR NOT pg_catalog.row_security_active(c.oid))\n" " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n" - " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n" - " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n" - " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n" - " AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n"); + " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_stats s\n" + " WHERE s.schemaname OPERATOR(pg_catalog.=) ns.nspname\n" + " AND s.tablename OPERATOR(pg_catalog.=) c.relname\n" + " AND s.attname OPERATOR(pg_catalog.=) a.attname\n" + " AND s.inherited OPERATOR(pg_catalog.=) p.inherited))\n"); /* extended stats */ appendPQExpBufferStr(&catalog_query, " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n" + " JOIN pg_catalog.pg_namespace en" + " ON en.oid OPERATOR(pg_catalog.=) e.stxnamespace\n" " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n" + " AND pg_catalog.pg_has_role(c.relowner, 'USAGE'::text)\n" + " AND (c.relrowsecurity OPERATOR(pg_catalog.=) false\n" + " OR NOT pg_catalog.row_security_active(c.oid))\n" " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n" - " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n" - " WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n" - " AND d.stxdinherit OPERATOR(pg_catalog.=) p.inherited))\n"); + " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_stats_ext d\n" + " WHERE d.statistics_schemaname OPERATOR(pg_catalog.=) en.nspname\n" + " AND d.statistics_name OPERATOR(pg_catalog.=) e.stxname\n" + " AND d.inherited OPERATOR(pg_catalog.=) p.inherited))\n"); /* expression indexes */ appendPQExpBufferStr(&catalog_query, " OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n" " JOIN pg_catalog.pg_index i" " ON i.indexrelid OPERATOR(pg_catalog.=) a.attrelid\n" + " JOIN pg_catalog.pg_class ic" + " ON ic.oid OPERATOR(pg_catalog.=) i.indexrelid\n" " WHERE i.indrelid OPERATOR(pg_catalog.=) c.oid\n" " AND i.indkey[a.attnum OPERATOR(pg_catalog.-) 1::pg_catalog.int2]" " OPERATOR(pg_catalog.=) 0::pg_catalog.int2\n" " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n" " AND NOT a.attisdropped\n" + " AND pg_catalog.has_column_privilege(ic.oid, a.attnum, 'select'::pg_catalog.text)\n" + " AND (c.relrowsecurity OPERATOR(pg_catalog.=) false\n" + " OR NOT pg_catalog.row_security_active(c.oid))\n" " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n" - " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n" - " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n" - " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n" - " AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n"); + " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_stats s\n" + " WHERE s.schemaname OPERATOR(pg_catalog.=) ns.nspname\n" + " AND s.tablename OPERATOR(pg_catalog.=) ic.relname\n" + " AND s.attname OPERATOR(pg_catalog.=) a.attname\n" + " AND s.inherited OPERATOR(pg_catalog.=) p.inherited))\n"); /* inheritance and regular stats */ appendPQExpBufferStr(&catalog_query, @@ -1012,25 +1028,35 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n" " AND c.relhassubclass\n" " AND NOT p.inherited\n" + " AND pg_catalog.has_column_privilege(c.oid, a.attnum, 'select'::pg_catalog.text)\n" + " AND (c.relrowsecurity OPERATOR(pg_catalog.=) false\n" + " OR NOT pg_catalog.row_security_active(c.oid))\n" " AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n" " WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n" - " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n" - " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n" - " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n" - " AND s.stainherit))\n"); + " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_stats s\n" + " WHERE s.schemaname OPERATOR(pg_catalog.=) ns.nspname\n" + " AND s.tablename OPERATOR(pg_catalog.=) c.relname\n" + " AND s.attname OPERATOR(pg_catalog.=) a.attname\n" + " AND s.inherited OPERATOR(pg_catalog.=) p.inherited))\n"); /* inheritance and extended stats */ appendPQExpBufferStr(&catalog_query, " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n" + " JOIN pg_catalog.pg_namespace en" + " ON en.oid OPERATOR(pg_catalog.=) e.stxnamespace\n" " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n" " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n" " AND c.relhassubclass\n" " AND NOT p.inherited\n" + " AND pg_catalog.pg_has_role(c.relowner, 'USAGE'::text)\n" + " AND (c.relrowsecurity OPERATOR(pg_catalog.=) false\n" + " OR NOT pg_catalog.row_security_active(c.oid))\n" " AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n" " WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n" - " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n" - " WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n" - " AND d.stxdinherit))\n"); + " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_stats_ext d\n" + " WHERE d.statistics_schemaname OPERATOR(pg_catalog.=) en.nspname\n" + " AND d.statistics_name OPERATOR(pg_catalog.=) e.stxname\n" + " AND d.inherited))\n"); appendPQExpBufferStr(&catalog_query, " )\n"); } base-commit: 12da45742cfd15d9fab151b25400d96a1febcbde -- 2.50.1
