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

Commitd2d3547

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 parent836af97 commitd2d3547

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+
Arelationpattern 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,constchar*pattern,intencoding)
13091309
{
13101310
PQExpBufferDatabuf;
1311+
intdotcnt;
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
PQExpBufferDatadbbuf;
13341341
PQExpBufferDatanspbuf;
1342+
intdotcnt;
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
PQExpBufferDatadbbuf;
13701384
PQExpBufferDatanspbuf;
13711385
PQExpBufferDatarelbuf;
1386+
intdotcnt;
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+
PQExpBufferDatadbbuf;
1322+
intdotcnt;
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+
intdotcnt;
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+
intdotcnt;
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+
PQExpBufferDatadbbuf;
1485+
intdotcnt;
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+
intdotcnt;
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 (inti=0;i<PQntuples(res);i++)

0 commit comments

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