Skip to content

Commit 4a66300

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 7891a0d commit 4a66300

File tree

11 files changed

+1799
-219
lines changed

11 files changed

+1799
-219
lines changed

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

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

35973597
<para>
3598-
A pattern that contains a dot (<literal>.</literal>) is interpreted as a schema
3598+
A relation pattern that contains a dot (<literal>.</literal>) is interpreted as a schema
35993599
name pattern followed by an object name pattern. For example,
36003600
<literal>\dt foo*.*bar*</literal> displays all tables whose table name
36013601
includes <literal>bar</literal> that are in schemas whose schema name
36023602
starts with <literal>foo</literal>. When no dot appears, then the pattern
36033603
matches only objects that are visible in the current schema search path.
36043604
Again, a dot within double quotes loses its special meaning and is matched
3605-
literally.
3605+
literally. A relation pattern that contains two dots (<literal>.</literal>)
3606+
is interpreted as a database name followed by a schema name pattern followed
3607+
by an object name pattern. The database name portion will not be treated as
3608+
a pattern and must match the name of the currently connected database, else
3609+
an error will be raised.
3610+
</para>
3611+
3612+
<para>
3613+
A schema pattern that contains a dot (<literal>.</literal>) is interpreted
3614+
as a database name followed by a schema name pattern. For example,
3615+
<literal>\dn mydb.*foo*</literal> displays all schemas whose schema name
3616+
includes <literal>foo</literal>. The database name portion will not be
3617+
treated as a pattern and must match the name of the currently connected
3618+
database, else an error will be raised.
36063619
</para>
36073620

36083621
<para>

‎src/bin/pg_amcheck/pg_amcheck.c

Lines changed: 24 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1341,10 +1341,17 @@ static void
13411341
append_database_pattern(PatternInfoArray *pia, const char *pattern, int encoding)
13421342
{
13431343
PQExpBufferData buf;
1344+
int dotcnt;
13441345
PatternInfo *info = extend_pattern_info_array(pia);
13451346

13461347
initPQExpBuffer(&buf);
1347-
patternToSQLRegex(encoding, NULL, NULL, &buf, pattern, false);
1348+
patternToSQLRegex(encoding, NULL, NULL, &buf, pattern, false, false,
1349+
&dotcnt);
1350+
if (dotcnt > 0)
1351+
{
1352+
pg_log_error("improper qualified name (too many dotted names): %s", pattern);
1353+
exit(2);
1354+
}
13481355
info->pattern = pattern;
13491356
info->db_regex = pstrdup(buf.data);
13501357

@@ -1365,12 +1372,19 @@ append_schema_pattern(PatternInfoArray *pia, const char *pattern, int encoding)
13651372
{
13661373
PQExpBufferData dbbuf;
13671374
PQExpBufferData nspbuf;
1375+
int dotcnt;
13681376
PatternInfo *info = extend_pattern_info_array(pia);
13691377

13701378
initPQExpBuffer(&dbbuf);
13711379
initPQExpBuffer(&nspbuf);
13721380

1373-
patternToSQLRegex(encoding, NULL, &dbbuf, &nspbuf, pattern, false);
1381+
patternToSQLRegex(encoding, NULL, &dbbuf, &nspbuf, pattern, false, false,
1382+
&dotcnt);
1383+
if (dotcnt > 1)
1384+
{
1385+
pg_log_error("improper qualified name (too many dotted names): %s", pattern);
1386+
exit(2);
1387+
}
13741388
info->pattern = pattern;
13751389
if (dbbuf.data[0])
13761390
{
@@ -1402,13 +1416,20 @@ append_relation_pattern_helper(PatternInfoArray *pia, const char *pattern,
14021416
PQExpBufferData dbbuf;
14031417
PQExpBufferData nspbuf;
14041418
PQExpBufferData relbuf;
1419+
int dotcnt;
14051420
PatternInfo *info = extend_pattern_info_array(pia);
14061421

14071422
initPQExpBuffer(&dbbuf);
14081423
initPQExpBuffer(&nspbuf);
14091424
initPQExpBuffer(&relbuf);
14101425

1411-
patternToSQLRegex(encoding, &dbbuf, &nspbuf, &relbuf, pattern, false);
1426+
patternToSQLRegex(encoding, &dbbuf, &nspbuf, &relbuf, pattern, false,
1427+
false, &dotcnt);
1428+
if (dotcnt > 2)
1429+
{
1430+
pg_log_error("improper relation name (too many dotted names): %s", pattern);
1431+
exit(2);
1432+
}
14121433
info->pattern = pattern;
14131434
if (dbbuf.data[0])
14141435
{

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

Lines changed: 95 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66

77
use PostgresNode;
88
use TestLib;
9-
use Test::More tests => 76;
9+
use Test::More tests => 100;
1010

1111
# Test set-up
1212
my ($node, $port);
@@ -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: 60 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -164,6 +164,9 @@ static void expand_table_name_patterns(Archive *fout,
164164
SimpleStringList *patterns,
165165
SimpleOidList *oids,
166166
bool strict_names);
167+
static void prohibit_crossdb_refs(PGconn *conn, const char *dbname,
168+
const char *pattern);
169+
167170
static NamespaceInfo *findNamespace(Oid nsoid);
168171
static void dumpTableData(Archive *fout, const TableDataInfo *tdinfo);
169172
static void refreshMatViewData(Archive *fout, const TableDataInfo *tdinfo);
@@ -1358,10 +1361,21 @@ expand_schema_name_patterns(Archive *fout,
13581361

13591362
for (cell = patterns->head; cell; cell = cell->next)
13601363
{
1364+
PQExpBufferData dbbuf;
1365+
int dotcnt;
1366+
13611367
appendPQExpBufferStr(query,
13621368
"SELECT oid FROM pg_catalog.pg_namespace n\n");
1369+
initPQExpBuffer(&dbbuf);
13631370
processSQLNamePattern(GetConnection(fout), query, cell->val, false,
1364-
false, NULL, "n.nspname", NULL, NULL);
1371+
false, NULL, "n.nspname", NULL, NULL, &dbbuf,
1372+
&dotcnt);
1373+
if (dotcnt > 1)
1374+
fatal("improper qualified name (too many dotted names): %s",
1375+
cell->val);
1376+
else if (dotcnt == 1)
1377+
prohibit_crossdb_refs(GetConnection(fout), dbbuf.data, cell->val);
1378+
termPQExpBuffer(&dbbuf);
13651379

13661380
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
13671381
if (strict_names && PQntuples(res) == 0)
@@ -1405,10 +1419,16 @@ expand_extension_name_patterns(Archive *fout,
14051419
*/
14061420
for (cell = patterns->head; cell; cell = cell->next)
14071421
{
1422+
int dotcnt;
1423+
14081424
appendPQExpBufferStr(query,
14091425
"SELECT oid FROM pg_catalog.pg_extension e\n");
14101426
processSQLNamePattern(GetConnection(fout), query, cell->val, false,
1411-
false, NULL, "e.extname", NULL, NULL);
1427+
false, NULL, "e.extname", NULL, NULL, NULL,
1428+
&dotcnt);
1429+
if (dotcnt > 0)
1430+
fatal("improper qualified name (too many dotted names): %s",
1431+
cell->val);
14121432

14131433
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
14141434
if (strict_names && PQntuples(res) == 0)
@@ -1452,10 +1472,16 @@ expand_foreign_server_name_patterns(Archive *fout,
14521472

14531473
for (cell = patterns->head; cell; cell = cell->next)
14541474
{
1475+
int dotcnt;
1476+
14551477
appendPQExpBufferStr(query,
14561478
"SELECT oid FROM pg_catalog.pg_foreign_server s\n");
14571479
processSQLNamePattern(GetConnection(fout), query, cell->val, false,
1458-
false, NULL, "s.srvname", NULL, NULL);
1480+
false, NULL, "s.srvname", NULL, NULL, NULL,
1481+
&dotcnt);
1482+
if (dotcnt > 0)
1483+
fatal("improper qualified name (too many dotted names): %s",
1484+
cell->val);
14591485

14601486
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
14611487
if (PQntuples(res) == 0)
@@ -1498,6 +1524,9 @@ expand_table_name_patterns(Archive *fout,
14981524

14991525
for (cell = patterns->head; cell; cell = cell->next)
15001526
{
1527+
PQExpBufferData dbbuf;
1528+
int dotcnt;
1529+
15011530
/*
15021531
* Query must remain ABSOLUTELY devoid of unqualified names. This
15031532
* would be unnecessary given a pg_table_is_visible() variant taking a
@@ -1513,9 +1542,17 @@ expand_table_name_patterns(Archive *fout,
15131542
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
15141543
RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
15151544
RELKIND_PARTITIONED_TABLE);
1545+
initPQExpBuffer(&dbbuf);
15161546
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
15171547
false, "n.nspname", "c.relname", NULL,
1518-
"pg_catalog.pg_table_is_visible(c.oid)");
1548+
"pg_catalog.pg_table_is_visible(c.oid)", &dbbuf,
1549+
&dotcnt);
1550+
if (dotcnt > 2)
1551+
fatal("improper relation name (too many dotted names): %s",
1552+
cell->val);
1553+
else if (dotcnt == 2)
1554+
prohibit_crossdb_refs(GetConnection(fout), dbbuf.data, cell->val);
1555+
termPQExpBuffer(&dbbuf);
15191556

15201557
ExecuteSqlStatement(fout, "RESET search_path");
15211558
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
@@ -1536,6 +1573,25 @@ expand_table_name_patterns(Archive *fout,
15361573
destroyPQExpBuffer(query);
15371574
}
15381575

1576+
/*
1577+
* Verifies that the connected database name matches the given database name,
1578+
* and if not, dies with an error about the given pattern.
1579+
*
1580+
* The 'dbname' argument should be a literal name parsed from 'pattern'.
1581+
*/
1582+
static void
1583+
prohibit_crossdb_refs(PGconn *conn, const char *dbname, const char *pattern)
1584+
{
1585+
const char *db;
1586+
1587+
db = PQdb(conn);
1588+
if (db == NULL)
1589+
fatal("You are currently not connected to a database.");
1590+
1591+
if (strcmp(db, dbname) != 0)
1592+
fatal("cross-database references are not implemented: %s", pattern);
1593+
}
1594+
15391595
/*
15401596
* checkExtensionMembership
15411597
* 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
@@ -1438,10 +1438,21 @@ expand_dbname_patterns(PGconn *conn,
14381438

14391439
for (SimpleStringListCell *cell = patterns->head; cell; cell = cell->next)
14401440
{
1441+
int dotcnt;
1442+
14411443
appendPQExpBufferStr(query,
14421444
"SELECT datname FROM pg_catalog.pg_database n\n");
14431445
processSQLNamePattern(conn, query, cell->val, false,
1444-
false, NULL, "datname", NULL, NULL);
1446+
false, NULL, "datname", NULL, NULL, NULL,
1447+
&dotcnt);
1448+
1449+
if (dotcnt > 0)
1450+
{
1451+
pg_log_error("improper qualified name (too many dotted names): %s",
1452+
cell->val);
1453+
PQfinish(conn);
1454+
exit_nicely(1);
1455+
}
14451456

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

0 commit comments

Comments
 (0)