Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4a66300

Browse files
committed
Allow db.schema.table patterns, but complain about random garbage.
psql, pg_dump, and pg_amcheck share code to process object namepatterns like 'foo*.bar*' to match all tables with names starting in'bar' that are in schemas starting with 'foo'. Before v14, any numberof extra name parts were silently ignored, so a command line '\dfoo.bar.baz.bletch.quux' was interpreted as '\d bletch.quux'. In v14,as a result of commit2c8726c, weinstead treated this as a request for table quux in a schema named'foo.bar.baz.bletch'. That caused problems for people like JustinPryzby who were accustomed to copying strings of the formdb.schema.table from messages generated by PostgreSQL itself and usingthem as arguments to \d.Accordingly, revise things so that if an object name pattern containsmore parts than we're expecting, we throw an error, unless there'sexactly one extra part and it matches the current database name.That way, thisdb.myschema.mytable is accepted as meaning justmyschema.mytable, but otherdb.myschema.mytable is an error, and sois some.random.garbage.myschema.mytable.Mark Dilger, per report from Justin Pryzby and discussion amongvarious people.Discussion:https://www.postgresql.org/message-id/20211013165426.GD27491%40telsasoft.com
1 parent7891a0d commit4a66300

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+
Arelationpattern 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,constchar*pattern,intencoding)
13421342
{
13431343
PQExpBufferDatabuf;
1344+
intdotcnt;
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
PQExpBufferDatadbbuf;
13671374
PQExpBufferDatanspbuf;
1375+
intdotcnt;
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
PQExpBufferDatadbbuf;
14031417
PQExpBufferDatanspbuf;
14041418
PQExpBufferDatarelbuf;
1419+
intdotcnt;
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::Moretests=>76;
9+
use Test::Moretests=>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+
PQExpBufferDatadbbuf;
1365+
intdotcnt;
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+
intdotcnt;
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+
intdotcnt;
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+
PQExpBufferDatadbbuf;
1528+
intdotcnt;
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+
intdotcnt;
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 (inti=0;i<PQntuples(res);i++)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp