Alvaro, * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > The subquery comparing the OID of pg_class using only a condition on > relname seems wrong; wouldn't it fail or produce wrong results if > somebody creates a table named pg_class in another schema? I think you > should write the comparison like this instead: > classoid = 'pg_catalog.pg_class'::regclass
Thanks, patch attached which does that (qualifying to the same level as the surrounding query for each). I've run it through my tests and will plan to push it tomorrow. Thanks! Stephen
From e294008daa8e909059c94441643157fddf9af9b6 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Tue, 10 May 2016 12:55:11 -0400
Subject: [PATCH] Qualify table usage in dumpTable() and use regclass
All of the other tables used in the query in dumpTable(), which is
collecting column-level ACLs, are qualified, so we should be qualifying
the pg_init_privs and related sub-select against pg_class too.
Also, use ::regclass (or ::pg_catalog.regclass, where appropriate)
instead of using a poorly constructed query to get the OID for various
catalog tables.
Issues identified by Noah and Alvaro, patch by me.
---
src/bin/pg_dump/pg_dump.c | 59 ++++++++++++++++++++++++-----------------------
1 file changed, 30 insertions(+), 29 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 1267afb..1d985e4 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2825,8 +2825,8 @@ getBlobs(Archive *fout)
"%s AS initrlomacl "
"FROM pg_largeobject_metadata l "
"LEFT JOIN pg_init_privs pip ON "
- "(l.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class WHERE relname = 'pg_largeobject')"
+ "(l.oid = pip.objoid "
+ "AND pip.classoid = 'pg_largeobject'::regclass "
"AND pip.objsubid = 0) ",
username_subquery,
acl_subquery->data,
@@ -3569,8 +3569,8 @@ getNamespaces(Archive *fout, int *numNamespaces)
"%s as initrnspacl "
"FROM pg_namespace n "
"LEFT JOIN pg_init_privs pip "
- "ON (n.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class WHERE relname = 'pg_namespace') "
+ "ON (n.oid = pip.objoid "
+ "AND pip.classoid = 'pg_namespace'::regclass "
"AND pip.objsubid = 0) ",
username_subquery,
acl_subquery->data,
@@ -3851,9 +3851,9 @@ getTypes(Archive *fout, int *numTypes)
"t.typname[0] = '_' AND t.typelem != 0 AND "
"(SELECT typarray FROM pg_type te WHERE oid = t.typelem) = t.oid AS isarray "
"FROM pg_type t "
- "LEFT JOIN pg_init_privs pip "
- "ON (t.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class WHERE relname = 'pg_type') "
+ "LEFT JOIN pg_init_privs pip ON "
+ "(t.oid = pip.objoid "
+ "AND pip.classoid = 'pg_type'::regclass "
"AND pip.objsubid = 0) ",
acl_subquery->data,
racl_subquery->data,
@@ -4713,8 +4713,8 @@ getAggregates(Archive *fout, int *numAggs)
"%s AS initraggacl "
"FROM pg_proc p "
"LEFT JOIN pg_init_privs pip ON "
- "(p.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class WHERE relname = 'pg_proc') "
+ "(p.oid = pip.objoid "
+ "AND pip.classoid = 'pg_proc'::regclass "
"AND pip.objsubid = 0) "
"WHERE p.proisagg AND ("
"p.pronamespace != "
@@ -4956,8 +4956,8 @@ getFuncs(Archive *fout, int *numFuncs)
"(%s p.proowner) AS rolname "
"FROM pg_proc p "
"LEFT JOIN pg_init_privs pip ON "
- "(p.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class WHERE relname = 'pg_proc') "
+ "(p.oid = pip.objoid "
+ "AND pip.classoid = 'pg_proc'::regclass "
"AND pip.objsubid = 0) "
"WHERE NOT proisagg "
"AND NOT EXISTS (SELECT 1 FROM pg_depend "
@@ -5246,9 +5246,10 @@ getTables(Archive *fout, int *numTables)
"CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text "
"WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, "
"tc.reloptions AS toast_reloptions, "
- "EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip ON"
- "(c.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AND pip.objsubid = at.attnum)"
+ "EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip ON "
+ "(c.oid = pip.objoid "
+ "AND pip.classoid = 'pg_class'::regclass "
+ "AND pip.objsubid = at.attnum)"
"WHERE at.attrelid = c.oid AND ("
"%s IS NOT NULL "
"OR %s IS NOT NULL "
@@ -5264,9 +5265,9 @@ getTables(Archive *fout, int *numTables)
"d.refclassid = c.tableoid AND d.deptype = 'a') "
"LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) "
"LEFT JOIN pg_init_privs pip ON "
- "(c.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class "
- "WHERE relname = 'pg_class') AND pip.objsubid = 0) "
+ "(c.oid = pip.objoid "
+ "AND pip.classoid = 'pg_class'::regclass "
+ "AND pip.objsubid = 0) "
"WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
@@ -7163,9 +7164,9 @@ getProcLangs(Archive *fout, int *numProcLangs)
"%s AS initrlanacl, "
"(%s l.lanowner) AS lanowner "
"FROM pg_language l "
- "LEFT JOIN pg_init_privs pip "
- "ON (l.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class WHERE relname = 'pg_type') "
+ "LEFT JOIN pg_init_privs pip ON "
+ "(l.oid = pip.objoid "
+ "AND pip.classoid = 'pg_type'::regclass "
"AND pip.objsubid = 0) "
"WHERE l.lanispl "
"ORDER BY l.oid",
@@ -8583,9 +8584,9 @@ getForeignDataWrappers(Archive *fout, int *numForeignDataWrappers)
"ORDER BY option_name"
"), E',\n ') AS fdwoptions "
"FROM pg_foreign_data_wrapper f "
- "LEFT JOIN pg_init_privs pip "
- "ON (f.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class WHERE relname = 'pg_foreign_data_wrapper') "
+ "LEFT JOIN pg_init_privs pip ON "
+ "(f.oid = pip.objoid "
+ "AND pip.classoid = 'pg_foreign_data_wrapper'::regclass "
"AND pip.objsubid = 0) ",
username_subquery,
acl_subquery->data,
@@ -8753,8 +8754,8 @@ getForeignServers(Archive *fout, int *numForeignServers)
"), E',\n ') AS srvoptions "
"FROM pg_foreign_server f "
"LEFT JOIN pg_init_privs pip "
- "ON (f.oid = pip.objoid AND pip.classoid = "
- "(SELECT oid FROM pg_class WHERE relname = 'pg_foreign_server') "
+ "ON (f.oid = pip.objoid "
+ "AND pip.classoid = 'pg_foreign_server'::regclass "
"AND pip.objsubid = 0) ",
username_subquery,
acl_subquery->data,
@@ -14992,10 +14993,10 @@ dumpTable(Archive *fout, TableInfo *tbinfo)
"%s AS initrattacl "
"FROM pg_catalog.pg_attribute at "
"JOIN pg_catalog.pg_class c ON (at.attrelid = c.oid) "
- "LEFT JOIN pg_init_privs pip ON "
- "(pip.classoid = "
- "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AND "
- " at.attrelid = pip.objoid AND at.attnum = pip.objsubid) "
+ "LEFT JOIN pg_catalog.pg_init_privs pip ON "
+ "(at.attrelid = pip.objoid "
+ "AND pip.classoid = 'pg_catalog.pg_class'::pg_catalog.regclass "
+ "AND at.attnum = pip.objsubid) "
"WHERE at.attrelid = '%u' AND "
"NOT at.attisdropped "
"AND ("
--
2.5.0
signature.asc
Description: Digital signature
