Skip to content

Commit d2d3547

Browse files
robertmhaasrobertmhaas
committed
Allow db.schema.table patterns, but complain about random garbage.
psql, pg_dump, and pg_amcheck share code to process object name patterns like 'foo*.bar*' to match all tables with names starting in 'bar' that are in schemas starting with 'foo'. Before v14, any number of extra name parts were silently ignored, so a command line '\d foo.bar.baz.bletch.quux' was interpreted as '\d bletch.quux'. In v14, as a result of commit 2c8726c, we instead treated this as a request for table quux in a schema named 'foo.bar.baz.bletch'. That caused problems for people like Justin Pryzby who were accustomed to copying strings of the form db.schema.table from messages generated by PostgreSQL itself and using them as arguments to \d. Accordingly, revise things so that if an object name pattern contains more parts than we're expecting, we throw an error, unless there's exactly one extra part and it matches the current database name. That way, thisdb.myschema.mytable is accepted as meaning just myschema.mytable, but otherdb.myschema.mytable is an error, and so is some.random.garbage.myschema.mytable. Mark Dilger, per report from Justin Pryzby and discussion among various people. Discussion: https://www.postgresql.org/message-id/20211013165426.GD27491%40telsasoft.com
1 parent 836af97 commit d2d3547

File tree

11 files changed

+1796
-217
lines changed

11 files changed

+1796
-217
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3641,14 +3641,27 @@ select 1\; select 2\; select 3;
36413641
</para>
36423642

36433643
<para>
3644-
A pattern that contains a dot (<literal>.</literal>) is interpreted as a schema
3644+
A relation pattern that contains a dot (<literal>.</literal>) is interpreted as a schema
36453645
name pattern followed by an object name pattern. For example,
36463646
<literal>\dt foo*.*bar*</literal> displays all tables whose table name
36473647
includes <literal>bar</literal> that are in schemas whose schema name
36483648
starts with <literal>foo</literal>. When no dot appears, then the pattern
36493649
matches only objects that are visible in the current schema search path.
36503650
Again, a dot within double quotes loses its special meaning and is matched
3651-
literally.
3651+
literally. A relation pattern that contains two dots (<literal>.</literal>)
3652+
is interpreted as a database name followed by a schema name pattern followed
3653+
by an object name pattern. The database name portion will not be treated as
3654+
a pattern and must match the name of the currently connected database, else
3655+
an error will be raised.
3656+
</para>
3657+
3658+
<para>
3659+
A schema pattern that contains a dot (<literal>.</literal>) is interpreted
3660+
as a database name followed by a schema name pattern. For example,
3661+
<literal>\dn mydb.*foo*</literal> displays all schemas whose schema name
3662+
includes <literal>foo</literal>. The database name portion will not be
3663+
treated as a pattern and must match the name of the currently connected
3664+
database, else an error will be raised.
36523665
</para>
36533666

36543667
<para>

‎src/bin/pg_amcheck/pg_amcheck.c

Lines changed: 24 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1308,10 +1308,17 @@ static void
13081308
append_database_pattern(PatternInfoArray *pia, const char *pattern, int encoding)
13091309
{
13101310
PQExpBufferData buf;
1311+
int dotcnt;
13111312
PatternInfo *info = extend_pattern_info_array(pia);
13121313

13131314
initPQExpBuffer(&buf);
1314-
patternToSQLRegex(encoding, NULL, NULL, &buf, pattern, false);
1315+
patternToSQLRegex(encoding, NULL, NULL, &buf, pattern, false, false,
1316+
&dotcnt);
1317+
if (dotcnt > 0)
1318+
{
1319+
pg_log_error("improper qualified name (too many dotted names): %s", pattern);
1320+
exit(2);
1321+
}
13151322
info->pattern = pattern;
13161323
info->db_regex = pstrdup(buf.data);
13171324

@@ -1332,12 +1339,19 @@ append_schema_pattern(PatternInfoArray *pia, const char *pattern, int encoding)
13321339
{
13331340
PQExpBufferData dbbuf;
13341341
PQExpBufferData nspbuf;
1342+
int dotcnt;
13351343
PatternInfo *info = extend_pattern_info_array(pia);
13361344

13371345
initPQExpBuffer(&dbbuf);
13381346
initPQExpBuffer(&nspbuf);
13391347

1340-
patternToSQLRegex(encoding, NULL, &dbbuf, &nspbuf, pattern, false);
1348+
patternToSQLRegex(encoding, NULL, &dbbuf, &nspbuf, pattern, false, false,
1349+
&dotcnt);
1350+
if (dotcnt > 1)
1351+
{
1352+
pg_log_error("improper qualified name (too many dotted names): %s", pattern);
1353+
exit(2);
1354+
}
13411355
info->pattern = pattern;
13421356
if (dbbuf.data[0])
13431357
{
@@ -1369,13 +1383,20 @@ append_relation_pattern_helper(PatternInfoArray *pia, const char *pattern,
13691383
PQExpBufferData dbbuf;
13701384
PQExpBufferData nspbuf;
13711385
PQExpBufferData relbuf;
1386+
int dotcnt;
13721387
PatternInfo *info = extend_pattern_info_array(pia);
13731388

13741389
initPQExpBuffer(&dbbuf);
13751390
initPQExpBuffer(&nspbuf);
13761391
initPQExpBuffer(&relbuf);
13771392

1378-
patternToSQLRegex(encoding, &dbbuf, &nspbuf, &relbuf, pattern, false);
1393+
patternToSQLRegex(encoding, &dbbuf, &nspbuf, &relbuf, pattern, false,
1394+
false, &dotcnt);
1395+
if (dotcnt > 2)
1396+
{
1397+
pg_log_error("improper relation name (too many dotted names): %s", pattern);
1398+
exit(2);
1399+
}
13791400
info->pattern = pattern;
13801401
if (dbbuf.data[0])
13811402
{

‎src/bin/pg_amcheck/t/002_nonesuch.pl

Lines changed: 94 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -147,6 +147,100 @@
147147
[qr/pg_amcheck: error: no heap tables to check matching "\."/],
148148
'checking table pattern "."');
149149

150+
# Check that a multipart database name is rejected
151+
$node->command_checks_all(
152+
[ 'pg_amcheck', '-d', 'localhost.postgres' ],
153+
2,
154+
[qr/^$/],
155+
[
156+
qr/pg_amcheck: error: improper qualified name \(too many dotted names\): localhost\.postgres/
157+
],
158+
'multipart database patterns are rejected'
159+
);
160+
161+
# Check that a three-part schema name is rejected
162+
$node->command_checks_all(
163+
[ 'pg_amcheck', '-s', 'localhost.postgres.pg_catalog' ],
164+
2,
165+
[qr/^$/],
166+
[
167+
qr/pg_amcheck: error: improper qualified name \(too many dotted names\): localhost\.postgres\.pg_catalog/
168+
],
169+
'three part schema patterns are rejected'
170+
);
171+
172+
# Check that a four-part table name is rejected
173+
$node->command_checks_all(
174+
[ 'pg_amcheck', '-t', 'localhost.postgres.pg_catalog.pg_class' ],
175+
2,
176+
[qr/^$/],
177+
[
178+
qr/pg_amcheck: error: improper relation name \(too many dotted names\): localhost\.postgres\.pg_catalog\.pg_class/
179+
],
180+
'four part table patterns are rejected'
181+
);
182+
183+
# Check that too many dotted names still draws an error under --no-strict-names
184+
# That flag means that it is ok for the object to be missing, not that it is ok
185+
# for the object name to be ungrammatical
186+
$node->command_checks_all(
187+
[ 'pg_amcheck', '--no-strict-names', '-t', 'this.is.a.really.long.dotted.string' ],
188+
2,
189+
[qr/^$/],
190+
[
191+
qr/pg_amcheck: error: improper relation name \(too many dotted names\): this\.is\.a\.really\.long\.dotted\.string/
192+
],
193+
'ungrammatical table names still draw errors under --no-strict-names'
194+
);
195+
$node->command_checks_all(
196+
[ 'pg_amcheck', '--no-strict-names', '-s', 'postgres.long.dotted.string' ],
197+
2,
198+
[qr/^$/],
199+
[
200+
qr/pg_amcheck: error: improper qualified name \(too many dotted names\): postgres\.long\.dotted\.string/
201+
],
202+
'ungrammatical schema names still draw errors under --no-strict-names'
203+
);
204+
$node->command_checks_all(
205+
[ 'pg_amcheck', '--no-strict-names', '-d', 'postgres.long.dotted.string' ],
206+
2,
207+
[qr/^$/],
208+
[
209+
qr/pg_amcheck: error: improper qualified name \(too many dotted names\): postgres\.long\.dotted\.string/
210+
],
211+
'ungrammatical database names still draw errors under --no-strict-names'
212+
);
213+
214+
# Likewise for exclusion patterns
215+
$node->command_checks_all(
216+
[ 'pg_amcheck', '--no-strict-names', '-T', 'a.b.c.d' ],
217+
2,
218+
[qr/^$/],
219+
[
220+
qr/pg_amcheck: error: improper relation name \(too many dotted names\): a\.b\.c\.d/
221+
],
222+
'ungrammatical table exclusions still draw errors under --no-strict-names'
223+
);
224+
$node->command_checks_all(
225+
[ 'pg_amcheck', '--no-strict-names', '-S', 'a.b.c' ],
226+
2,
227+
[qr/^$/],
228+
[
229+
qr/pg_amcheck: error: improper qualified name \(too many dotted names\): a\.b\.c/
230+
],
231+
'ungrammatical schema exclusions still draw errors under --no-strict-names'
232+
);
233+
$node->command_checks_all(
234+
[ 'pg_amcheck', '--no-strict-names', '-D', 'a.b' ],
235+
2,
236+
[qr/^$/],
237+
[
238+
qr/pg_amcheck: error: improper qualified name \(too many dotted names\): a\.b/
239+
],
240+
'ungrammatical database exclusions still draw errors under --no-strict-names'
241+
);
242+
243+
150244
#########################################
151245
# Test checking non-existent databases, schemas, tables, and indexes
152246

@@ -165,9 +259,7 @@
165259
'-d', 'no*such*database',
166260
'-r', 'none.none',
167261
'-r', 'none.none.none',
168-
'-r', 'this.is.a.really.long.dotted.string',
169262
'-r', 'postgres.none.none',
170-
'-r', 'postgres.long.dotted.string',
171263
'-r', 'postgres.pg_catalog.none',
172264
'-r', 'postgres.none.pg_class',
173265
'-t', 'postgres.pg_catalog.pg_class', # This exists
@@ -186,15 +278,12 @@
186278
qr/pg_amcheck: warning: no connectable databases to check matching "no\*such\*database"/,
187279
qr/pg_amcheck: warning: no relations to check matching "none\.none"/,
188280
qr/pg_amcheck: warning: no connectable databases to check matching "none\.none\.none"/,
189-
qr/pg_amcheck: warning: no connectable databases to check matching "this\.is\.a\.really\.long\.dotted\.string"/,
190281
qr/pg_amcheck: warning: no relations to check matching "postgres\.none\.none"/,
191-
qr/pg_amcheck: warning: no relations to check matching "postgres\.long\.dotted\.string"/,
192282
qr/pg_amcheck: warning: no relations to check matching "postgres\.pg_catalog\.none"/,
193283
qr/pg_amcheck: warning: no relations to check matching "postgres\.none\.pg_class"/,
194284
qr/pg_amcheck: warning: no connectable databases to check matching "no_such_database"/,
195285
qr/pg_amcheck: warning: no connectable databases to check matching "no\*such\*database"/,
196286
qr/pg_amcheck: warning: no connectable databases to check matching "none\.none\.none"/,
197-
qr/pg_amcheck: warning: no connectable databases to check matching "this\.is\.a\.really\.long\.dotted\.string"/,
198287
],
199288
'many unmatched patterns and one matched pattern under --no-strict-names'
200289
);

‎src/bin/pg_dump/pg_dump.c

Lines changed: 61 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -178,6 +178,9 @@ static void expand_table_name_patterns(Archive *fout,
178178
SimpleStringList *patterns,
179179
SimpleOidList *oids,
180180
bool strict_names);
181+
static void prohibit_crossdb_refs(PGconn *conn, const char *dbname,
182+
const char *pattern);
183+
181184
static NamespaceInfo *findNamespace(Oid nsoid);
182185
static void dumpTableData(Archive *fout, const TableDataInfo *tdinfo);
183186
static void refreshMatViewData(Archive *fout, const TableDataInfo *tdinfo);
@@ -1315,10 +1318,21 @@ expand_schema_name_patterns(Archive *fout,
13151318

13161319
for (cell = patterns->head; cell; cell = cell->next)
13171320
{
1321+
PQExpBufferData dbbuf;
1322+
int dotcnt;
1323+
13181324
appendPQExpBufferStr(query,
13191325
"SELECT oid FROM pg_catalog.pg_namespace n\n");
1326+
initPQExpBuffer(&dbbuf);
13201327
processSQLNamePattern(GetConnection(fout), query, cell->val, false,
1321-
false, NULL, "n.nspname", NULL, NULL);
1328+
false, NULL, "n.nspname", NULL, NULL, &dbbuf,
1329+
&dotcnt);
1330+
if (dotcnt > 1)
1331+
pg_fatal("improper qualified name (too many dotted names): %s",
1332+
cell->val);
1333+
else if (dotcnt == 1)
1334+
prohibit_crossdb_refs(GetConnection(fout), dbbuf.data, cell->val);
1335+
termPQExpBuffer(&dbbuf);
13221336

13231337
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
13241338
if (strict_names && PQntuples(res) == 0)
@@ -1362,10 +1376,16 @@ expand_extension_name_patterns(Archive *fout,
13621376
*/
13631377
for (cell = patterns->head; cell; cell = cell->next)
13641378
{
1379+
int dotcnt;
1380+
13651381
appendPQExpBufferStr(query,
13661382
"SELECT oid FROM pg_catalog.pg_extension e\n");
13671383
processSQLNamePattern(GetConnection(fout), query, cell->val, false,
1368-
false, NULL, "e.extname", NULL, NULL);
1384+
false, NULL, "e.extname", NULL, NULL, NULL,
1385+
&dotcnt);
1386+
if (dotcnt > 0)
1387+
pg_fatal("improper qualified name (too many dotted names): %s",
1388+
cell->val);
13691389

13701390
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
13711391
if (strict_names && PQntuples(res) == 0)
@@ -1409,10 +1429,16 @@ expand_foreign_server_name_patterns(Archive *fout,
14091429

14101430
for (cell = patterns->head; cell; cell = cell->next)
14111431
{
1432+
int dotcnt;
1433+
14121434
appendPQExpBufferStr(query,
14131435
"SELECT oid FROM pg_catalog.pg_foreign_server s\n");
14141436
processSQLNamePattern(GetConnection(fout), query, cell->val, false,
1415-
false, NULL, "s.srvname", NULL, NULL);
1437+
false, NULL, "s.srvname", NULL, NULL, NULL,
1438+
&dotcnt);
1439+
if (dotcnt > 0)
1440+
pg_fatal("improper qualified name (too many dotted names): %s",
1441+
cell->val);
14161442

14171443
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
14181444
if (PQntuples(res) == 0)
@@ -1455,6 +1481,9 @@ expand_table_name_patterns(Archive *fout,
14551481

14561482
for (cell = patterns->head; cell; cell = cell->next)
14571483
{
1484+
PQExpBufferData dbbuf;
1485+
int dotcnt;
1486+
14581487
/*
14591488
* Query must remain ABSOLUTELY devoid of unqualified names. This
14601489
* would be unnecessary given a pg_table_is_visible() variant taking a
@@ -1470,9 +1499,17 @@ expand_table_name_patterns(Archive *fout,
14701499
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
14711500
RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
14721501
RELKIND_PARTITIONED_TABLE);
1502+
initPQExpBuffer(&dbbuf);
14731503
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
14741504
false, "n.nspname", "c.relname", NULL,
1475-
"pg_catalog.pg_table_is_visible(c.oid)");
1505+
"pg_catalog.pg_table_is_visible(c.oid)", &dbbuf,
1506+
&dotcnt);
1507+
if (dotcnt > 2)
1508+
pg_fatal("improper relation name (too many dotted names): %s",
1509+
cell->val);
1510+
else if (dotcnt == 2)
1511+
prohibit_crossdb_refs(GetConnection(fout), dbbuf.data, cell->val);
1512+
termPQExpBuffer(&dbbuf);
14761513

14771514
ExecuteSqlStatement(fout, "RESET search_path");
14781515
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
@@ -1493,6 +1530,26 @@ expand_table_name_patterns(Archive *fout,
14931530
destroyPQExpBuffer(query);
14941531
}
14951532

1533+
/*
1534+
* Verifies that the connected database name matches the given database name,
1535+
* and if not, dies with an error about the given pattern.
1536+
*
1537+
* The 'dbname' argument should be a literal name parsed from 'pattern'.
1538+
*/
1539+
static void
1540+
prohibit_crossdb_refs(PGconn *conn, const char *dbname, const char *pattern)
1541+
{
1542+
const char *db;
1543+
1544+
db = PQdb(conn);
1545+
if (db == NULL)
1546+
pg_fatal("You are currently not connected to a database.");
1547+
1548+
if (strcmp(db, dbname) != 0)
1549+
pg_fatal("cross-database references are not implemented: %s",
1550+
pattern);
1551+
}
1552+
14961553
/*
14971554
* checkExtensionMembership
14981555
* Determine whether object is an extension member, and if so,

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1269,10 +1269,21 @@ expand_dbname_patterns(PGconn *conn,
12691269

12701270
for (SimpleStringListCell *cell = patterns->head; cell; cell = cell->next)
12711271
{
1272+
int dotcnt;
1273+
12721274
appendPQExpBufferStr(query,
12731275
"SELECT datname FROM pg_catalog.pg_database n\n");
12741276
processSQLNamePattern(conn, query, cell->val, false,
1275-
false, NULL, "datname", NULL, NULL);
1277+
false, NULL, "datname", NULL, NULL, NULL,
1278+
&dotcnt);
1279+
1280+
if (dotcnt > 0)
1281+
{
1282+
pg_log_error("improper qualified name (too many dotted names): %s",
1283+
cell->val);
1284+
PQfinish(conn);
1285+
exit_nicely(1);
1286+
}
12761287

12771288
res = executeQuery(conn, query->data);
12781289
for (int i = 0; i < PQntuples(res); i++)

0 commit comments

Comments
 (0)