We try to tell our clients not to update the catalogs directly, but
there are at least two instances where it's not possible to do otherwise
(pg_database.datistemplate and .datallowconn). This patch aims to
remedy that.
For example, it is now possible to say
ALTER DATABASE d ALLOW CONNECTIONS = false;
and
ALTER DATABASE d IS TEMPLATE = true;
This syntax matches that of CONNECTION LIMIT but unfortunately required
me to make ALLOW and CONNECTIONS unreserved keywords. I know we try not
to do that but I didn't see any other way. The two new options are of
course also available on CREATE DATABASE.
There is a slight change in behavior with this patch in that previously
one had to be superuser or have rolcatupdate appropriately set, and now
the owner of the database is also allowed to change these settings. I
believe this is for the better.
It was suggested to me that these options should either error out if
there are existing connections or terminate said connections. I don't
agree with that because there is no harm in connecting to a template
database (how else do you modify it?), and adding a reject rule in
pg_hba.conf doesn't disconnect existing users so why should turning off
ALLOW CONNECTIONS do it?
As for regression tests, I couldn't figure out how to make CREATE/ALTER
DATABASE play nice with make installcheck and so I haven't provided any.
Other than that, I think this patch is complete and so I'm adding it the
next commitfest.
--
Vik
*** a/doc/src/sgml/ref/alter_database.sgml
--- b/doc/src/sgml/ref/alter_database.sgml
***************
*** 25,30 **** ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <rep
--- 25,32 ----
<phrase>where <replaceable class="PARAMETER">option</replaceable> can be:</phrase>
+ IS TEMPLATE <replaceable class="PARAMETER">istemplate</replaceable>
+ ALLOW CONNECTIONS <replaceable class="PARAMETER">allowconn</replaceable>
CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
***************
*** 107,112 **** ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET ALL
--- 109,134 ----
</varlistentry>
<varlistentry>
+ <term><replaceable class="parameter">istemplate</replaceable></term>
+ <listitem>
+ <para>
+ If true, then this database can be cloned by any user with CREATEDB
+ privileges; if false, then only superusers or the owner of the
+ database can clone it.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">allowconn</replaceable></term>
+ <listitem>
+ <para>
+ If false then no one can connect to this database.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">connlimit</replaceable></term>
<listitem>
<para>
*** a/doc/src/sgml/ref/create_database.sgml
--- b/doc/src/sgml/ref/create_database.sgml
***************
*** 28,33 **** CREATE DATABASE <replaceable class="PARAMETER">name</replaceable>
--- 28,35 ----
[ LC_COLLATE [=] <replaceable class="parameter">lc_collate</replaceable> ]
[ LC_CTYPE [=] <replaceable class="parameter">lc_ctype</replaceable> ]
[ TABLESPACE [=] <replaceable class="parameter">tablespace_name</replaceable> ]
+ [ IS TEMPLATE [=] <replaceable class="parameter">istemplate</replaceable>]
+ [ ALLOW CONNECTIONS [=] <replaceable class="parameter">allowconn</replaceable>]
[ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ] ]
</synopsis>
</refsynopsisdiv>
***************
*** 148,153 **** CREATE DATABASE <replaceable class="PARAMETER">name</replaceable>
--- 150,175 ----
</varlistentry>
<varlistentry>
+ <term><replaceable class="parameter">istemplate</replaceable></term>
+ <listitem>
+ <para>
+ If true, then this database can be cloned by any user with CREATEDB
+ privileges; if false, then only superusers or the owner of the
+ database can clone it.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">allowconn</replaceable></term>
+ <listitem>
+ <para>
+ If false then no one can connect to this database.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">connlimit</replaceable></term>
<listitem>
<para>
*** a/src/backend/commands/dbcommands.c
--- b/src/backend/commands/dbcommands.c
***************
*** 39,44 ****
--- 39,45 ----
#include "catalog/pg_tablespace.h"
#include "commands/comment.h"
#include "commands/dbcommands.h"
+ #include "commands/defrem.h"
#include "commands/seclabel.h"
#include "commands/tablespace.h"
#include "mb/pg_wchar.h"
***************
*** 122,127 **** createdb(const CreatedbStmt *stmt)
--- 123,130 ----
DefElem *dencoding = NULL;
DefElem *dcollate = NULL;
DefElem *dctype = NULL;
+ DefElem *distemplate = NULL;
+ DefElem *dallowconn = NULL;
DefElem *dconnlimit = NULL;
char *dbname = stmt->dbname;
char *dbowner = NULL;
***************
*** 130,135 **** createdb(const CreatedbStmt *stmt)
--- 133,140 ----
char *dbctype = NULL;
char *canonname;
int encoding = -1;
+ bool istemplate = false;
+ bool allowconn = true;
int dbconnlimit = -1;
int notherbackends;
int npreparedxacts;
***************
*** 188,193 **** createdb(const CreatedbStmt *stmt)
--- 193,214 ----
errmsg("conflicting or redundant options")));
dctype = defel;
}
+ else if (strcmp(defel->defname, "istemplate") == 0)
+ {
+ if (distemplate)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ distemplate = defel;
+ }
+ else if (strcmp(defel->defname, "allowconnections") == 0)
+ {
+ if (dallowconn)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ dallowconn = defel;
+ }
else if (strcmp(defel->defname, "connectionlimit") == 0)
{
if (dconnlimit)
***************
*** 246,251 **** createdb(const CreatedbStmt *stmt)
--- 267,277 ----
if (dctype && dctype->arg)
dbctype = strVal(dctype->arg);
+ if (distemplate && distemplate->arg)
+ istemplate = defGetBoolean(distemplate);
+ if (dallowconn && dallowconn->arg)
+ allowconn = defGetBoolean(dallowconn);
+
if (dconnlimit && dconnlimit->arg)
{
dbconnlimit = intVal(dconnlimit->arg);
***************
*** 488,495 **** createdb(const CreatedbStmt *stmt)
DirectFunctionCall1(namein, CStringGetDatum(dbcollate));
new_record[Anum_pg_database_datctype - 1] =
DirectFunctionCall1(namein, CStringGetDatum(dbctype));
! new_record[Anum_pg_database_datistemplate - 1] = BoolGetDatum(false);
! new_record[Anum_pg_database_datallowconn - 1] = BoolGetDatum(true);
new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(dbconnlimit);
new_record[Anum_pg_database_datlastsysoid - 1] = ObjectIdGetDatum(src_lastsysoid);
new_record[Anum_pg_database_datfrozenxid - 1] = TransactionIdGetDatum(src_frozenxid);
--- 514,521 ----
DirectFunctionCall1(namein, CStringGetDatum(dbcollate));
new_record[Anum_pg_database_datctype - 1] =
DirectFunctionCall1(namein, CStringGetDatum(dbctype));
! new_record[Anum_pg_database_datistemplate - 1] = BoolGetDatum(istemplate);
! new_record[Anum_pg_database_datallowconn - 1] = BoolGetDatum(allowconn);
new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(dbconnlimit);
new_record[Anum_pg_database_datlastsysoid - 1] = ObjectIdGetDatum(src_lastsysoid);
new_record[Anum_pg_database_datfrozenxid - 1] = TransactionIdGetDatum(src_frozenxid);
***************
*** 1329,1335 **** AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel)
--- 1355,1365 ----
ScanKeyData scankey;
SysScanDesc scan;
ListCell *option;
+ bool istemplate = false;
+ bool allowconn = true;
int connlimit = -1;
+ DefElem *distemplate = NULL;
+ DefElem *dallowconn = NULL;
DefElem *dconnlimit = NULL;
DefElem *dtablespace = NULL;
Datum new_record[Natts_pg_database];
***************
*** 1341,1347 **** AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel)
{
DefElem *defel = (DefElem *) lfirst(option);
! if (strcmp(defel->defname, "connectionlimit") == 0)
{
if (dconnlimit)
ereport(ERROR,
--- 1371,1393 ----
{
DefElem *defel = (DefElem *) lfirst(option);
! if (strcmp(defel->defname, "istemplate") == 0)
! {
! if (distemplate)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("conflicting or redundant options")));
! distemplate = defel;
! }
! else if (strcmp(defel->defname, "allowconnections") == 0)
! {
! if (dallowconn)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("conflicting or redundant options")));
! dallowconn = defel;
! }
! else if (strcmp(defel->defname, "connectionlimit") == 0)
{
if (dconnlimit)
ereport(ERROR,
***************
*** 1365,1377 **** AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel)
if (dtablespace)
{
/* currently, can't be specified along with any other options */
! Assert(!dconnlimit);
/* this case isn't allowed within a transaction block */
PreventTransactionChain(isTopLevel, "ALTER DATABASE SET TABLESPACE");
movedb(stmt->dbname, strVal(dtablespace->arg));
return InvalidOid;
}
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
--- 1411,1428 ----
if (dtablespace)
{
/* currently, can't be specified along with any other options */
! Assert(!distemplate && !dallowconn && !dconnlimit);
/* this case isn't allowed within a transaction block */
PreventTransactionChain(isTopLevel, "ALTER DATABASE SET TABLESPACE");
movedb(stmt->dbname, strVal(dtablespace->arg));
return InvalidOid;
}
+ if (distemplate)
+ istemplate = defGetBoolean(distemplate);
+ if (dallowconn)
+ allowconn = defGetBoolean(dallowconn);
+
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
***************
*** 1412,1417 **** AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel)
--- 1463,1480 ----
MemSet(new_record_nulls, false, sizeof(new_record_nulls));
MemSet(new_record_repl, false, sizeof(new_record_repl));
+ if (distemplate)
+ {
+ new_record[Anum_pg_database_datistemplate - 1] = BoolGetDatum(istemplate);
+ new_record_repl[Anum_pg_database_datistemplate -1] = true;
+ }
+
+ if (dallowconn)
+ {
+ new_record[Anum_pg_database_datallowconn - 1] = BoolGetDatum(allowconn);
+ new_record_repl[Anum_pg_database_datallowconn - 1] = true;
+ }
+
if (dconnlimit)
{
new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(connlimit);
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 522,528 **** static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
/* ordinary key words in alphabetical order */
%token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD_P ADMIN AFTER
! AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC
ASSERTION ASSIGNMENT ASYMMETRIC AT ATTRIBUTE AUTHORIZATION
BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT
--- 522,528 ----
/* ordinary key words in alphabetical order */
%token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD_P ADMIN AFTER
! AGGREGATE ALL ALLOW ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC
ASSERTION ASSIGNMENT ASYMMETRIC AT ATTRIBUTE AUTHORIZATION
BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT
***************
*** 531,537 **** static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
CACHE CALLED CASCADE CASCADED CASE CAST CATALOG_P CHAIN CHAR_P
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT
! COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE
CROSS CSV CURRENT_P
CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
--- 531,537 ----
CACHE CALLED CASCADE CASCADED CASE CAST CATALOG_P CHAIN CHAR_P
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT
! COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONNECTIONS CONSTRAINT CONSTRAINTS
CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE
CROSS CSV CURRENT_P
CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
***************
*** 8374,8379 **** createdb_opt_item:
--- 8374,8387 ----
{
$$ = makeDefElem("lc_ctype", NULL);
}
+ | IS TEMPLATE opt_equal opt_boolean_or_string
+ {
+ $$ = makeDefElem("istemplate", (Node *)makeString($4));
+ }
+ | ALLOW CONNECTIONS opt_equal opt_boolean_or_string
+ {
+ $$ = makeDefElem("allowconnections", (Node *)makeString($4));
+ }
| CONNECTION LIMIT opt_equal SignedIconst
{
$$ = makeDefElem("connectionlimit", (Node *)makeInteger($4));
***************
*** 8438,8444 **** alterdb_opt_list:
;
alterdb_opt_item:
! CONNECTION LIMIT opt_equal SignedIconst
{
$$ = makeDefElem("connectionlimit", (Node *)makeInteger($4));
}
--- 8446,8460 ----
;
alterdb_opt_item:
! IS TEMPLATE opt_equal opt_boolean_or_string
! {
! $$ = makeDefElem("istemplate", (Node *)makeString($4));
! }
! | ALLOW CONNECTIONS opt_equal opt_boolean_or_string
! {
! $$ = makeDefElem("allowconnections", (Node *)makeString($4));
! }
! | CONNECTION LIMIT opt_equal SignedIconst
{
$$ = makeDefElem("connectionlimit", (Node *)makeInteger($4));
}
***************
*** 12805,12810 **** unreserved_keyword:
--- 12821,12827 ----
| ADMIN
| AFTER
| AGGREGATE
+ | ALLOW
| ALSO
| ALTER
| ALWAYS
***************
*** 12833,12838 **** unreserved_keyword:
--- 12850,12856 ----
| COMMITTED
| CONFIGURATION
| CONNECTION
+ | CONNECTIONS
| CONSTRAINTS
| CONTENT_P
| CONTINUE_P
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 987,994 **** psql_completion(const char *text, int start, int end)
{"OWNER TO", "RENAME TO", "SET SCHEMA", NULL};
COMPLETE_WITH_LIST(list_ALTERGEN);
! }
!
/* ALTER CONVERSION <name> */
else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
pg_strcasecmp(prev2_wd, "CONVERSION") == 0)
--- 987,993 ----
{"OWNER TO", "RENAME TO", "SET SCHEMA", NULL};
COMPLETE_WITH_LIST(list_ALTERGEN);
! }
/* ALTER CONVERSION <name> */
else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
pg_strcasecmp(prev2_wd, "CONVERSION") == 0)
***************
*** 1004,1010 **** psql_completion(const char *text, int start, int end)
pg_strcasecmp(prev2_wd, "DATABASE") == 0)
{
static const char *const list_ALTERDATABASE[] =
! {"RESET", "SET", "OWNER TO", "RENAME TO", "CONNECTION LIMIT", NULL};
COMPLETE_WITH_LIST(list_ALTERDATABASE);
}
--- 1003,1010 ----
pg_strcasecmp(prev2_wd, "DATABASE") == 0)
{
static const char *const list_ALTERDATABASE[] =
! {"RESET", "SET", "OWNER TO", "RENAME TO", "IS TEMPLATE",
! "ALLOW CONNECTIONS", "CONNECTION LIMIT", NULL};
COMPLETE_WITH_LIST(list_ALTERDATABASE);
}
***************
*** 2045,2052 **** psql_completion(const char *text, int start, int end)
pg_strcasecmp(prev2_wd, "DATABASE") == 0)
{
static const char *const list_DATABASE[] =
! {"OWNER", "TEMPLATE", "ENCODING", "TABLESPACE", "CONNECTION LIMIT",
! NULL};
COMPLETE_WITH_LIST(list_DATABASE);
}
--- 2045,2052 ----
pg_strcasecmp(prev2_wd, "DATABASE") == 0)
{
static const char *const list_DATABASE[] =
! {"OWNER", "TEMPLATE", "ENCODING", "TABLESPACE", "IS TEMPLATE",
! "ALLOW CONNECTIONS", "CONNECTION LIMIT", NULL};
COMPLETE_WITH_LIST(list_DATABASE);
}
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 35,40 **** PG_KEYWORD("admin", ADMIN, UNRESERVED_KEYWORD)
--- 35,41 ----
PG_KEYWORD("after", AFTER, UNRESERVED_KEYWORD)
PG_KEYWORD("aggregate", AGGREGATE, UNRESERVED_KEYWORD)
PG_KEYWORD("all", ALL, RESERVED_KEYWORD)
+ PG_KEYWORD("allow", ALLOW, UNRESERVED_KEYWORD)
PG_KEYWORD("also", ALSO, UNRESERVED_KEYWORD)
PG_KEYWORD("alter", ALTER, UNRESERVED_KEYWORD)
PG_KEYWORD("always", ALWAYS, UNRESERVED_KEYWORD)
***************
*** 88,93 **** PG_KEYWORD("committed", COMMITTED, UNRESERVED_KEYWORD)
--- 89,95 ----
PG_KEYWORD("concurrently", CONCURRENTLY, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("configuration", CONFIGURATION, UNRESERVED_KEYWORD)
PG_KEYWORD("connection", CONNECTION, UNRESERVED_KEYWORD)
+ PG_KEYWORD("connections", CONNECTIONS, UNRESERVED_KEYWORD)
PG_KEYWORD("constraint", CONSTRAINT, RESERVED_KEYWORD)
PG_KEYWORD("constraints", CONSTRAINTS, UNRESERVED_KEYWORD)
PG_KEYWORD("content", CONTENT_P, UNRESERVED_KEYWORD)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
