pg.ddx.io reorg/pg_repack GitHub issues and pull requests
help / color / mirror / Atom feed[reorg/pg_repack] PR #496: Harden client code queries and generated SQL against search_path
3+ messages / 1 participants
[nested] [flat]
* [reorg/pg_repack] PR #496: Harden client code queries and generated SQL against search_path
@ 2026-11-20 10:22 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
0 siblings, 0 replies; 3+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-20 10:22 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
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.patch
diff --git a/bin/pg_repack.c b/bin/pg_repack.c
index 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.c
index 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.out
index 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".qt
diff --git a/regress/sql/get_order_by.sql b/regress/sql/get_order_by.sql
index 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;
^ permalink raw [nested|flat] 3+ messages in thread
* Re: [reorg/pg_repack] PR #496: Harden client code queries and generated SQL against search_path
@ 2026-11-20 10:22 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
1 sibling, 0 replies; 3+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-20 10:22 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@za-arthur requested a review.
^ permalink raw [nested|flat] 3+ messages in thread
* Re: [reorg/pg_repack] PR #496: Harden client code queries and generated SQL against search_path
@ 2026-11-20 10:22 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
1 sibling, 0 replies; 3+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-20 10:22 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@za-arthur requested a review.
^ permalink raw [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-11-20 10:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-20 10:22 [reorg/pg_repack] PR #496: Harden client code queries and generated SQL against search_path "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-20 10:22 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-20 10:22 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox