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

Commitfbb1d7d

Browse files
committed
Allow CREATE/ALTER DATABASE to manipulate datistemplate and datallowconn.
Historically these database properties could be manipulated only bymanually updating pg_database, which is error-prone and only possible forsuperusers. But there seems no good reason not to allow database owners toset them for their databases, so invent CREATE/ALTER DATABASE options to dothat. Adjust a couple of places that were doing it the hard way to use thecommands instead.Vik Fearing, reviewed by Pavel Stehule
1 parent15c82ef commitfbb1d7d

File tree

7 files changed

+135
-30
lines changed

7 files changed

+135
-30
lines changed

‎contrib/pg_upgrade/pg_upgrade.c

Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -540,9 +540,8 @@ set_frozenxids(void)
540540
*/
541541
if (strcmp(datallowconn,"f")==0)
542542
PQclear(executeQueryOrDie(conn_template1,
543-
"UPDATE pg_catalog.pg_database "
544-
"SETdatallowconn = true "
545-
"WHERE datname = '%s'",datname));
543+
"ALTER DATABASE %s ALLOW_CONNECTIONS = true",
544+
quote_identifier(datname)));
546545

547546
conn=connectToServer(&new_cluster,datname);
548547

@@ -558,9 +557,8 @@ set_frozenxids(void)
558557
/* Reset datallowconn flag */
559558
if (strcmp(datallowconn,"f")==0)
560559
PQclear(executeQueryOrDie(conn_template1,
561-
"UPDATE pg_catalog.pg_database "
562-
"SETdatallowconn = false "
563-
"WHERE datname = '%s'",datname));
560+
"ALTER DATABASE %s ALLOW_CONNECTIONS = false",
561+
quote_identifier(datname)));
564562
}
565563

566564
PQclear(dbres);

‎doc/src/sgml/ref/alter_database.sgml

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,8 @@ ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <rep
2525

2626
<phrase>where <replaceable class="PARAMETER">option</replaceable> can be:</phrase>
2727

28+
IS_TEMPLATE <replaceable class="PARAMETER">istemplate</replaceable>
29+
ALLOW_CONNECTIONS <replaceable class="PARAMETER">allowconn</replaceable>
2830
CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
2931

3032
ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
@@ -107,6 +109,26 @@ ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET ALL
107109
</varlistentry>
108110

109111
<varlistentry>
112+
<term><replaceable class="parameter">istemplate</replaceable></term>
113+
<listitem>
114+
<para>
115+
If true, then this database can be cloned by any user with CREATEDB
116+
privileges; if false, then only superusers or the owner of the
117+
database can clone it.
118+
</para>
119+
</listitem>
120+
</varlistentry>
121+
122+
<varlistentry>
123+
<term><replaceable class="parameter">allowconn</replaceable></term>
124+
<listitem>
125+
<para>
126+
If false then no one can connect to this database.
127+
</para>
128+
</listitem>
129+
</varlistentry>
130+
131+
<varlistentry>
110132
<term><replaceable class="parameter">connlimit</replaceable></term>
111133
<listitem>
112134
<para>

‎doc/src/sgml/ref/create_database.sgml

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,8 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable>
2828
[ LC_COLLATE [=] <replaceable class="parameter">lc_collate</replaceable> ]
2929
[ LC_CTYPE [=] <replaceable class="parameter">lc_ctype</replaceable> ]
3030
[ TABLESPACE [=] <replaceable class="parameter">tablespace_name</replaceable> ]
31+
[ IS_TEMPLATE [=] <replaceable class="parameter">istemplate</replaceable> ]
32+
[ ALLOW_CONNECTIONS [=] <replaceable class="parameter">allowconn</replaceable> ]
3133
[ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ] ]
3234
</synopsis>
3335
</refsynopsisdiv>
@@ -148,6 +150,28 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable>
148150
</varlistentry>
149151

150152
<varlistentry>
153+
<term><replaceable class="parameter">istemplate</replaceable></term>
154+
<listitem>
155+
<para>
156+
If true, then this database can be cloned by any user with CREATEDB
157+
privileges; if false (the default), then only superusers or the owner
158+
of the database can clone it.
159+
</para>
160+
</listitem>
161+
</varlistentry>
162+
163+
<varlistentry>
164+
<term><replaceable class="parameter">allowconn</replaceable></term>
165+
<listitem>
166+
<para>
167+
If false then no one can connect to this database. The default is
168+
true, allowing connections (except as restricted by other mechanisms,
169+
such as <literal>GRANT</>/<literal>REVOKE CONNECT</>).
170+
</para>
171+
</listitem>
172+
</varlistentry>
173+
174+
<varlistentry>
151175
<term><replaceable class="parameter">connlimit</replaceable></term>
152176
<listitem>
153177
<para>

‎src/backend/commands/dbcommands.c

Lines changed: 77 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -123,6 +123,8 @@ createdb(const CreatedbStmt *stmt)
123123
DefElem*dencoding=NULL;
124124
DefElem*dcollate=NULL;
125125
DefElem*dctype=NULL;
126+
DefElem*distemplate=NULL;
127+
DefElem*dallowconnections=NULL;
126128
DefElem*dconnlimit=NULL;
127129
char*dbname=stmt->dbname;
128130
char*dbowner=NULL;
@@ -131,6 +133,8 @@ createdb(const CreatedbStmt *stmt)
131133
char*dbctype=NULL;
132134
char*canonname;
133135
intencoding=-1;
136+
booldbistemplate= false;
137+
booldballowconnections= true;
134138
intdbconnlimit=-1;
135139
intnotherbackends;
136140
intnpreparedxacts;
@@ -189,6 +193,22 @@ createdb(const CreatedbStmt *stmt)
189193
errmsg("conflicting or redundant options")));
190194
dctype=defel;
191195
}
196+
elseif (strcmp(defel->defname,"is_template")==0)
197+
{
198+
if (distemplate)
199+
ereport(ERROR,
200+
(errcode(ERRCODE_SYNTAX_ERROR),
201+
errmsg("conflicting or redundant options")));
202+
distemplate=defel;
203+
}
204+
elseif (strcmp(defel->defname,"allow_connections")==0)
205+
{
206+
if (dallowconnections)
207+
ereport(ERROR,
208+
(errcode(ERRCODE_SYNTAX_ERROR),
209+
errmsg("conflicting or redundant options")));
210+
dallowconnections=defel;
211+
}
192212
elseif (strcmp(defel->defname,"connection_limit")==0)
193213
{
194214
if (dconnlimit)
@@ -244,7 +264,10 @@ createdb(const CreatedbStmt *stmt)
244264
dbcollate=defGetString(dcollate);
245265
if (dctype&&dctype->arg)
246266
dbctype=defGetString(dctype);
247-
267+
if (distemplate&&distemplate->arg)
268+
dbistemplate=defGetBoolean(distemplate);
269+
if (dallowconnections&&dallowconnections->arg)
270+
dballowconnections=defGetBoolean(dallowconnections);
248271
if (dconnlimit&&dconnlimit->arg)
249272
{
250273
dbconnlimit=defGetInt32(dconnlimit);
@@ -487,8 +510,8 @@ createdb(const CreatedbStmt *stmt)
487510
DirectFunctionCall1(namein,CStringGetDatum(dbcollate));
488511
new_record[Anum_pg_database_datctype-1]=
489512
DirectFunctionCall1(namein,CStringGetDatum(dbctype));
490-
new_record[Anum_pg_database_datistemplate-1]=BoolGetDatum(false);
491-
new_record[Anum_pg_database_datallowconn-1]=BoolGetDatum(true);
513+
new_record[Anum_pg_database_datistemplate-1]=BoolGetDatum(dbistemplate);
514+
new_record[Anum_pg_database_datallowconn-1]=BoolGetDatum(dballowconnections);
492515
new_record[Anum_pg_database_datconnlimit-1]=Int32GetDatum(dbconnlimit);
493516
new_record[Anum_pg_database_datlastsysoid-1]=ObjectIdGetDatum(src_lastsysoid);
494517
new_record[Anum_pg_database_datfrozenxid-1]=TransactionIdGetDatum(src_frozenxid);
@@ -1328,7 +1351,11 @@ AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel)
13281351
ScanKeyDatascankey;
13291352
SysScanDescscan;
13301353
ListCell*option;
1331-
intconnlimit=-1;
1354+
booldbistemplate= false;
1355+
booldballowconnections= true;
1356+
intdbconnlimit=-1;
1357+
DefElem*distemplate=NULL;
1358+
DefElem*dallowconnections=NULL;
13321359
DefElem*dconnlimit=NULL;
13331360
DefElem*dtablespace=NULL;
13341361
Datumnew_record[Natts_pg_database];
@@ -1340,7 +1367,23 @@ AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel)
13401367
{
13411368
DefElem*defel= (DefElem*)lfirst(option);
13421369

1343-
if (strcmp(defel->defname,"connection_limit")==0)
1370+
if (strcmp(defel->defname,"is_template")==0)
1371+
{
1372+
if (distemplate)
1373+
ereport(ERROR,
1374+
(errcode(ERRCODE_SYNTAX_ERROR),
1375+
errmsg("conflicting or redundant options")));
1376+
distemplate=defel;
1377+
}
1378+
elseif (strcmp(defel->defname,"allow_connections")==0)
1379+
{
1380+
if (dallowconnections)
1381+
ereport(ERROR,
1382+
(errcode(ERRCODE_SYNTAX_ERROR),
1383+
errmsg("conflicting or redundant options")));
1384+
dallowconnections=defel;
1385+
}
1386+
elseif (strcmp(defel->defname,"connection_limit")==0)
13441387
{
13451388
if (dconnlimit)
13461389
ereport(ERROR,
@@ -1380,13 +1423,17 @@ AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel)
13801423
returnInvalidOid;
13811424
}
13821425

1426+
if (distemplate&&distemplate->arg)
1427+
dbistemplate=defGetBoolean(distemplate);
1428+
if (dallowconnections&&dallowconnections->arg)
1429+
dballowconnections=defGetBoolean(dallowconnections);
13831430
if (dconnlimit&&dconnlimit->arg)
13841431
{
1385-
connlimit=defGetInt32(dconnlimit);
1386-
if (connlimit<-1)
1432+
dbconnlimit=defGetInt32(dconnlimit);
1433+
if (dbconnlimit<-1)
13871434
ereport(ERROR,
13881435
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1389-
errmsg("invalid connection limit: %d",connlimit)));
1436+
errmsg("invalid connection limit: %d",dbconnlimit)));
13901437
}
13911438

13921439
/*
@@ -1413,16 +1460,37 @@ AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel)
14131460
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_DATABASE,
14141461
stmt->dbname);
14151462

1463+
/*
1464+
* In order to avoid getting locked out and having to go through
1465+
* standalone mode, we refuse to disallow connections to the database
1466+
* we're currently connected to. Lockout can still happen with concurrent
1467+
* sessions but the likeliness of that is not high enough to worry about.
1468+
*/
1469+
if (!dballowconnections&&dboid==MyDatabaseId)
1470+
ereport(ERROR,
1471+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1472+
errmsg("cannot disallow connections for current database")));
1473+
14161474
/*
14171475
* Build an updated tuple, perusing the information just obtained
14181476
*/
14191477
MemSet(new_record,0,sizeof(new_record));
14201478
MemSet(new_record_nulls, false,sizeof(new_record_nulls));
14211479
MemSet(new_record_repl, false,sizeof(new_record_repl));
14221480

1481+
if (distemplate)
1482+
{
1483+
new_record[Anum_pg_database_datistemplate-1]=BoolGetDatum(dbistemplate);
1484+
new_record_repl[Anum_pg_database_datistemplate-1]= true;
1485+
}
1486+
if (dallowconnections)
1487+
{
1488+
new_record[Anum_pg_database_datallowconn-1]=BoolGetDatum(dballowconnections);
1489+
new_record_repl[Anum_pg_database_datallowconn-1]= true;
1490+
}
14231491
if (dconnlimit)
14241492
{
1425-
new_record[Anum_pg_database_datconnlimit-1]=Int32GetDatum(connlimit);
1493+
new_record[Anum_pg_database_datconnlimit-1]=Int32GetDatum(dbconnlimit);
14261494
new_record_repl[Anum_pg_database_datconnlimit-1]= true;
14271495
}
14281496

‎src/bin/initdb/initdb.c

Lines changed: 1 addition & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2288,11 +2288,7 @@ make_template0(void)
22882288
PG_CMD_DECL;
22892289
constchar**line;
22902290
staticconstchar*template0_setup[]= {
2291-
"CREATE DATABASE template0;\n",
2292-
"UPDATE pg_database SET "
2293-
"datistemplate = 't', "
2294-
"datallowconn = 'f' "
2295-
" WHERE datname = 'template0';\n",
2291+
"CREATE DATABASE template0 IS_TEMPLATE = true ALLOW_CONNECTIONS = false;\n",
22962292

22972293
/*
22982294
* We use the OID of template0 to determine lastsysoid

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 3 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1374,19 +1374,15 @@ dumpCreateDB(PGconn *conn)
13741374
appendPQExpBuffer(buf," TABLESPACE = %s",
13751375
fmtId(dbtablespace));
13761376

1377+
if (strcmp(dbistemplate,"t")==0)
1378+
appendPQExpBuffer(buf," IS_TEMPLATE = true");
1379+
13771380
if (strcmp(dbconnlimit,"-1")!=0)
13781381
appendPQExpBuffer(buf," CONNECTION LIMIT = %s",
13791382
dbconnlimit);
13801383

13811384
appendPQExpBufferStr(buf,";\n");
13821385

1383-
if (strcmp(dbistemplate,"t")==0)
1384-
{
1385-
appendPQExpBufferStr(buf,"UPDATE pg_catalog.pg_database SET datistemplate = 't' WHERE datname = ");
1386-
appendStringLiteralConn(buf,dbname,conn);
1387-
appendPQExpBufferStr(buf,";\n");
1388-
}
1389-
13901386
if (binary_upgrade)
13911387
{
13921388
appendPQExpBufferStr(buf,"-- For binary upgrade, set datfrozenxid.\n");

‎src/bin/psql/tab-complete.c

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1021,7 +1021,8 @@ psql_completion(const char *text, int start, int end)
10211021
pg_strcasecmp(prev2_wd,"DATABASE")==0)
10221022
{
10231023
staticconstchar*constlist_ALTERDATABASE[]=
1024-
{"RESET","SET","OWNER TO","RENAME TO","CONNECTION LIMIT",NULL};
1024+
{"RESET","SET","OWNER TO","RENAME TO","IS_TEMPLATE",
1025+
"ALLOW_CONNECTIONS","CONNECTION LIMIT",NULL};
10251026

10261027
COMPLETE_WITH_LIST(list_ALTERDATABASE);
10271028
}
@@ -2111,8 +2112,8 @@ psql_completion(const char *text, int start, int end)
21112112
pg_strcasecmp(prev2_wd,"DATABASE")==0)
21122113
{
21132114
staticconstchar*constlist_DATABASE[]=
2114-
{"OWNER","TEMPLATE","ENCODING","TABLESPACE","CONNECTION LIMIT",
2115-
NULL};
2115+
{"OWNER","TEMPLATE","ENCODING","TABLESPACE","IS_TEMPLATE",
2116+
"ALLOW_CONNECTIONS","CONNECTION LIMIT",NULL};
21162117

21172118
COMPLETE_WITH_LIST(list_DATABASE);
21182119
}

0 commit comments

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