8000 Allow CREATE/ALTER DATABASE to manipulate datistemplate and datallowc… · postgrespro/postgres_cluster@fbb1d7d · GitHub
[go: up one dir, main page]
Skip to content

Commit fbb1d7d

Browse files
tglsfdctglsfdc
committed
Allow CREATE/ALTER DATABASE to manipulate datistemplate and datallowconn.
Historically these database properties could be manipulated only by manually updating pg_database, which is error-prone and only possible for superusers. But there seems no good reason not to allow database owners to set them for their databases, so invent CREATE/ALTER DATABASE options to do that. Adjust a couple of places that were doing it the hard way to use the commands instead. Vik Fearing, reviewed by Pavel Stehule
1 parent 15c82ef commit fbb1d7d

File tree

7 files changed

+135
-30
lines changed
  • bin

    • 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-
    "SET datallowconn = 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-
    "SET datallowconn = 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
    int encoding = -1;
    136+
    bool dbistemplate = false;
    137+
    bool dballowconnections = true;
    134138
    int dbconnlimit = -1;
    135139
    int notherbackends;
    136140
    int npreparedxacts;
    @@ -189,6 +193,22 @@ createdb(const CreatedbStmt *stmt)
    189193
    errmsg("conflicting or redundant options")));
    190194
    dctype = defel;
    191195
    }
    196+
    else if (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+
    else if (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
    else if (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
    ScanKeyData scankey;
    13291352
    SysScanDesc scan;
    13301353
    ListCell *option;
    1331-
    int connlimit = -1;
    1354+
    bool dbistemplate = false;
    1355+
    bool dballowconnections = true;
    1356+
    int dbconnlimit = -1;
    1357+
    DefElem *distemplate = NULL;
    1358+
    DefElem *dallowconnections = NULL;
    13321359
    DefElem *dconnlimit = NULL;
    13331360
    DefElem *dtablespace = NULL;
    13341361
    Datum new_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+
    else if (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+
    else if (strcmp(defel->defname, "connection_limit") == 0)
    13441387
    {
    13451388
    if (dconnlimit)
    13461389
    ereport(ERROR,
    @@ -1380,13 +1423,17 @@ AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel)
    13801423
    return InvalidOid;
    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
    const char **line;
    22902290
    static const char *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
    static const char *const list_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
    static const char *const list_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)
    0