diff options
30 files changed, 783 insertions, 180 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 0c8ed68195d..d8ec4f9f658 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,6 +1,6 @@ <!-- Documentation of the system catalogs, directed toward PostgreSQL developers - $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.109 2005/07/26 16:38:25 tgl Exp $ + $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.110 2005/07/31 17:19:16 tgl Exp $ --> <chapter id="catalogs"> @@ -1019,6 +1019,16 @@ </row> <row> + <entry><structfield>rolconnlimit</structfield></entry> + <entry><type>int4</type></entry> + <entry></entry> + <entry> + For roles that can log in, this sets maximum number of concurrent + connections this role can make. -1 means no limit. + </entry> + </row> + + <row> <entry><structfield>rolpassword</structfield></entry> <entry><type>text</type></entry> <entry></entry> @@ -1922,6 +1932,16 @@ </row> <row> + <entry><structfield>datconnlimit</structfield></entry> + <entry><type>int4</type></entry> + <entry></entry> + <entry> + Sets maximum number of concurrent connections that can be made + to this database. -1 means no limit. + </entry> + </row> + + <row> <entry><structfield>datlastsysoid</structfield></entry> <entry><type>oid</type></entry> <entry></entry> @@ -4812,6 +4832,16 @@ </row> <row> + <entry><structfield>rolconnlimit</structfield></entry> + <entry><type>int4</type></entry> + <entry></entry> + <entry> + For roles that can log in, this sets maximum number of concurrent + connections this role can make. -1 means no limit. + </entry> + </row> + + <row> <entry><structfield>rolpassword</structfield></entry> <entry><type>text</type></entry> <entry></entry> diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml index c209308716a..03333d3d99b 100644 --- a/doc/src/sgml/ref/alter_database.sgml +++ b/doc/src/sgml/ref/alter_database.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/alter_database.sgml,v 1.15 2005/01/05 14:22:39 petere Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/alter_database.sgml,v 1.16 2005/07/31 17:19:16 tgl Exp $ PostgreSQL documentation --> @@ -20,6 +20,12 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> +ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replaceable class="PARAMETER">option</replaceable> [ ... ] ] + +where <replaceable class="PARAMETER">option</replaceable> can be: + + CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable> + ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT } ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable>parameter</replaceable> @@ -38,7 +44,12 @@ ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> OWNER TO <repla </para> <para> - The first two forms change the session default for a run-time + The first form changes certain per-database settings. (See below for + details.) Only the database owner or a superuser can change these settings. + </para> + + <para> + The second and third forms change the session default for a run-time configuration variable for a <productname>PostgreSQL</productname> database. Whenever a new session is subsequently started in that database, the specified value becomes the session default value. @@ -51,7 +62,7 @@ ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> OWNER TO <repla </para> <para> - The third form changes the name of the database. Only the database + The fourth form changes the name of the database. Only the database owner or a superuser can rename a database; non-superuser owners must also have the <literal>CREATEDB</literal> privilege. The current database cannot @@ -60,7 +71,7 @@ ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> OWNER TO <repla </para> <para> - The fourth form changes the owner of the database. Only a superuser + The fifth form changes the owner of the database. Only a superuser can change the database's owner. </para> </refsect1> @@ -79,6 +90,16 @@ ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> OWNER TO <repla </varlistentry> <varlistentry> + <term><replaceable class="parameter">connlimit</replaceable></term> + <listitem> + <para> + How many concurrent connections can be made + to this database. -1 means no limit. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><replaceable>parameter</replaceable></term> <term><replaceable>value</replaceable></term> <listitem> diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml index 3e87cf01763..7ebd8014f29 100644 --- a/doc/src/sgml/ref/alter_role.sgml +++ b/doc/src/sgml/ref/alter_role.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/alter_role.sgml,v 1.1 2005/07/26 23:24:02 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/alter_role.sgml,v 1.2 2005/07/31 17:19:17 tgl Exp $ PostgreSQL documentation --> @@ -30,6 +30,7 @@ where <replaceable class="PARAMETER">option</replaceable> can be: | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN + | CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable> | [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>' | VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>' @@ -118,6 +119,7 @@ ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable> <term><literal>NOINHERIT</literal></term> <term><literal>LOGIN</literal></term> <term><literal>NOLOGIN</literal></term> + <term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term> <term><literal>PASSWORD</> <replaceable class="parameter">password</replaceable></term> <term><literal>ENCRYPTED</></term> <term><literal>UNENCRYPTED</></term> diff --git a/doc/src/sgml/ref/alter_user.sgml b/doc/src/sgml/ref/alter_user.sgml index 2844a63d083..d5dc3f1fca3 100644 --- a/doc/src/sgml/ref/alter_user.sgml +++ b/doc/src/sgml/ref/alter_user.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/alter_user.sgml,v 1.38 2005/07/26 23:24:02 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/alter_user.sgml,v 1.39 2005/07/31 17:19:17 tgl Exp $ PostgreSQL documentation --> @@ -30,6 +30,7 @@ where <replaceable class="PARAMETER">option</replaceable> can be: | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN + | CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable> | [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>' | VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>' diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index edb98b784b9..b4bd2d57398 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/create_database.sgml,v 1.43 2004/10/29 03:17:22 neilc Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/create_database.sgml,v 1.44 2005/07/31 17:19:17 tgl Exp $ PostgreSQL documentation --> @@ -24,7 +24,8 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] [ OWNER [=] <replaceable class="parameter">dbowner</replaceable> ] [ TEMPLATE [=] <replaceable class="parameter">template</replaceable> ] [ ENCODING [=] <replaceable class="parameter">encoding</replaceable> ] - [ TABLESPACE [=] <replaceable class="parameter">tablespace</replaceable> ] ] + [ TABLESPACE [=] <replaceable class="parameter">tablespace</replaceable> ] + [ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ] ] </synopsis> </refsynopsisdiv> @@ -123,6 +124,16 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> </para> </listitem> </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">connlimit</replaceable></term> + <listitem> + <para> + How many concurrent connections can be made + to this database. -1 (the default) means no limit. + </para> + </listitem> + </varlistentry> </variablelist> <para> @@ -161,6 +172,13 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> We recommend that databases used as templates be treated as read-only. See <xref linkend="manage-ag-templatedbs"> for more information. </para> + + <para> + The <literal>CONNECTION LIMIT</> option is only enforced approximately; + if two new sessions start at about the same time when just one + connection <quote>slot</> remains for the database, it is possible that + both will fail. Also, the limit is not enforced against superusers. + </para> </refsect1> <refsect1> diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index 598888528fb..4cff62a6ec6 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/create_role.sgml,v 1.1 2005/07/26 23:24:02 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/create_role.sgml,v 1.2 2005/07/31 17:19:17 tgl Exp $ PostgreSQL documentation --> @@ -30,6 +30,7 @@ where <replaceable class="PARAMETER">option</replaceable> can be: | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN + | CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable> | [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>' | VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>' | IN ROLE <replaceable class="PARAMETER">rolename</replaceable> [, ...] @@ -173,6 +174,16 @@ where <replaceable class="PARAMETER">option</replaceable> can be: </varlistentry> <varlistentry> + <term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term> + <listitem> + <para> + If role can log in, this specifies how many concurrent connections + the role can make. -1 (the default) means no limit. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>PASSWORD</> <replaceable class="parameter">password</replaceable></term> <listitem> <para> @@ -327,6 +338,13 @@ where <replaceable class="PARAMETER">option</replaceable> can be: the same functionality as <command>CREATE ROLE</command> (in fact, it calls this command) but can be run from the command shell. </para> + + <para> + The <literal>CONNECTION LIMIT</> option is only enforced approximately; + if two new sessions start at about the same time when just one + connection <quote>slot</> remains for the role, it is possible that + both will fail. Also, the limit is never enforced for superusers. + </para> </refsect1> <refsect1> diff --git a/doc/src/sgml/ref/create_user.sgml b/doc/src/sgml/ref/create_user.sgml index 68d37bbd86d..44d52cd779a 100644 --- a/doc/src/sgml/ref/create_user.sgml +++ b/doc/src/sgml/ref/create_user.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/create_user.sgml,v 1.37 2005/07/26 23:24:02 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/create_user.sgml,v 1.38 2005/07/31 17:19:17 tgl Exp $ PostgreSQL documentation --> @@ -30,6 +30,7 @@ where <replaceable class="PARAMETER">option</replaceable> can be: | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN + | CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable> | [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>' | VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>' | IN ROLE <replaceable class="PARAMETER">rolename</replaceable> [, ...] diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c index 5f7ea98ffe1..62ebf9fb406 100644 --- a/src/backend/access/transam/twophase.c +++ b/src/backend/access/transam/twophase.c @@ -7,7 +7,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/transam/twophase.c,v 1.8 2005/07/04 04:51:44 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/access/transam/twophase.c,v 1.9 2005/07/31 17:19:17 tgl Exp $ * * NOTES * Each global transaction is associated with a global transaction @@ -272,6 +272,7 @@ MarkAsPreparing(TransactionId xid, const char *gid, gxact->proc.xmin = InvalidTransactionId; gxact->proc.pid = 0; gxact->proc.databaseId = databaseid; + gxact->proc.roleId = owner; gxact->proc.lwWaiting = false; gxact->proc.lwExclusive = false; gxact->proc.lwWaitLink = NULL; diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 22e2c911107..f3f3b356808 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -3,7 +3,7 @@ * * Copyright (c) 1996-2005, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.17 2005/07/26 16:38:26 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.18 2005/07/31 17:19:17 tgl Exp $ */ CREATE VIEW pg_roles AS @@ -15,6 +15,7 @@ CREATE VIEW pg_roles AS rolcreatedb, rolcatupdate, rolcanlogin, + rolconnlimit, '********'::text as rolpassword, rolvaliduntil, rolconfig, diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c index 4e23def8361..295ae955d1f 100644 --- a/src/backend/commands/dbcommands.c +++ b/src/backend/commands/dbcommands.c @@ -15,7 +15,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/dbcommands.c,v 1.167 2005/07/14 21:46:29 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/dbcommands.c,v 1.168 2005/07/31 17:19:17 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -92,10 +92,12 @@ createdb(const CreatedbStmt *stmt) DefElem *downer = NULL; DefElem *dtemplate = NULL; DefElem *dencoding = NULL; + DefElem *dconnlimit = NULL; char *dbname = stmt->dbname; char *dbowner = NULL; const char *dbtemplate = NULL; int encoding = -1; + int dbconnlimit = -1; #ifndef WIN32 char buf[2 * MAXPGPATH + 100]; @@ -141,6 +143,14 @@ createdb(const CreatedbStmt *stmt) errmsg("conflicting or redundant options"))); dencoding = defel; } + else if (strcmp(defel->defname, "connectionlimit") == 0) + { + if (dconnlimit) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dconnlimit = defel; + } else if (strcmp(defel->defname, "location") == 0) { ereport(WARNING, @@ -186,6 +196,8 @@ createdb(const CreatedbStmt *stmt) elog(ERROR, "unrecognized node type: %d", nodeTag(dencoding->arg)); } + if (dconnlimit && dconnlimit->arg) + dbconnlimit = intVal(dconnlimit->arg); /* obtain OID of proposed owner */ if (dbowner) @@ -484,6 +496,7 @@ createdb(const CreatedbStmt *stmt) new_record[Anum_pg_database_encoding - 1] = Int32GetDatum(encoding); 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_datvacuumxid - 1] = TransactionIdGetDatum(src_vacuumxid); new_record[Anum_pg_database_datfrozenxid - 1] = TransactionIdGetDatum(src_frozenxid); @@ -791,6 +804,98 @@ RenameDatabase(const char *oldname, const char *newname) /* + * ALTER DATABASE name ... + */ +void +AlterDatabase(AlterDatabaseStmt *stmt) +{ + Relation rel; + HeapTuple tuple, + newtuple; + ScanKeyData scankey; + SysScanDesc scan; + ListCell *option; + int connlimit = -1; + DefElem *dconnlimit = NULL; + Datum new_record[Natts_pg_database]; + char new_record_nulls[Natts_pg_database]; + char new_record_repl[Natts_pg_database]; + + /* Extract options from the statement node tree */ + foreach(option, stmt->options) + { + DefElem *defel = (DefElem *) lfirst(option); + + if (strcmp(defel->defname, "connectionlimit") == 0) + { + if (dconnlimit) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dconnlimit = defel; + } + else + elog(ERROR, "option \"%s\" not recognized", + defel->defname); + } + + if (dconnlimit) + connlimit = intVal(dconnlimit->arg); + + /* + * We don't need ExclusiveLock since we aren't updating the + * flat file. + */ + rel = heap_open(DatabaseRelationId, RowExclusiveLock); + ScanKeyInit(&scankey, + Anum_pg_database_datname, + BTEqualStrategyNumber, F_NAMEEQ, + NameGetDatum(stmt->dbname)); + scan = systable_beginscan(rel, DatabaseNameIndexId, true, + SnapshotNow, 1, &scankey); + tuple = systable_getnext(scan); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_DATABASE), + errmsg("database \"%s\" does not exist", stmt->dbname))); + + if (!pg_database_ownercheck(HeapTupleGetOid(tuple), GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE, + stmt->dbname); + + /* + * Build an updated tuple, perusing the information just obtained + */ + MemSet(new_record, 0, sizeof(new_record)); + MemSet(new_record_nulls, ' ', sizeof(new_record_nulls)); + MemSet(new_record_repl, ' ', sizeof(new_record_repl)); + + if (dconnlimit) + { + new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(connlimit); + new_record_repl[Anum_pg_database_datconnlimit - 1] = 'r'; + } + + newtuple = heap_modifytuple(tuple, RelationGetDescr(rel), new_record, + new_record_nulls, new_record_repl); + simple_heap_update(rel, &tuple->t_self, newtuple); + + /* Update indexes */ + CatalogUpdateIndexes(rel, newtuple); + + systable_endscan(scan); + + /* Close pg_database, but keep lock till commit */ + heap_close(rel, NoLock); + + /* + * We don't bother updating the flat file since the existing options + * for ALTER DATABASE don't affect it. + */ +} + + +/* * ALTER DATABASE name SET ... */ void diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index 6ef612dc4a9..082ea0cf7a0 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/backend/commands/user.c,v 1.159 2005/07/26 22:37:49 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/user.c,v 1.160 2005/07/31 17:19:17 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -86,6 +86,7 @@ CreateRole(CreateRoleStmt *stmt) bool createrole = false; /* Can this user create roles? */ bool createdb = false; /* Can the user create databases? */ bool canlogin = false; /* Can this user login? */ + int connlimit = -1; /* maximum connections allowed */ List *addroleto = NIL; /* roles to make this a member of */ List *rolemembers = NIL; /* roles to be members of this role */ List *adminmembers = NIL; /* roles to be admins of this role */ @@ -96,6 +97,7 @@ CreateRole(CreateRoleStmt *stmt) DefElem *dcreaterole = NULL; DefElem *dcreatedb = NULL; DefElem *dcanlogin = NULL; + DefElem *dconnlimit = NULL; DefElem *daddroleto = NULL; DefElem *drolemembers = NULL; DefElem *dadminmembers = NULL; @@ -178,6 +180,14 @@ CreateRole(CreateRoleStmt *stmt) errmsg("conflicting or redundant options"))); dcanlogin = defel; } + else if (strcmp(defel->defname, "connectionlimit") == 0) + { + if (dconnlimit) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dconnlimit = defel; + } else if (strcmp(defel->defname, "addroleto") == 0) { if (daddroleto) @@ -227,6 +237,8 @@ CreateRole(CreateRoleStmt *stmt) createdb = intVal(dcreatedb->arg) != 0; if (dcanlogin) canlogin = intVal(dcanlogin->arg) != 0; + if (dconnlimit) + connlimit = intVal(dconnlimit->arg); if (daddroleto) addroleto = (List *) daddroleto->arg; if (drolemembers) @@ -292,6 +304,7 @@ CreateRole(CreateRoleStmt *stmt) /* superuser gets catupdate right by default */ new_record[Anum_pg_authid_rolcatupdate - 1] = BoolGetDatum(issuper); new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin); + new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit); if (password) { @@ -401,6 +414,7 @@ AlterRole(AlterRoleStmt *stmt) int createrole = -1; /* Can this user create roles? */ int createdb = -1; /* Can the user create databases? */ int canlogin = -1; /* Can this user login? */ + int connlimit = -1; /* maximum connections allowed */ List *rolemembers = NIL; /* roles to be added/removed */ char *validUntil = NULL; /* time the login is valid until */ DefElem *dpassword = NULL; @@ -409,6 +423,7 @@ AlterRole(AlterRoleStmt *stmt) DefElem *dcreaterole = NULL; DefElem *dcreatedb = NULL; DefElem *dcanlogin = NULL; + DefElem *dconnlimit = NULL; DefElem *drolemembers = NULL; DefElem *dvalidUntil = NULL; Oid roleid; @@ -472,6 +487,14 @@ AlterRole(AlterRoleStmt *stmt) errmsg("conflicting or redundant options"))); dcanlogin = defel; } + else if (strcmp(defel->defname, "connectionlimit") == 0) + { + if (dconnlimit) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dconnlimit = defel; + } else if (strcmp(defel->defname, "rolemembers") == 0 && stmt->action != 0) { @@ -506,6 +529,8 @@ AlterRole(AlterRoleStmt *stmt) createdb = intVal(dcreatedb->arg); if (dcanlogin) canlogin = intVal(dcanlogin->arg); + if (dconnlimit) + connlimit = intVal(dconnlimit->arg); if (drolemembers) rolemembers = (List *) drolemembers->arg; if (dvalidUntil) @@ -545,6 +570,7 @@ AlterRole(AlterRoleStmt *stmt) createrole < 0 && createdb < 0 && canlogin < 0 && + !dconnlimit && !rolemembers && !validUntil && password && @@ -602,6 +628,12 @@ AlterRole(AlterRoleStmt *stmt) new_record_repl[Anum_pg_authid_rolcanlogin - 1] = 'r'; } + if (dconnlimit) + { + new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit); + new_record_repl[Anum_pg_authid_rolconnlimit - 1] = 'r'; + } + /* password */ if (password) { diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 283cf549514..35fbee61641 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.312 2005/07/26 16:38:27 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.313 2005/07/31 17:19:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -2204,6 +2204,17 @@ _copyCreatedbStmt(CreatedbStmt *from) return newnode; } +static AlterDatabaseStmt * +_copyAlterDatabaseStmt(AlterDatabaseStmt *from) +{ + AlterDatabaseStmt *newnode = makeNode(AlterDatabaseStmt); + + COPY_STRING_FIELD(dbname); + COPY_NODE_FIELD(options); + + return newnode; +} + static AlterDatabaseSetStmt * _copyAlterDatabaseSetStmt(AlterDatabaseSetStmt *from) { @@ -3011,6 +3022,9 @@ copyObject(void *from) case T_CreatedbStmt: retval = _copyCreatedbStmt(from); break; + case T_AlterDatabaseStmt: + retval = _copyAlterDatabaseStmt(from); + break; case T_AlterDatabaseSetStmt: retval = _copyAlterDatabaseSetStmt(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 47e989dbc7d..6d727a63278 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -18,7 +18,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.249 2005/07/26 16:38:27 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.250 2005/07/31 17:19:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1152,6 +1152,15 @@ _equalCreatedbStmt(CreatedbStmt *a, CreatedbStmt *b) } static bool +_equalAlterDatabaseStmt(AlterDatabaseStmt *a, AlterDatabaseStmt *b) +{ + COMPARE_STRING_FIELD(dbname); + COMPARE_NODE_FIELD(options); + + return true; +} + +static bool _equalAlterDatabaseSetStmt(AlterDatabaseSetStmt *a, AlterDatabaseSetStmt *b) { COMPARE_STRING_FIELD(dbname); @@ -2059,6 +2068,9 @@ equal(void *a, void *b) case T_CreatedbStmt: retval = _equalCreatedbStmt(a, b); break; + case T_AlterDatabaseStmt: + retval = _equalAlterDatabaseStmt(a, b); + break; case T_AlterDatabaseSetStmt: retval = _equalAlterDatabaseSetStmt(a, b); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 3ce9ec411b7..f0475bf2bee 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.504 2005/07/26 22:37:50 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.505 2005/07/31 17:19:18 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -131,9 +131,9 @@ static void doNegateFloat(Value *v); } %type <node> stmt schema_stmt - AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt - AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt - AlterRoleStmt AlterRoleSetStmt + AlterDatabaseStmt AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt + AlterOwnerStmt AlterSeqStmt AlterTableStmt + AlterUserStmt AlterUserSetStmt AlterRoleStmt AlterRoleSetStmt AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt @@ -165,8 +165,10 @@ static void doNegateFloat(Value *v); %type <dbehavior> opt_drop_behavior -%type <list> createdb_opt_list copy_opt_list transaction_mode_list -%type <defelt> createdb_opt_item copy_opt_item transaction_mode_item +%type <list> createdb_opt_list alterdb_opt_list copy_opt_list + transaction_mode_list +%type <defelt> createdb_opt_item alterdb_opt_item copy_opt_item + transaction_mode_item %type <ival> opt_lock lock_type cast_context %type <boolean> opt_force opt_or_replace @@ -257,7 +259,7 @@ static void doNegateFloat(Value *v); %type <boolean> copy_from opt_hold -%type <ival> fetch_count opt_column event cursor_options +%type <ival> opt_column event cursor_options %type <objtype> reindex_type drop_type comment_type %type <node> fetch_direction select_limit_value select_offset_value @@ -302,7 +304,7 @@ static void doNegateFloat(Value *v); %type <ival> opt_numeric opt_decimal %type <boolean> opt_varying opt_timezone -%type <ival> Iconst +%type <ival> Iconst SignedIconst %type <str> Sconst comment_text %type <str> RoleId opt_granted_by opt_boolean ColId_or_Sconst %type <list> var_list var_list_or_default @@ -342,7 +344,7 @@ static void doNegateFloat(Value *v); CACHE CALLED CASCADE CASE CAST CHAIN CHAR_P CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT - COMMITTED CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY CREATE CREATEDB + COMMITTED CONNECTION CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY CREATE CREATEDB CREATEROLE CREATEUSER CROSS CSV CURRENT_DATE CURRENT_ROLE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE @@ -486,7 +488,8 @@ stmtmulti: stmtmulti ';' stmt ; stmt : - AlterDatabaseSetStmt + AlterDatabaseStmt + | AlterDatabaseSetStmt | AlterDomainStmt | AlterFunctionStmt | AlterGroupStmt @@ -672,6 +675,10 @@ OptRoleElem: { $$ = makeDefElem("canlogin", (Node *)makeInteger(FALSE)); } + | CONNECTION LIMIT SignedIconst + { + $$ = makeDefElem("connectionlimit", (Node *)makeInteger($3)); + } | IN_P ROLE name_list { $$ = makeDefElem("addroleto", (Node *)$3); @@ -2238,17 +2245,8 @@ FloatOnly: FCONST { $$ = makeFloat($1); } } ; -IntegerOnly: - Iconst - { - $$ = makeInteger($1); - } - | '-' Iconst - { - $$ = makeInteger($2); - $$->val.ival = - $$->val.ival; - } - ; +IntegerOnly: SignedIconst { $$ = makeInteger($1); }; + /***************************************************************************** * @@ -3044,21 +3042,21 @@ fetch_direction: n->howMany = -1; $$ = (Node *)n; } - | ABSOLUTE_P fetch_count + | ABSOLUTE_P SignedIconst { FetchStmt *n = makeNode(FetchStmt); n->direction = FETCH_ABSOLUTE; n->howMany = $2; $$ = (Node *)n; } - | RELATIVE_P fetch_count + | RELATIVE_P SignedIconst { FetchStmt *n = makeNode(FetchStmt); n->direction = FETCH_RELATIVE; n->howMany = $2; $$ = (Node *)n; } - | fetch_count + | SignedIconst { FetchStmt *n = makeNode(FetchStmt); n->direction = FETCH_FORWARD; @@ -3079,7 +3077,7 @@ fetch_direction: n->howMany = 1; $$ = (Node *)n; } - | FORWARD fetch_count + | FORWARD SignedIconst { FetchStmt *n = makeNode(FetchStmt); n->direction = FETCH_FORWARD; @@ -3100,7 +3098,7 @@ fetch_direction: n->howMany = 1; $$ = (Node *)n; } - | BACKWARD fetch_count + | BACKWARD SignedIconst { FetchStmt *n = makeNode(FetchStmt); n->direction = FETCH_BACKWARD; @@ -3116,11 +3114,6 @@ fetch_direction: } ; -fetch_count: - Iconst { $$ = $1; } - | '-' Iconst { $$ = - $2; } - ; - from_in: FROM {} | IN_P {} ; @@ -4473,6 +4466,10 @@ createdb_opt_item: { $$ = makeDefElem("encoding", NULL); } + | CONNECTION LIMIT opt_equal SignedIconst + { + $$ = makeDefElem("connectionlimit", (Node *)makeInteger($4)); + } | OWNER opt_equal name { $$ = makeDefElem("owner", (Node *)makeString($3)); @@ -4485,8 +4482,7 @@ createdb_opt_item: /* * Though the equals sign doesn't match other WITH options, pg_dump uses - * equals for backward compability, and it doesn't seem worth removing it. - * 2002-02-25 + * equals for backward compatibility, and it doesn't seem worth removing it. */ opt_equal: '=' {} | /*EMPTY*/ {} @@ -4499,6 +4495,16 @@ opt_equal: '=' {} * *****************************************************************************/ +AlterDatabaseStmt: + ALTER DATABASE database_name opt_with alterdb_opt_list + { + AlterDatabaseStmt *n = makeNode(AlterDatabaseStmt); + n->dbname = $3; + n->options = $5; + $$ = (Node *)n; + } + ; + AlterDatabaseSetStmt: ALTER DATABASE database_name SET set_rest { @@ -4519,6 +4525,19 @@ AlterDatabaseSetStmt: ; +alterdb_opt_list: + alterdb_opt_list alterdb_opt_item { $$ = lappend($1, $2); } + | /* EMPTY */ { $$ = NIL; } + ; + +alterdb_opt_item: + CONNECTION LIMIT opt_equal SignedIconst + { + $$ = makeDefElem("connectionlimit", (Node *)makeInteger($4)); + } + ; + + /***************************************************************************** * * DROP DATABASE @@ -7875,6 +7894,10 @@ Iconst: ICONST { $$ = $1; }; Sconst: SCONST { $$ = $1; }; RoleId: ColId { $$ = $1; }; +SignedIconst: ICONST { $$ = $1; } + | '-' ICONST { $$ = - $2; } + ; + /* * Name classification hierarchy. * @@ -7959,6 +7982,7 @@ unreserved_keyword: | COMMENT | COMMIT | COMMITTED + | CONNECTION | CONSTRAINTS | CONVERSION_P | COPY diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c index 5d4cab2124f..9c09dc5bf3b 100644 --- a/src/backend/parser/keywords.c +++ b/src/backend/parser/keywords.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.163 2005/07/26 16:38:27 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.164 2005/07/31 17:19:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -83,6 +83,7 @@ static const ScanKeyword ScanKeywords[] = { {"comment", COMMENT}, {"commit", COMMIT}, {"committed", COMMITTED}, + {"connection", CONNECTION}, {"constraint", CONSTRAINT}, {"constraints", CONSTRAINTS}, {"conversion", CONVERSION_P}, diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index 7c2766e6285..1e2783dea80 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -23,7 +23,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/storage/ipc/procarray.c,v 1.3 2005/06/17 22:32:45 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/storage/ipc/procarray.c,v 1.4 2005/07/31 17:19:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -733,6 +733,60 @@ CountActiveBackends(void) return count; } +/* + * CountDBBackends --- count backends that are using specified database + */ +int +CountDBBackends(Oid databaseid) +{ + ProcArrayStruct *arrayP = procArray; + int count = 0; + int index; + + LWLockAcquire(ProcArrayLock, LW_SHARED); + + for (index = 0; index < arrayP->numProcs; index++) + { + PGPROC *proc = arrayP->procs[index]; + + if (proc->pid == 0) + continue; /* do not count prepared xacts */ + if (proc->databaseId == databaseid) + count++; + } + + LWLockRelease(ProcArrayLock); + + return count; +} + +/* + * CountUserBackends --- count backends that are used by specified user + */ +int +CountUserBackends(Oid roleid) +{ + ProcArrayStruct *arrayP = procArray; + int count = 0; + int index; + + LWLockAcquire(ProcArrayLock, LW_SHARED); + + for (index = 0; index < arrayP->numProcs; index++) + { + PGPROC *proc = arrayP->procs[index]; + + if (proc->pid == 0) + continue; /* do not count prepared xacts */ + if (proc->roleId == roleid) + count++; + } + + LWLockRelease(ProcArrayLock); + + return count; +} + #define XidCacheRemove(i) \ do { \ diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index 227c3694788..11d0c70f6df 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/storage/lmgr/proc.c,v 1.160 2005/06/17 22:32:45 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/storage/lmgr/proc.c,v 1.161 2005/07/31 17:19:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -254,6 +254,8 @@ InitProcess(void) MyProc->xmin = InvalidTransactionId; MyProc->pid = MyProcPid; MyProc->databaseId = MyDatabaseId; + /* Will be set properly after the session role id is determined */ + MyProc->roleId = InvalidOid; MyProc->lwWaiting = false; MyProc->lwExclusive = false; MyProc->lwWaitLink = NULL; @@ -331,6 +333,7 @@ InitDummyProcess(int proctype) MyProc->xid = InvalidTransactionId; MyProc->xmin = InvalidTransactionId; MyProc->databaseId = MyDatabaseId; + MyProc->roleId = InvalidOid; MyProc->lwWaiting = false; MyProc->lwExclusive = false; MyProc->lwWaitLink = NULL; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 6df4546538d..546aa49c2d0 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.241 2005/07/14 05:13:41 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.242 2005/07/31 17:19:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -275,6 +275,7 @@ check_xact_readonly(Node *parsetree) switch (nodeTag(parsetree)) { + case T_AlterDatabaseStmt: case T_AlterDatabaseSetStmt: case T_AlterDomainStmt: case T_AlterFunctionStmt: @@ -788,6 +789,10 @@ ProcessUtility(Node *parsetree, createdb((CreatedbStmt *) parsetree); break; + case T_AlterDatabaseStmt: + AlterDatabase((AlterDatabaseStmt *) parsetree); + break; + case T_AlterDatabaseSetStmt: AlterDatabaseSet((AlterDatabaseSetStmt *) parsetree); break; @@ -1504,6 +1509,10 @@ CreateCommandTag(Node *parsetree) tag = "CREATE DATABASE"; break; + case T_AlterDatabaseStmt: + tag = "ALTER DATABASE"; + break; + case T_AlterDatabaseSetStmt: tag = "ALTER DATABASE"; break; diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c index 66d6d1725e0..f6c50438e03 100644 --- a/src/backend/utils/init/miscinit.c +++ b/src/backend/utils/init/miscinit.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/init/miscinit.c,v 1.147 2005/07/25 22:12:33 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/init/miscinit.c,v 1.148 2005/07/31 17:19:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -36,6 +36,8 @@ #include "storage/fd.h" #include "storage/ipc.h" #include "storage/pg_shmem.h" +#include "storage/proc.h" +#include "storage/procarray.h" #include "utils/builtins.h" #include "utils/guc.h" #include "utils/lsyscache.h" @@ -404,17 +406,52 @@ InitializeSessionUserId(const char *rolename) rform = (Form_pg_authid) GETSTRUCT(roleTup); roleid = HeapTupleGetOid(roleTup); - if (!rform->rolcanlogin) - ereport(FATAL, - (errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION), - errmsg("role \"%s\" is not permitted to log in", rolename))); - AuthenticatedUserId = roleid; AuthenticatedUserIsSuperuser = rform->rolsuper; /* This sets OuterUserId/CurrentUserId too */ SetSessionUserId(roleid, AuthenticatedUserIsSuperuser); + /* Also mark our PGPROC entry with the authenticated user id */ + /* (We assume this is an atomic store so no lock is needed) */ + MyProc->roleId = roleid; + + /* + * These next checks are not enforced when in standalone mode, so that + * there is a way to recover from sillinesses like + * "UPDATE pg_authid SET rolcanlogin = false;". + * + * We do not enforce them for the autovacuum process either. + */ + if (IsUnderPostmaster && !IsAutoVacuumProcess()) + { + /* + * Is role allowed to login at all? + */ + if (!rform->rolcanlogin) + ereport(FATAL, + (errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION), + errmsg("role \"%s\" is not permitted to log in", + rolename))); + /* + * Check connection limit for this role. + * + * There is a race condition here --- we create our PGPROC before + * checking for other PGPROCs. If two backends did this at about the + * same time, they might both think they were over the limit, while + * ideally one should succeed and one fail. Getting that to work + * exactly seems more trouble than it is worth, however; instead + * we just document that the connection limit is approximate. + */ + if (rform->rolconnlimit >= 0 && + !AuthenticatedUserIsSuperuser && + CountUserBackends(roleid) > rform->rolconnlimit) + ereport(FATAL, + (errcode(ERRCODE_TOO_MANY_CONNECTIONS), + errmsg("too many connections for role \"%s\"", + rolename))); + } + /* Record username and superuser status as GUC settings too */ SetConfigOption("session_authorization", rolename, PGC_BACKEND, PGC_S_OVERRIDE); diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index b013eca86cf..d06fa5db369 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/init/postinit.c,v 1.154 2005/07/29 19:30:05 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/init/postinit.c,v 1.155 2005/07/31 17:19:19 tgl Exp $ * * *------------------------------------------------------------------------- @@ -169,18 +169,43 @@ ReverifyMyDatabase(const char *name) name, MyDatabaseId))); } + dbform = (Form_pg_database) GETSTRUCT(tup); + /* - * Also check that the database is currently allowing connections. - * (We do not enforce this in standalone mode, however, so that there is - * a way to recover from "UPDATE pg_database SET datallowconn = false;". - * We do not enforce it for the autovacuum process either.) + * These next checks are not enforced when in standalone mode, so that + * there is a way to recover from disabling all access to all databases, + * for example "UPDATE pg_database SET datallowconn = false;". + * + * We do not enforce them for the autovacuum process either. */ - dbform = (Form_pg_database) GETSTRUCT(tup); - if (IsUnderPostmaster && !IsAutoVacuumProcess() && !dbform->datallowconn) - ereport(FATAL, - (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), - errmsg("database \"%s\" is not currently accepting connections", - name))); + if (IsUnderPostmaster && !IsAutoVacuumProcess()) + { + /* + * Check that the database is currently allowing connections. + */ + if (!dbform->datallowconn) + ereport(FATAL, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("database \"%s\" is not currently accepting connections", + name))); + /* + * Check connection limit for this database. + * + * There is a race condition here --- we create our PGPROC before + * checking for other PGPROCs. If two backends did this at about the + * same time, they might both think they were over the limit, while + * ideally one should succeed and one fail. Getting that to work + * exactly seems more trouble than it is worth, however; instead + * we just document that the connection limit is approximate. + */ + if (dbform->datconnlimit >= 0 && + !superuser() && + CountDBBackends(MyDatabaseId) > dbform->datconnlimit) + ereport(FATAL, + (errcode(ERRCODE_TOO_MANY_CONNECTIONS), + errmsg("too many connections for database \"%s\"", + name))); + } /* * OK, we're golden. Next to-do item is to save the encoding diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index 498c598e2b5..28032c47b93 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -1,18 +1,19 @@ /*------------------------------------------------------------------------- * - * pg_dumpall + * pg_dumpall.c * * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * * - * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.65 2005/07/25 04:52:32 tgl Exp $ + * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.66 2005/07/31 17:19:19 tgl Exp $ * *------------------------------------------------------------------------- */ #include "postgres_fe.h" +#include <time.h> #include <unistd.h> #ifdef ENABLE_NLS #include <locale.h> @@ -20,8 +21,6 @@ #ifndef HAVE_STRDUP #include "strdup.h" #endif -#include <errno.h> -#include <time.h> #include "getopt_long.h" @@ -43,7 +42,8 @@ static const char *progname; static void help(void); -static void dumpUsers(PGconn *conn, bool initdbonly); +static void dumpRoles(PGconn *conn, bool initdbonly); +static void dumpRoleMembership(PGconn *conn); static void dumpGroups(PGconn *conn); static void dumpTablespaces(PGconn *conn); static void dumpCreateDB(PGconn *conn); @@ -57,19 +57,20 @@ static int runPgDump(const char *dbname); static PGconn *connectDatabase(const char *dbname, const char *pghost, const char *pgport, const char *pguser, bool require_password, bool fail_on_error); static PGresult *executeQuery(PGconn *conn, const char *query); - -char pg_dump_bin[MAXPGPATH]; -PQExpBuffer pgdumpopts; -bool output_clean = false; -bool skip_acls = false; -bool verbose = false; -static bool ignoreVersion = false; -int server_version; - +static void executeCommand(PGconn *conn, const char *query); + +static char pg_dump_bin[MAXPGPATH]; +static PQExpBuffer pgdumpopts; +static bool output_clean = false; +static bool skip_acls = false; +static bool verbose = false; +static bool ignoreVersion = false; /* flags for -X long options */ -int disable_dollar_quoting = 0; -int disable_triggers = 0; -int use_setsessauth = 0; +static int disable_dollar_quoting = 0; +static int disable_triggers = 0; +static int use_setsessauth = 0; +static int server_version; + int main(int argc, char *argv[]) @@ -316,14 +317,22 @@ main(int argc, char *argv[]) if (!data_only) { /* Dump all users excluding the initdb user */ - dumpUsers(conn, false); - dumpGroups(conn); + dumpRoles(conn, false); + + /* Dump role memberships --- need different method for pre-8.1 */ + if (server_version >= 80100) + dumpRoleMembership(conn); + else + dumpGroups(conn); + if (server_version >= 80000) dumpTablespaces(conn); + if (!globals_only) dumpCreateDB(conn); + /* Dump alter command for initdb user */ - dumpUsers(conn, true); + dumpRoles(conn, true); } if (!globals_only) @@ -384,81 +393,151 @@ help(void) /* - * Dump users + * Dump roles + * * Is able to dump all non initdb users or just the initdb user. */ static void -dumpUsers(PGconn *conn, bool initdbonly) +dumpRoles(PGconn *conn, bool initdbonly) { PQExpBuffer buf = createPQExpBuffer(); PGresult *res; + int i_rolname, + i_rolsuper, + i_rolinherit, + i_rolcreaterole, + i_rolcreatedb, + i_rolcatupdate, + i_rolcanlogin, + i_rolconnlimit, + i_rolpassword, + i_rolvaliduntil, + i_clusterowner; int i; - if (server_version >= 70100) - res = executeQuery(conn, - "SELECT usename, usesysid, passwd, usecreatedb, " - "usesuper, valuntil, " - "(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner " - "FROM pg_shadow"); + /* note: rolconfig is dumped later */ + if (server_version >= 80100) + printfPQExpBuffer(buf, + "SELECT rolname, rolsuper, rolinherit, " + "rolcreaterole, rolcreatedb, rolcatupdate, " + "rolcanlogin, rolconnlimit, rolpassword, " + "rolvaliduntil, " + "(oid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner " + "FROM pg_authid"); else - res = executeQuery(conn, - "SELECT usename, usesysid, passwd, usecreatedb, " - "usesuper, valuntil, " - "(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template1')) AS clusterowner " - "FROM pg_shadow"); + printfPQExpBuffer(buf, + "SELECT usename as rolname, " + "usesuper as rolsuper, " + "true as rolinherit, " + "usesuper as rolcreaterole, " + "usecreatedb as rolcreatedb, " + "usecatupd as rolcatupdate, " + "true as rolcanlogin, " + "-1 as rolconnlimit, " + "passwd as rolpassword, " + "valuntil as rolvaliduntil, " + "(usesysid = (SELECT datdba FROM pg_database WHERE datname = '%s')) AS clusterowner " + "FROM pg_shadow " + "UNION ALL " + "SELECT groname as rolname, " + "false as rolsuper, " + "true as rolinherit, " + "false as rolcreaterole, " + "false as rolcreatedb, " + "false as rolcatupdate, " + "false as rolcanlogin, " + "-1 as rolconnlimit, " + "null::text as rolpassword, " + "null::abstime as rolvaliduntil, " + "false AS clusterowner " + "FROM pg_group", + (server_version >= 70100) ? "template0" : "template1"); + + res = executeQuery(conn, buf->data); + + i_rolname = PQfnumber(res, "rolname"); + i_rolsuper = PQfnumber(res, "rolsuper"); + i_rolinherit = PQfnumber(res, "rolinherit"); + i_rolcreaterole = PQfnumber(res, "rolcreaterole"); + i_rolcreatedb = PQfnumber(res, "rolcreatedb"); + i_rolcatupdate = PQfnumber(res, "rolcatupdate"); + i_rolcanlogin = PQfnumber(res, "rolcanlogin"); + i_rolconnlimit = PQfnumber(res, "rolconnlimit"); + i_rolpassword = PQfnumber(res, "rolpassword"); + i_rolvaliduntil = PQfnumber(res, "rolvaliduntil"); + i_clusterowner = PQfnumber(res, "clusterowner"); if (PQntuples(res) > 0 || (!initdbonly && output_clean)) - printf("--\n-- Users\n--\n\n"); + printf("--\n-- Roles\n--\n\n"); if (!initdbonly && output_clean) printf("DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');\n\n"); for (i = 0; i < PQntuples(res); i++) { - const char *username; + const char *rolename; bool clusterowner; - username = PQgetvalue(res, i, 0); - clusterowner = (strcmp(PQgetvalue(res, i, 6), "t") == 0); + rolename = PQgetvalue(res, i, i_rolname); + clusterowner = (strcmp(PQgetvalue(res, i, i_clusterowner), "t") == 0); /* Check which pass we're on */ if ((initdbonly && !clusterowner) || (!initdbonly && clusterowner)) continue; /* - * Dump ALTER USER for the cluster owner and CREATE USER for all - * other users + * Dump ALTER ROLE for the cluster owner and CREATE ROLE for all + * other roles */ if (!clusterowner) - printfPQExpBuffer(buf, "CREATE USER %s WITH", fmtId(username)); + printfPQExpBuffer(buf, "CREATE ROLE %s WITH", fmtId(rolename)); else - printfPQExpBuffer(buf, "ALTER USER %s WITH", fmtId(username)); + printfPQExpBuffer(buf, "ALTER ROLE %s WITH", fmtId(rolename)); - if (!PQgetisnull(res, i, 2)) - { - appendPQExpBuffer(buf, " PASSWORD "); - appendStringLiteral(buf, PQgetvalue(res, i, 2), true); - } + if (strcmp(PQgetvalue(res, i, i_rolsuper), "t") == 0) + appendPQExpBuffer(buf, " SUPERUSER"); + else + appendPQExpBuffer(buf, " NOSUPERUSER"); - if (strcmp(PQgetvalue(res, i, 3), "t") == 0) + if (strcmp(PQgetvalue(res, i, i_rolinherit), "t") == 0) + appendPQExpBuffer(buf, " INHERIT"); + else + appendPQExpBuffer(buf, " NOINHERIT"); + + if (strcmp(PQgetvalue(res, i, i_rolcreaterole), "t") == 0) + appendPQExpBuffer(buf, " CREATEROLE"); + else + appendPQExpBuffer(buf, " NOCREATEROLE"); + + if (strcmp(PQgetvalue(res, i, i_rolcreatedb), "t") == 0) appendPQExpBuffer(buf, " CREATEDB"); else appendPQExpBuffer(buf, " NOCREATEDB"); - if (strcmp(PQgetvalue(res, i, 4), "t") == 0) - appendPQExpBuffer(buf, " CREATEUSER"); + if (strcmp(PQgetvalue(res, i, i_rolcanlogin), "t") == 0) + appendPQExpBuffer(buf, " LOGIN"); else - appendPQExpBuffer(buf, " NOCREATEUSER"); + appendPQExpBuffer(buf, " NOLOGIN"); + + if (strcmp(PQgetvalue(res, i, i_rolconnlimit), "-1") != 0) + appendPQExpBuffer(buf, " CONNECTION LIMIT %s", + PQgetvalue(res, i, i_rolconnlimit)); - if (!PQgetisnull(res, i, 5)) + if (!PQgetisnull(res, i, i_rolpassword)) + { + appendPQExpBuffer(buf, " PASSWORD "); + appendStringLiteral(buf, PQgetvalue(res, i, i_rolpassword), true); + } + + if (!PQgetisnull(res, i, i_rolvaliduntil)) appendPQExpBuffer(buf, " VALID UNTIL '%s'", - PQgetvalue(res, i, 5)); + PQgetvalue(res, i, i_rolvaliduntil)); appendPQExpBuffer(buf, ";\n"); printf("%s", buf->data); if (server_version >= 70300) - dumpUserConfig(conn, username); + dumpUserConfig(conn, rolename); } PQclear(res); @@ -469,63 +548,109 @@ dumpUsers(PGconn *conn, bool initdbonly) } +/* + * Dump role memberships. This code is used for 8.1 and later servers. + * + * Note: we expect dumpRoles already created all the roles, but there is + * no membership yet. + */ +static void +dumpRoleMembership(PGconn *conn) +{ + PGresult *res; + int i; + + res = executeQuery(conn, "SELECT ur.rolname AS roleid, " + "um.rolname AS member, " + "ug.rolname AS grantor, " + "a.admin_option " + "FROM pg_auth_members a " + "LEFT JOIN pg_authid ur on ur.oid = a.roleid " + "LEFT JOIN pg_authid um on um.oid = a.member " + "LEFT JOIN pg_authid ug on ug.oid = a.grantor"); + + if (PQntuples(res) > 0 || output_clean) + printf("--\n-- Role memberships\n--\n\n"); + if (output_clean) + printf("DELETE FROM pg_auth_members;\n\n"); + + for (i = 0; i < PQntuples(res); i++) + { + char *roleid = PQgetvalue(res, i, 0); + char *member = PQgetvalue(res, i, 1); + char *grantor = PQgetvalue(res, i, 2); + char *option = PQgetvalue(res, i, 3); + + printf("GRANT %s", fmtId(roleid)); + printf(" TO %s", fmtId(member)); + if (*option == 't') + printf(" WITH ADMIN OPTION"); + printf(" GRANTED BY %s;\n", fmtId(grantor)); + } + + PQclear(res); + + printf("\n\n"); +} /* - * Dump groups. + * Dump group memberships from a pre-8.1 server. It's annoying that we + * can't share any useful amount of code with the post-8.1 case, but + * the catalog representations are too different. + * + * Note: we expect dumpRoles already created all the roles, but there is + * no membership yet. */ static void dumpGroups(PGconn *conn) { + PQExpBuffer buf = createPQExpBuffer(); PGresult *res; int i; res = executeQuery(conn, "SELECT groname, grolist FROM pg_group"); if (PQntuples(res) > 0 || output_clean) - printf("--\n-- Groups\n--\n\n"); + printf("--\n-- Role memberships\n--\n\n"); if (output_clean) - printf("DELETE FROM pg_group;\n\n"); + printf("DELETE FROM pg_auth_members;\n\n"); for (i = 0; i < PQntuples(res); i++) { - PQExpBuffer buf = createPQExpBuffer(); + char *groname = PQgetvalue(res, i, 0); char *val; char *tok; - appendPQExpBuffer(buf, "CREATE GROUP %s;\n", - fmtId(PQgetvalue(res, i, 0))); - val = strdup(PQgetvalue(res, i, 1)); + tok = strtok(val, ",{}"); while (tok) { PGresult *res2; - PQExpBuffer buf2 = createPQExpBuffer(); int j; - appendPQExpBuffer(buf2, "SELECT usename FROM pg_shadow WHERE usesysid = %s;", tok); - res2 = executeQuery(conn, buf2->data); - destroyPQExpBuffer(buf2); + printfPQExpBuffer(buf, + "SELECT usename FROM pg_shadow WHERE usesysid = %s", + tok); + + res2 = executeQuery(conn, buf->data); for (j = 0; j < PQntuples(res2); j++) { - appendPQExpBuffer(buf, "ALTER GROUP %s ", - fmtId(PQgetvalue(res, i, 0))); - appendPQExpBuffer(buf, "ADD USER %s;\n", - fmtId(PQgetvalue(res2, j, 0))); + printf("GRANT %s", fmtId(groname)); + printf(" TO %s;\n", fmtId(PQgetvalue(res2, j, 0))); } PQclear(res2); - tok = strtok(NULL, "{},"); + tok = strtok(NULL, ",{}"); } free(val); - - printf("%s", buf->data); - destroyPQExpBuffer(buf); } PQclear(res); + destroyPQExpBuffer(buf); + printf("\n\n"); } @@ -607,17 +732,27 @@ dumpTablespaces(PGconn *conn) static void dumpCreateDB(PGconn *conn) { + PQExpBuffer buf = createPQExpBuffer(); PGresult *res; int i; printf("--\n-- Database creation\n--\n\n"); - if (server_version >= 80000) + if (server_version >= 80100) + res = executeQuery(conn, + "SELECT datname, " + "coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), " + "pg_encoding_to_char(d.encoding), " + "datistemplate, datacl, datconnlimit, " + "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace " + "FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) " + "WHERE datallowconn ORDER BY 1"); + else if (server_version >= 80000) res = executeQuery(conn, "SELECT datname, " "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), " "pg_encoding_to_char(d.encoding), " - "datistemplate, datacl, " + "datistemplate, datacl, -1 as datconnlimit, " "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace " "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) " "WHERE datallowconn ORDER BY 1"); @@ -626,7 +761,7 @@ dumpCreateDB(PGconn *conn) "SELECT datname, " "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), " "pg_encoding_to_char(d.encoding), " - "datistemplate, datacl, " + "datistemplate, datacl, -1 as datconnlimit, " "'pg_default' AS dattablespace " "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) " "WHERE datallowconn ORDER BY 1"); @@ -637,7 +772,7 @@ dumpCreateDB(PGconn *conn) "(select usename from pg_shadow where usesysid=datdba), " "(select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), " "pg_encoding_to_char(d.encoding), " - "datistemplate, '' as datacl, " + "datistemplate, '' as datacl, -1 as datconnlimit, " "'pg_default' AS dattablespace " "FROM pg_database d " "WHERE datallowconn ORDER BY 1"); @@ -652,7 +787,7 @@ dumpCreateDB(PGconn *conn) "(select usename from pg_shadow where usesysid=datdba), " "pg_encoding_to_char(d.encoding), " "'f' as datistemplate, " - "'' as datacl, " + "'' as datacl, -1 as datconnlimit, " "'pg_default' AS dattablespace " "FROM pg_database d " "ORDER BY 1"); @@ -660,18 +795,19 @@ dumpCreateDB(PGconn *conn) for (i = 0; i < PQntuples(res); i++) { - PQExpBuffer buf; char *dbname = PQgetvalue(res, i, 0); char *dbowner = PQgetvalue(res, i, 1); char *dbencoding = PQgetvalue(res, i, 2); char *dbistemplate = PQgetvalue(res, i, 3); char *dbacl = PQgetvalue(res, i, 4); - char *dbtablespace = PQgetvalue(res, i, 5); + char *dbconnlimit = PQgetvalue(res, i, 5); + char *dbtablespace = PQgetvalue(res, i, 6); char *fdbname; - buf = createPQExpBuffer(); fdbname = strdup(fmtId(dbname)); + resetPQExpBuffer(buf); + /* * Skip the CREATE DATABASE commands for "template1" and "postgres", * since they are presumably already there in the destination cluster. @@ -698,6 +834,10 @@ dumpCreateDB(PGconn *conn) appendPQExpBuffer(buf, " TABLESPACE = %s", fmtId(dbtablespace)); + if (strcmp(dbconnlimit, "-1") != 0) + appendPQExpBuffer(buf, " CONNECTION LIMIT = %s", + dbconnlimit); + appendPQExpBuffer(buf, ";\n"); if (strcmp(dbistemplate, "t") == 0) @@ -723,11 +863,12 @@ dumpCreateDB(PGconn *conn) if (server_version >= 70300) dumpDatabaseConfig(conn, dbname); - destroyPQExpBuffer(buf); free(fdbname); } PQclear(res); + destroyPQExpBuffer(buf); + printf("\n\n"); } @@ -782,14 +923,17 @@ dumpUserConfig(PGconn *conn, const char *username) { PGresult *res; - printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count); + if (server_version >= 80100) + printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM pg_authid WHERE rolname = ", count); + else + printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count); appendStringLiteral(buf, username, true); appendPQExpBuffer(buf, ";"); res = executeQuery(conn, buf->data); if (!PQgetisnull(res, 0, 0)) { - makeAlterConfigCommand(PQgetvalue(res, 0, 0), "USER", username); + makeAlterConfigCommand(PQgetvalue(res, 0, 0), "ROLE", username); PQclear(res); count++; } @@ -1041,11 +1185,17 @@ connectDatabase(const char *dbname, const char *pghost, const char *pgport, } } + /* + * On 7.3 and later, make sure we are not fooled by non-system schemas + * in the search path. + */ + if (server_version >= 70300) + executeCommand(conn, "SET search_path = pg_catalog"); + return conn; } - /* * Run a query, return the results, exit program on failure. */ @@ -1061,8 +1211,10 @@ executeQuery(PGconn *conn, const char *query) if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { - fprintf(stderr, _("%s: query failed: %s"), progname, PQerrorMessage(conn)); - fprintf(stderr, _("%s: query was: %s\n"), progname, query); + fprintf(stderr, _("%s: query failed: %s"), + progname, PQerrorMessage(conn)); + fprintf(stderr, _("%s: query was: %s\n"), + progname, query); PQfinish(conn); exit(1); } @@ -1070,6 +1222,32 @@ executeQuery(PGconn *conn, const char *query) return res; } +/* + * As above for a SQL command (which returns nothing). + */ +static void +executeCommand(PGconn *conn, const char *query) +{ + PGresult *res; + + if (verbose) + fprintf(stderr, _("%s: executing %s\n"), progname, query); + + res = PQexec(conn, query); + if (!res || + PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, _("%s: query failed: %s"), + progname, PQerrorMessage(conn)); + fprintf(stderr, _("%s: query was: %s\n"), + progname, query); + PQfinish(conn); + exit(1); + } + + PQclear(res); +} + /* * dumpTimestamp diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index ec2813be898..c7584968a36 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.293 2005/07/29 15:04:22 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.294 2005/07/31 17:19:20 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200507291 +#define CATALOG_VERSION_NO 200507301 #endif diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h index 6672138d865..bcdee017d15 100644 --- a/src/include/catalog/pg_authid.h +++ b/src/include/catalog/pg_authid.h @@ -10,7 +10,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_authid.h,v 1.2 2005/07/26 16:38:28 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_authid.h,v 1.3 2005/07/31 17:19:21 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -49,6 +49,7 @@ CATALOG(pg_authid,1260) BKI_SHARED_RELATION bool rolcreatedb; /* allowed to create databases? */ bool rolcatupdate; /* allowed to alter catalogs manually? */ bool rolcanlogin; /* allowed to log in as session user? */ + int4 rolconnlimit; /* max connections allowed (-1=no limit) */ /* remaining fields may be null; use heap_getattr to read them! */ text rolpassword; /* password, if any */ @@ -70,7 +71,7 @@ typedef FormData_pg_authid *Form_pg_authid; * compiler constants for pg_authid * ---------------- */ -#define Natts_pg_authid 10 +#define Natts_pg_authid 11 #define Anum_pg_authid_rolname 1 #define Anum_pg_authid_rolsuper 2 #define Anum_pg_authid_rolinherit 3 @@ -78,9 +79,10 @@ typedef FormData_pg_authid *Form_pg_authid; #define Anum_pg_authid_rolcreatedb 5 #define Anum_pg_authid_rolcatupdate 6 #define Anum_pg_authid_rolcanlogin 7 -#define Anum_pg_authid_rolpassword 8 -#define Anum_pg_authid_rolvaliduntil 9 -#define Anum_pg_authid_rolconfig 10 +#define Anum_pg_authid_rolconnlimit 8 +#define Anum_pg_authid_rolpassword 9 +#define Anum_pg_authid_rolvaliduntil 10 +#define Anum_pg_authid_rolconfig 11 /* ---------------- * initial contents of pg_authid @@ -89,7 +91,7 @@ typedef FormData_pg_authid *Form_pg_authid; * user choices. * ---------------- */ -DATA(insert OID = 10 ( "POSTGRES" t t t t t t _null_ _null_ _null_ )); +DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ _null_ )); #define BOOTSTRAP_SUPERUSERID 10 diff --git a/src/include/catalog/pg_database.h b/src/include/catalog/pg_database.h index 37c57f8508c..5ad5737825e 100644 --- a/src/include/catalog/pg_database.h +++ b/src/include/catalog/pg_database.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_database.h,v 1.36 2005/06/28 05:09:06 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_database.h,v 1.37 2005/07/31 17:19:21 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -40,6 +40,7 @@ CATALOG(pg_database,1262) BKI_SHARED_RELATION int4 encoding; /* character encoding */ bool datistemplate; /* allowed as CREATE DATABASE template? */ bool datallowconn; /* new connections allowed? */ + int4 datconnlimit; /* max connections allowed (-1=no limit) */ Oid datlastsysoid; /* highest OID to consider a system OID */ TransactionId datvacuumxid; /* all XIDs before this are vacuumed */ TransactionId datfrozenxid; /* all XIDs before this are frozen */ @@ -59,20 +60,21 @@ typedef FormData_pg_database *Form_pg_database; * compiler constants for pg_database * ---------------- */ -#define Natts_pg_database 11 +#define Natts_pg_database 12 #define Anum_pg_database_datname 1 #define Anum_pg_database_datdba 2 #define Anum_pg_database_encoding 3 #define Anum_pg_database_datistemplate 4 #define Anum_pg_database_datallowconn 5 -#define Anum_pg_database_datlastsysoid 6 -#define Anum_pg_database_datvacuumxid 7 -#define Anum_pg_database_datfrozenxid 8 -#define Anum_pg_database_dattablespace 9 -#define Anum_pg_database_datconfig 10 -#define Anum_pg_database_datacl 11 +#define Anum_pg_database_datconnlimit 6 +#define Anum_pg_database_datlastsysoid 7 +#define Anum_pg_database_datvacuumxid 8 +#define Anum_pg_database_datfrozenxid 9 +#define Anum_pg_database_dattablespace 10 +#define Anum_pg_database_datconfig 11 +#define Anum_pg_database_datacl 12 -DATA(insert OID = 1 ( template1 PGUID ENCODING t t 0 0 0 1663 _null_ _null_ )); +DATA(insert OID = 1 ( template1 PGUID ENCODING t t -1 0 0 0 1663 _null_ _null_ )); DESCR("Default template database"); #define TemplateDbOid 1 diff --git a/src/include/commands/dbcommands.h b/src/include/commands/dbcommands.h index 8020fc7e4e8..7770ea9b97e 100644 --- a/src/include/commands/dbcommands.h +++ b/src/include/commands/dbcommands.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/commands/dbcommands.h,v 1.40 2005/07/08 04:12:27 neilc Exp $ + * $PostgreSQL: pgsql/src/include/commands/dbcommands.h,v 1.41 2005/07/31 17:19:21 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -55,6 +55,7 @@ typedef struct xl_dbase_drop_rec extern void createdb(const CreatedbStmt *stmt); extern void dropdb(const char *dbname); extern void RenameDatabase(const char *oldname, const char *newname); +extern void AlterDatabase(AlterDatabaseStmt *stmt); extern void AlterDatabaseSet(AlterDatabaseSetStmt *stmt); extern void AlterDatabaseOwner(const char *dbname, Oid newOwnerId); diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 3e623911c70..a488d8dce71 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/nodes.h,v 1.172 2005/06/28 05:09:13 tgl Exp $ + * $PostgreSQL: pgsql/src/include/nodes/nodes.h,v 1.173 2005/07/31 17:19:21 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -270,6 +270,7 @@ typedef enum NodeTag T_ReindexStmt, T_CheckPointStmt, T_CreateSchemaStmt, + T_AlterDatabaseStmt, T_AlterDatabaseSetStmt, T_AlterRoleSetStmt, T_CreateConversionStmt, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 6d388b07d31..68262d28f7c 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.286 2005/07/26 16:38:28 tgl Exp $ + * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.287 2005/07/31 17:19:21 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1624,6 +1624,13 @@ typedef struct CreatedbStmt * Alter Database * ---------------------- */ +typedef struct AlterDatabaseStmt +{ + NodeTag type; + char *dbname; /* name of database to alter */ + List *options; /* List of DefElem nodes */ +} AlterDatabaseStmt; + typedef struct AlterDatabaseSetStmt { NodeTag type; diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index ece321028fc..63090de1cfd 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/storage/proc.h,v 1.79 2005/06/17 22:32:50 tgl Exp $ + * $PostgreSQL: pgsql/src/include/storage/proc.h,v 1.80 2005/07/31 17:19:22 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -71,6 +71,7 @@ struct PGPROC int pid; /* This backend's process id, or 0 */ Oid databaseId; /* OID of database this backend is using */ + Oid roleId; /* OID of role using this backend */ /* Info about LWLock the process is currently waiting for, if any. */ bool lwWaiting; /* true if waiting for an LW lock */ diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h index d1780bcca18..aa0adfec881 100644 --- a/src/include/storage/procarray.h +++ b/src/include/storage/procarray.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/storage/procarray.h,v 1.2 2005/06/17 22:32:50 tgl Exp $ + * $PostgreSQL: pgsql/src/include/storage/procarray.h,v 1.3 2005/07/31 17:19:22 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -31,6 +31,8 @@ extern bool IsBackendPid(int pid); extern bool DatabaseHasActiveBackends(Oid databaseId, bool ignoreMyself); extern int CountActiveBackends(void); +extern int CountDBBackends(Oid databaseid); +extern int CountUserBackends(Oid roleid); extern void XidCacheRemoveRunningXids(TransactionId xid, int nxids, TransactionId *xids); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index e204864c3ce..55b0653a3be 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1281,7 +1281,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS "tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); pg_locks | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid, l.objid, l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database" oid, relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint, "transaction" xid, pid integer, "mode" text, "granted" boolean); pg_prepared_xacts | SELECT p."transaction", p.gid, p."prepared", u.rolname AS "owner", d.datname AS "database" FROM ((pg_prepared_xact() p("transaction" xid, gid text, "prepared" timestamp with time zone, ownerid oid, dbid oid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid))); - pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid; + pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid; pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); pg_settings | SELECT a.name, a.setting, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings() a(name text, setting text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text); pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid.rolconfig AS useconfig FROM pg_authid WHERE pg_authid.rolcanlogin; |