From a217491ce3f4fc8e0bba2a743e8c2a2833749505 Mon Sep 17 00:00:00 2001 From: Corey Huinker 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