From: za-arthur (@za-arthur) <noreply+za-arthur@github.com>
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Subject: [reorg/pg_repack] PR #496: Harden client code queries and generated SQL against search_path
Date: Fri, 20 Nov 2026 10:22:17 +0000
Message-ID: <gh-reorg-pg_repack-496@github.com> (raw)
Follow-up to #493. The client code connects with `search_path = pg_catalog, pg_temp, public` and some of the SQL it sends still resolved names through that path.
**Changes**:
1. Use exact `oid` comparisons for relation lookups in the client. Queries compared `oid` columns with `regclass` values, such as `relid = $1::regclass` and `classid = 'pg_class'::regclass.pg_catalog` has no `(oid, regclass)` operator, so Postgres picked the operator by implicit cast through the search_path.
2. Fix `ORDER BY` generation for non-default operator classes. `repack_get_order_by()` emitted a bare operator name (`USING <`), which was resolved again through the search_path. It now emits `USING OPERATOR(schema.op)`.
3. Quote the schema name in the index cleanup statement. The cleanup `DROP INDEX CONCURRENTLY` now builds the schema identifier with `PQescapeIdentifier()`.
inline patch 1: bin/pg_repack.c, lib/repack.c, regress/expected/get_order_by.out and 1 more (+136/-28 lines) download inline-1.patchdiff --git a/bin/pg_repack.c b/bin/pg_repack.cindex 8cc540a..7774699 100644--- a/bin/pg_repack.c+++ b/bin/pg_repack.c@@ -662,11 +662,11 @@ is_requested_relation_exists(char *errbuf, size_t errsize){
") AS given_t(r,kind) WHERE"
/* regular --table relation or inherited --parent-table */
" NOT EXISTS("
- " SELECT FROM repack.tables WHERE relid=pg_catalog.to_regclass(given_t.r))"+ " SELECT FROM repack.tables WHERE relid=pg_catalog.to_regclass(given_t.r)::oid)"
/* declarative partitioned --parent-table */
" AND NOT EXISTS("
" SELECT FROM pg_catalog.pg_class c"
- " WHERE c.oid=pg_catalog.to_regclass(given_t.r) AND c.relkind = given_t.kind AND given_t.kind = 'p')"+ " WHERE c.oid=pg_catalog.to_regclass(given_t.r)::oid AND c.relkind = given_t.kind AND given_t.kind = 'p')"
);
/* double check the parameters array is sane */
@@ -848,7 +848,7 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
for (cell = table_list.head; cell; cell = cell->next)
{
/* Construct table name placeholders to be used by PQexecParams */
- appendStringInfo(&sql, "relid = $%d::regclass", iparam + 1);+ appendStringInfo(&sql, "relid = $%d::regclass::oid", iparam + 1);
params[iparam++] = cell->val;
if (cell->next)
appendStringInfoString(&sql, " OR ");
@@ -867,7 +867,7 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
{
/* Construct table name placeholders to be used by PQexecParams */
appendStringInfo(&sql,
- "relid = ANY(repack.get_table_and_inheritors($%d::regclass))",+ "relid = ANY(repack.get_table_and_inheritors($%d::regclass)::oid[])",
iparam + 1);
params[iparam++] = cell->val;
if (cell->next)
@@ -898,11 +898,11 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
if (exclude_extension_list.head)
{
appendStringInfoString(&sql, " AND t.relid NOT IN"
- " (SELECT d.objid::regclass"+ " (SELECT d.objid"
" FROM pg_catalog.pg_depend d"
" JOIN pg_catalog.pg_extension e"
" ON d.refobjid = e.oid"
- " WHERE d.classid = 'pg_class'::regclass AND (");+ " WHERE d.classid = 'pg_class'::regclass::oid AND (");
/* List all excluded extensions */
for (cell = exclude_extension_list.head; cell; cell = cell->next)
@@ -2047,7 +2047,7 @@ lock_exclusive(PGconn *conn, const char *relid, const char *lock_query, bool sta
{
elog(WARNING, "terminating conflicted backends");
cancel_query =
- "SELECT pg_catalog.pg_terminate_backend(pid) FROM pg_locks"+ "SELECT pg_catalog.pg_terminate_backend(pid) FROM pg_catalog.pg_locks"
" WHERE locktype = 'relation'"
" AND relation = $1 AND pid <> pg_catalog.pg_backend_pid()";
}
@@ -2055,7 +2055,7 @@ lock_exclusive(PGconn *conn, const char *relid, const char *lock_query, bool sta
{
elog(WARNING, "canceling conflicted backends");
cancel_query =
- "SELECT pg_catalog.pg_cancel_backend(pid) FROM pg_locks"+ "SELECT pg_catalog.pg_cancel_backend(pid) FROM pg_catalog.pg_locks"
" WHERE locktype = 'relation'"
" AND relation = $1 AND pid <> pg_catalog.pg_backend_pid()";
}
@@ -2191,16 +2191,25 @@ repack_cleanup_index(bool fatal, void *userdata)
StringInfoData sql, sql_drop;
PGresult *index_details = (PGresult *) userdata;
char *schema_name;
+ char *quoted_schema;
int num;
schema_name = getstr(index_details, 0, 5);
num = PQntuples(index_details);
+ quoted_schema = PQescapeIdentifier(connection, schema_name,+ strlen(schema_name));+ if (quoted_schema == NULL)+ elog(ERROR, "Unable to quote schema name \"%s\": %s",+ schema_name, PQerrorMessage(connection));+
initStringInfo(&sql);
initStringInfo(&sql_drop);
appendStringInfoString(&sql, "DROP INDEX CONCURRENTLY IF EXISTS ");
- appendStringInfo(&sql, "\"%s\".", schema_name);+ appendStringInfo(&sql, "%s.", quoted_schema);++ PQfreemem(quoted_schema);
for (int i = 0; i < num; i++)
{
@@ -2236,7 +2245,7 @@ repack_table_indexes(PGresult *index_details)
params[1] = utoa(table, buffer[1]);
params[2] = tablespace;
schema_name = getstr(index_details, 0, 5);
- /* table_name is schema-qualified */+ /* table_name is quoted and schema-qualified (repack.oid2text) */
table_name = getstr(index_details, 0, 4);
/* Keep track of which of the table's indexes we have successfully
@@ -2281,6 +2290,9 @@ repack_table_indexes(PGresult *index_details)
}
if (PQntuples(res) > 0)
{
+ char *quoted_schema = PQescapeIdentifier(connection,+ schema_name,+ strlen(schema_name));
ereport(WARNING,
(errcode(E_PG_COMMAND),
errmsg("Cannot create index \"%s\".\"index_%u\", "
@@ -2288,9 +2300,12 @@ repack_table_indexes(PGresult *index_details)
errdetail("An invalid index may have been left behind"
" by a previous pg_repack on the table"
" which was interrupted. Please use DROP "
- "INDEX \"%s\".\"index_%u\""+ "INDEX %s.\"index_%u\""
" to remove this index and try again.",
- schema_name, index)));+ quoted_schema ? quoted_schema : schema_name,+ index)));+ if (quoted_schema)+ PQfreemem(quoted_schema);
continue;
}
@@ -2424,18 +2439,18 @@ repack_all_indexes(char *errbuf, size_t errsize)
" FROM pg_catalog.pg_index idx"
" JOIN pg_catalog.pg_class i ON i.oid = idx.indexrelid"
" JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace"
- " WHERE idx.indexrelid = $1::regclass ORDER BY indisvalid DESC, i.relname, n.nspname");+ " WHERE idx.indexrelid = $1::regclass::oid ORDER BY indisvalid DESC, i.relname, n.nspname");
cell = r_index.head;
}
else if (table_list.head || parent_table_list.head)
{
appendStringInfoString(&sql,
- "SELECT repack.oid2text(i.oid), idx.indexrelid, idx.indisvalid, idx.indrelid, $1::text, n.nspname"+ "SELECT repack.oid2text(i.oid), idx.indexrelid, idx.indisvalid, idx.indrelid, repack.oid2text(idx.indrelid), n.nspname"
" FROM pg_catalog.pg_index idx"
" JOIN pg_catalog.pg_class i ON i.oid = idx.indexrelid"
" JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace"
- " WHERE idx.indrelid = $1::regclass ORDER BY indisvalid DESC, i.relname, n.nspname");+ " WHERE idx.indrelid = $1::regclass::oid ORDER BY indisvalid DESC, i.relname, n.nspname");
for (cell = parent_table_list.head; cell; cell = cell->next)
{
@@ -2455,7 +2470,7 @@ repack_all_indexes(char *errbuf, size_t errsize)
res = execute_elevel("SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname)"
" FROM pg_catalog.pg_class c"
" JOIN pg_catalog.pg_namespace n on n.oid = c.relnamespace"
- " WHERE c.oid = ANY (repack.get_table_and_inheritors($1::regclass))"+ " WHERE c.oid = ANY (repack.get_table_and_inheritors($1::regclass)::oid[])"
" AND c.relkind = 'r'"
" ORDER BY n.nspname, c.relname",
1, params, DEBUG2);
diff --git a/lib/repack.c b/lib/repack.cindex 1b8f5e5..4fe679e 100644--- a/lib/repack.c+++ b/lib/repack.c@@ -40,6 +40,7 @@
#include "catalog/pg_inherits_fn.h"
#endif
#include "catalog/pg_namespace.h"
+#include "catalog/pg_operator.h"
#include "catalog/pg_type.h"
#include "commands/tablecmds.h"
#include "commands/trigger.h"
@@ -707,6 +708,26 @@ parse_indexdef_col(char *token, char **desc, char **nulls, char **collate)
}
}
+/*+ * Append the operator as OPERATOR(schema.name), so that it does not depend on+ * the search_path of whoever executes the generated ORDER BY.+ */+static void+append_qualified_opname(StringInfo str, Oid oprid)+{+ HeapTuple tuple;+ Form_pg_operator opr;++ tuple = SearchSysCache1(OPEROID, ObjectIdGetDatum(oprid));+ if (!HeapTupleIsValid(tuple))+ elog(ERROR, "cache lookup failed for operator %u", oprid);+ opr = (Form_pg_operator) GETSTRUCT(tuple);+ appendStringInfo(str, "OPERATOR(%s.%s)",+ quote_identifier(get_namespace_name(opr->oprnamespace)),+ NameStr(opr->oprname));+ ReleaseSysCache(tuple);+}+
/**
* @fn Datum repack_get_order_by(PG_FUNCTION_ARGS)
* @brief Get key definition of the index.
@@ -753,14 +774,18 @@ repack_get_order_by(PG_FUNCTION_ARGS)
appendStringInfoString(&str, token);
if (colcollate)
appendStringInfo(&str, " %s", colcollate);
- if (coldesc)+ /*+ * DESC cannot be combined with USING; for a DESC column with an+ * operator class we emit USING <greater-than operator> instead.+ */+ if (coldesc && !opcname)
appendStringInfo(&str, " %s", coldesc);
if (opcname)
{
/* lookup default operator name from operator class */
Oid oprid;
- int16 strategy = BTLessStrategyNumber;+ int16 strategy = coldesc ? BTGreaterStrategyNumber : BTLessStrategyNumber;
Oid opcintype;
Oid opfamily;
@@ -790,7 +815,8 @@ repack_get_order_by(PG_FUNCTION_ARGS)
strategy, opcintype, opcintype, opfamily);
opcname[-1] = '\0';
- appendStringInfo(&str, " USING %s", get_opname(oprid));+ appendStringInfoString(&str, " USING ");+ append_qualified_opname(&str, oprid);
}
if (colnulls)
appendStringInfo(&str, " %s", colnulls);
diff --git a/regress/expected/get_order_by.out b/regress/expected/get_order_by.outindex 1336a7b..74d54e7 100644--- a/regress/expected/get_order_by.out+++ b/regress/expected/get_order_by.out@@ -14,9 +14,9 @@ INFO: repacking table "public.issue3_1"
CREATE TABLE issue3_2 (col1 int NOT NULL, col2 text NOT NULL);
CREATE UNIQUE INDEX issue3_2_idx ON issue3_2 (col1 DESC, col2 text_pattern_ops);
SELECT repack.get_order_by('issue3_2_idx'::regclass::oid, 'issue3_2'::regclass::oid);
- get_order_by ----------------------------- col1 DESC, col2 USING ~<~+ get_order_by +------------------------------------------------+ col1 DESC, col2 USING OPERATOR(pg_catalog.~<~)
(1 row)
\! pg_repack --dbname=contrib_regression --table=issue3_2
@@ -34,9 +34,9 @@ INFO: repacking table "public.issue3_3"
CREATE TABLE issue3_4 (col1 int NOT NULL, col2 text NOT NULL);
CREATE UNIQUE INDEX issue3_4_idx ON issue3_4 (col1 NULLS FIRST, col2 text_pattern_ops DESC NULLS LAST);
SELECT repack.get_order_by('issue3_4_idx'::regclass::oid, 'issue3_4'::regclass::oid);
- get_order_by ---------------------------------------------------- col1 NULLS FIRST, col2 DESC USING ~<~ NULLS LAST+ get_order_by +------------------------------------------------------------------+ col1 NULLS FIRST, col2 USING OPERATOR(pg_catalog.~>~) NULLS LAST
(1 row)
\! pg_repack --dbname=contrib_regression --table=issue3_4
@@ -69,11 +69,55 @@ CREATE OPERATOR CLASS public.opclass_int4_ops FOR TYPE int4 USING btree AS
CREATE TABLE opclass_tbl (col1 int NOT NULL, col2 int NOT NULL);
CREATE UNIQUE INDEX opclass_tbl_idx ON opclass_tbl (col1 public.opclass_int4_ops, col2 DESC);
SELECT repack.get_order_by('opclass_tbl_idx'::regclass::oid, 'opclass_tbl'::regclass::oid);
- get_order_by --------------------------- col1 USING <, col2 DESC+ get_order_by +----------------------------------------------+ col1 USING OPERATOR(pg_catalog.<), col2 DESC
(1 row)
CLUSTER opclass_tbl USING opclass_tbl_idx;
\! pg_repack --dbname=contrib_regression --table=opclass_tbl
INFO: repacking table "public.opclass_tbl"
+--+-- DESC with a non-default operator class must not produce "DESC USING"+--+CREATE TABLE desc_opclass_tbl (col1 int NOT NULL, col2 text NOT NULL);+CREATE UNIQUE INDEX desc_opclass_tbl_idx ON desc_opclass_tbl (col1, col2 text_pattern_ops DESC NULLS LAST);+INSERT INTO desc_opclass_tbl VALUES (2, 'a'), (1, 'b'), (1, 'c');+CLUSTER desc_opclass_tbl USING desc_opclass_tbl_idx;+SELECT repack.get_order_by('desc_opclass_tbl_idx'::regclass::oid, 'desc_opclass_tbl'::regclass::oid);+ get_order_by +------------------------------------------------------+ col1, col2 USING OPERATOR(pg_catalog.~>~) NULLS LAST+(1 row)++\! pg_repack --dbname=contrib_regression --table=desc_opclass_tbl+INFO: repacking table "public.desc_opclass_tbl"+SELECT col1, col2 FROM desc_opclass_tbl ORDER BY ctid;+ col1 | col2 +------+------+ 1 | c+ 1 | b+ 2 | a+(3 rows)++-- Quoted schema name in generated SQL+CREATE SCHEMA "q""s";+CREATE TABLE "q""s".qt (id int PRIMARY KEY, v text) WITH (fillfactor = 70, toast.autovacuum_enabled = false);+CREATE INDEX qt_idx ON "q""s".qt (v);+INSERT INTO "q""s".qt SELECT i, 'v' || i FROM generate_series(1, 5) i;+\! pg_repack --dbname=contrib_regression --table='"q""s".qt'+INFO: repacking table ""q""s".qt"+\! pg_repack --dbname=contrib_regression --only-indexes --table='"q""s".qt'+INFO: repacking indexes of ""q""s".qt"+INFO: repacking index ""q""s".qt_idx"+INFO: repacking index ""q""s".qt_pkey"+SELECT (SELECT count(*) FROM pg_class WHERE relnamespace = '"q""s"'::regnamespace AND relname LIKE 'index\_%') AS leftover,+ c.reloptions, t.reloptions AS toast_reloptions, (SELECT count(*) FROM "q""s".qt) AS n+FROM pg_class c JOIN pg_class t ON t.oid = c.reltoastrelid WHERE c.oid = '"q""s".qt'::regclass;+ leftover | reloptions | toast_reloptions | n +----------+-----------------+----------------------------+---+ 0 | {fillfactor=70} | {autovacuum_enabled=false} | 5+(1 row)++DROP SCHEMA "q""s" CASCADE;+NOTICE: drop cascades to table "q""s".qtdiff --git a/regress/sql/get_order_by.sql b/regress/sql/get_order_by.sqlindex 307315e..12e823a 100644--- a/regress/sql/get_order_by.sql+++ b/regress/sql/get_order_by.sql@@ -45,3 +45,26 @@ CREATE UNIQUE INDEX opclass_tbl_idx ON opclass_tbl (col1 public.opclass_int4_ops
SELECT repack.get_order_by('opclass_tbl_idx'::regclass::oid, 'opclass_tbl'::regclass::oid);
CLUSTER opclass_tbl USING opclass_tbl_idx;
\! pg_repack --dbname=contrib_regression --table=opclass_tbl
++--+-- DESC with a non-default operator class must not produce "DESC USING"+--+CREATE TABLE desc_opclass_tbl (col1 int NOT NULL, col2 text NOT NULL);+CREATE UNIQUE INDEX desc_opclass_tbl_idx ON desc_opclass_tbl (col1, col2 text_pattern_ops DESC NULLS LAST);+INSERT INTO desc_opclass_tbl VALUES (2, 'a'), (1, 'b'), (1, 'c');+CLUSTER desc_opclass_tbl USING desc_opclass_tbl_idx;+SELECT repack.get_order_by('desc_opclass_tbl_idx'::regclass::oid, 'desc_opclass_tbl'::regclass::oid);+\! pg_repack --dbname=contrib_regression --table=desc_opclass_tbl+SELECT col1, col2 FROM desc_opclass_tbl ORDER BY ctid;++-- Quoted schema name in generated SQL+CREATE SCHEMA "q""s";+CREATE TABLE "q""s".qt (id int PRIMARY KEY, v text) WITH (fillfactor = 70, toast.autovacuum_enabled = false);+CREATE INDEX qt_idx ON "q""s".qt (v);+INSERT INTO "q""s".qt SELECT i, 'v' || i FROM generate_series(1, 5) i;+\! pg_repack --dbname=contrib_regression --table='"q""s".qt'+\! pg_repack --dbname=contrib_regression --only-indexes --table='"q""s".qt'+SELECT (SELECT count(*) FROM pg_class WHERE relnamespace = '"q""s"'::regnamespace AND relname LIKE 'index\_%') AS leftover,+ c.reloptions, t.reloptions AS toast_reloptions, (SELECT count(*) FROM "q""s".qt) AS n+FROM pg_class c JOIN pg_class t ON t.oid = c.reltoastrelid WHERE c.oid = '"q""s".qt'::regclass;+DROP SCHEMA "q""s" CASCADE;