Re: per user/database connections limit again

We will need these:

> Patch includes only changes to backend, I will make pg_dump, ecpg and
> documentation patches once this is completed and accepted by team.

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Petr Jelinek wrote:
> Hi,
>
> I attached second try of per-database and per-user connection limit for
> your review.
>
> This time I am using information stored in ProcArray to get number of
> connections - I modified PGPROC struct to also include userid.
>
> Limits for user and database are stored in catalog tables. This aproach
> led to implementation of "universal" ALTER DATABASE query (I followed
> ALTER USER ad ALTER DATABASE ... RENAME implementatons). So queries for
> setting maximum connections look like this: CREATE|ALTER DATABASE|USER
> name MAX CONNECTIONS = 20;
> Maximum connections defaults to zero which means unlimited (limited by
> global maximum only) and isn't enforced for superusers.
>
> The actual check for maximum conenctions is done in ReverifyMyDatabase
> for database and InitializeSessionUser for user because we don't have
> information from system catalog before so we don't know how many
> connections are allowed.
>
> Patch includes only changes to backend, I will make pg_dump, ecpg and
> documentation patches once this is completed and accepted by team.
>
> Diff is made against cvs from today morning GMT (apply with -p1 if you
> want to test it) - cvs is down now so I can't make diff against repository.
>
> --
> Regards
> Petr Jelinek (PJMODOS)
>
>

> diff -Nacr my-cvs/src/backend/commands/dbcommands.c my-aproach2/src/backend/commands/dbcommands.c
> *** my-cvs/src/backend/commands/dbcommands.c Sun Jun 26 00:47:30 2005
> --- my-aproach2/src/backend/commands/dbcommands.c Tue Jun 28 11:26:08 2005
> ***************
> *** 53,60 ****
>
> /* non-export function prototypes */
> static bool get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
> ! int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
> ! Oid *dbLastSysOidP,
> TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
> Oid *dbTablespace);
> static bool have_createdb_privilege(void);
> --- 53,60 ----
>
> /* non-export function prototypes */
> static bool get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
> ! int *encodingP, int *dbMaxConnP, bool *dbIsTemplateP,
> ! bool *dbAllowConnP, Oid *dbLastSysOidP,
> TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
> Oid *dbTablespace);
> static bool have_createdb_privilege(void);
> ***************
> *** 74,79 ****
> --- 74,80 ----
> int src_encoding;
> bool src_istemplate;
> bool src_allowconn;
> + int src_maxconn;
> Oid src_lastsysoid;
> TransactionId src_vacuumxid;
> TransactionId src_frozenxid;
> ***************
> *** 91,100 ****
> --- 92,103 ----
> DefElem *downer = NULL;
> DefElem *dtemplate = NULL;
> DefElem *dencoding = NULL;
> + DefElem *dmaxconn = NULL;
> char *dbname = stmt->dbname;
> char *dbowner = NULL;
> const char *dbtemplate = NULL;
> int encoding = -1;
> + int dbmaxconn = -1;
>
> #ifndef WIN32
> char buf[2 * MAXPGPATH + 100];
> ***************
> *** 140,145 ****
> --- 143,156 ----
> errmsg("conflicting or redundant options")));
> dencoding = defel;
> }
> + else if (strcmp(defel->defname, "maxconnections") == 0)
> + {
> + if (dmaxconn)
> + ereport(ERROR,
> + (errcode(ERRCODE_SYNTAX_ERROR),
> + errmsg("conflicting or redundant options")));
> + dmaxconn = defel;
> + }
> else if (strcmp(defel->defname, "location") == 0)
> {
> ereport(WARNING,
> ***************
> *** 185,190 ****
> --- 196,203 ----
> elog(ERROR, "unrecognized node type: %d",
> nodeTag(dencoding->arg));
> }
> + if (dmaxconn && dmaxconn->arg)
> + dbmaxconn = intVal(dmaxconn->arg);
>
> /* obtain sysid of proposed owner */
> if (dbowner)
> ***************
> *** 218,224 ****
> * idea, so accept possibility of race to create. We will check again
> * after we grab the exclusive lock.
> */
> ! if (get_db_info(dbname, NULL, NULL, NULL,
> NULL, NULL, NULL, NULL, NULL, NULL))
> ereport(ERROR,
> (errcode(ERRCODE_DUPLICATE_DATABASE),
> --- 231,237 ----
> * idea, so accept possibility of race to create. We will check again
> * after we grab the exclusive lock.
> */
> ! if (get_db_info(dbname, NULL, NULL, NULL, NULL,
> NULL, NULL, NULL, NULL, NULL, NULL))
> ereport(ERROR,
> (errcode(ERRCODE_DUPLICATE_DATABASE),
> ***************
> *** 231,238 ****
> dbtemplate = "template1"; /* Default template database name */
>
> if (!get_db_info(dbtemplate, &src_dboid, &src_owner, &src_encoding,
> ! &src_istemplate, &src_allowconn, &src_lastsysoid,
> ! &src_vacuumxid, &src_frozenxid, &src_deftablespace))
> ereport(ERROR,
> (errcode(ERRCODE_UNDEFINED_DATABASE),
> errmsg("template database \"%s\" does not exist", dbtemplate)));
> --- 244,252 ----
> dbtemplate = "template1"; /* Default template database name */
>
> if (!get_db_info(dbtemplate, &src_dboid, &src_owner, &src_encoding,
> ! &src_maxconn, &src_istemplate, &src_allowconn,
> ! &src_lastsysoid, &src_vacuumxid, &src_frozenxid,
> ! &src_deftablespace))
> ereport(ERROR,
> (errcode(ERRCODE_UNDEFINED_DATABASE),
> errmsg("template database \"%s\" does not exist", dbtemplate)));
> ***************
> *** 266,271 ****
> --- 280,289 ----
> if (encoding < 0)
> encoding = src_encoding;
>
> + /* If dbmaxconn is defaulted, use source's dbmaxconn */
> + if (dbmaxconn < 0)
> + dbmaxconn = src_maxconn;
> +
> /* Some encodings are client only */
> if (!PG_VALID_BE_ENCODING(encoding))
> ereport(ERROR,
> ***************
> *** 461,467 ****
> pg_database_rel = heap_open(DatabaseRelationId, ExclusiveLock);
>
> /* Check to see if someone else created same DB name meanwhile. */
> ! if (get_db_info(dbname, NULL, NULL, NULL,
> NULL, NULL, NULL, NULL, NULL, NULL))
> {
> /* Don't hold lock while doing recursive remove */
> --- 479,485 ----
> pg_database_rel = heap_open(DatabaseRelationId, ExclusiveLock);
>
> /* Check to see if someone else created same DB name meanwhile. */
> ! if (get_db_info(dbname, NULL, NULL, NULL, NULL,
> NULL, NULL, NULL, NULL, NULL, NULL))
> {
> /* Don't hold lock while doing recursive remove */
> ***************
> *** 487,492 ****
> --- 505,511 ----
> 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_datmaxconn - 1] = Int32GetDatum(dbmaxconn);
> 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);
> ***************
> *** 588,594 ****
> */
> pgdbrel = heap_open(DatabaseRelationId, ExclusiveLock);
>
> ! if (!get_db_info(dbname, &db_id, &db_owner, NULL,
> &db_istemplate, NULL, NULL, NULL, NULL, NULL))
> ereport(ERROR,
> (errcode(ERRCODE_UNDEFINED_DATABASE),
> --- 607,613 ----
> */
> pgdbrel = heap_open(DatabaseRelationId, ExclusiveLock);
>
> ! if (!get_db_info(dbname, &db_id, &db_owner, NULL, NULL,
> &db_istemplate, NULL, NULL, NULL, NULL, NULL))
> ereport(ERROR,
> (errcode(ERRCODE_UNDEFINED_DATABASE),
> ***************
> *** 784,789 ****
> --- 803,894 ----
>
>
> /*
> + * ALTER DATABASE name ...
> + */
> + void
> + AlterDatabase(AlterDatabaseStmt *stmt)
> + {
> + Datum new_record[Natts_pg_database];
> + char new_record_nulls[Natts_pg_database];
> + char new_record_repl[Natts_pg_database];
> + Relation rel;
> + HeapTuple tuple,
> + newtuple;
> + ScanKeyData scankey;
> + SysScanDesc scan;
> + ListCell *option;
> + int maxconn = -1; /* Maximum connections allowed */
> +
> + DefElem *dmaxconn = NULL;
> +
> + /* Extract options from the statement node tree */
> + foreach(option, stmt->options)
> + {
> + DefElem *defel = (DefElem *) lfirst(option);
> +
> + if (strcmp(defel->defname, "maxconnections") == 0)
> + {
> + if (dmaxconn)
> + ereport(ERROR,
> + (errcode(ERRCODE_SYNTAX_ERROR),
> + errmsg("conflicting or redundant options")));
> + dmaxconn = defel;
> + }
> + }
> +
> + if (dmaxconn)
> + maxconn = intVal(dmaxconn->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 (!(superuser()
> + || ((Form_pg_database) GETSTRUCT(tuple))->datdba == 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 (maxconn >= 0)
> + {
> + new_record[Anum_pg_database_datmaxconn - 1] = Int32GetDatum(maxconn);
> + new_record_repl[Anum_pg_database_datmaxconn - 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);
> + }
> +
> +
> + /*
> * ALTER DATABASE name SET ...
> */
> void
> ***************
> *** 973,980 ****
>
> static bool
> get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
> ! int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
> ! Oid *dbLastSysOidP,
> TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
> Oid *dbTablespace)
> {
> --- 1078,1085 ----
>
> static bool
> get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
> ! int *encodingP, int *dbMaxConnP, bool *dbIsTemplateP,
> ! bool *dbAllowConnP, Oid *dbLastSysOidP,
> TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
> Oid *dbTablespace)
> {
> ***************
> *** 1019,1024 ****
> --- 1124,1132 ----
> /* allowing connections? */
> if (dbAllowConnP)
> *dbAllowConnP = dbform->datallowconn;
> + /* maximum connections */
> + if (dbMaxConnP)
> + *dbMaxConnP = dbform->datmaxconn;
> /* last system OID used in database */
> if (dbLastSysOidP)
> *dbLastSysOidP = dbform->datlastsysoid;
> diff -Nacr my-cvs/src/backend/commands/user.c my-aproach2/src/backend/commands/user.c
> *** my-cvs/src/backend/commands/user.c Thu Apr 14 22:03:24 2005
> --- my-aproach2/src/backend/commands/user.c Tue Jun 28 11:26:18 2005
> ***************
> *** 64,69 ****
> --- 64,70 ----
> int sysid = 0; /* PgSQL system id (valid if havesysid) */
> bool createdb = false; /* Can the user create databases? */
> bool createuser = false; /* Can this user create users? */
> + int maxconn = false; /* maximum connections allowed */
> List *groupElts = NIL; /* The groups the user is a member of */
> char *validUntil = NULL; /* The time the login is valid
> * until */
> ***************
> *** 73,78 ****
> --- 74,80 ----
> DefElem *dcreateuser = NULL;
> DefElem *dgroupElts = NULL;
> DefElem *dvalidUntil = NULL;
> + DefElem *dmaxconn = NULL;
>
> /* Extract options from the statement node tree */
> foreach(option, stmt->options)
> ***************
> *** 117,122 ****
> --- 119,132 ----
> errmsg("conflicting or redundant options")));
> dcreateuser = defel;
> }
> + else if (strcmp(defel->defname, "maxconnections") == 0)
> + {
> + if (dmaxconn)
> + ereport(ERROR,
> + (errcode(ERRCODE_SYNTAX_ERROR),
> + errmsg("conflicting or redundant options")));
> + dmaxconn = defel;
> + }
> else if (strcmp(defel->defname, "groupElts") == 0)
> {
> if (dgroupElts)
> ***************
> *** 142,147 ****
> --- 152,165 ----
> createdb = intVal(dcreatedb->arg) != 0;
> if (dcreateuser)
> createuser = intVal(dcreateuser->arg) != 0;
> + if (dmaxconn)
> + {
> + maxconn = intVal(dmaxconn->arg);
> + if (maxconn < 0)
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> + errmsg("MAX CONNECTIONS must not be negative")));
> + }
> if (dsysid)
> {
> sysid = intVal(dsysid->arg);
> ***************
> *** 233,238 ****
> --- 251,257 ----
> new_record[Anum_pg_shadow_usesuper - 1] = BoolGetDatum(createuser);
> /* superuser gets catupd right by default */
> new_record[Anum_pg_shadow_usecatupd - 1] = BoolGetDatum(createuser);
> + new_record[Anum_pg_shadow_usemaxconn - 1] = Int32GetDatum(maxconn);
>
> if (password)
> {
> ***************
> *** 317,328 ****
> --- 336,349 ----
> char encrypted_password[MD5_PASSWD_LEN + 1];
> int createdb = -1; /* Can the user create databases? */
> int createuser = -1; /* Can this user create users? */
> + int maxconn = -1; /* Maximum connections allowed */
> char *validUntil = NULL; /* The time the login is valid
> * until */
> DefElem *dpassword = NULL;
> DefElem *dcreatedb = NULL;
> DefElem *dcreateuser = NULL;
> DefElem *dvalidUntil = NULL;
> + DefElem *dmaxconn = NULL;
>
> /* Extract options from the statement node tree */
> foreach(option, stmt->options)
> ***************
> *** 359,364 ****
> --- 380,393 ----
> errmsg("conflicting or redundant options")));
> dcreateuser = defel;
> }
> + else if (strcmp(defel->defname, "maxconnections") == 0)
> + {
> + if (dmaxconn)
> + ereport(ERROR,
> + (errcode(ERRCODE_SYNTAX_ERROR),
> + errmsg("conflicting or redundant options")));
> + dmaxconn = defel;
> + }
> else if (strcmp(defel->defname, "validUntil") == 0)
> {
> if (dvalidUntil)
> ***************
> *** 376,381 ****
> --- 405,412 ----
> createdb = intVal(dcreatedb->arg);
> if (dcreateuser)
> createuser = intVal(dcreateuser->arg);
> + if (dmaxconn)
> + maxconn = intVal(dmaxconn->arg);
> if (dvalidUntil)
> validUntil = strVal(dvalidUntil->arg);
> if (dpassword)
> ***************
> *** 427,432 ****
> --- 458,469 ----
> {
> new_record[Anum_pg_shadow_usecreatedb - 1] = BoolGetDatum(createdb > 0);
> new_record_repl[Anum_pg_shadow_usecreatedb - 1] = 'r';
> + }
> +
> + if (maxconn >= 0)
> + {
> + new_record[Anum_pg_shadow_usemaxconn - 1] = Int32GetDatum(maxconn);
> + new_record_repl[Anum_pg_shadow_usemaxconn - 1] = 'r';
> }
>
> /*
> diff -Nacr my-cvs/src/backend/nodes/copyfuncs.c my-aproach2/src/backend/nodes/copyfuncs.c
> *** my-cvs/src/backend/nodes/copyfuncs.c Mon Jun 27 00:05:38 2005
> --- my-aproach2/src/backend/nodes/copyfuncs.c Tue Jun 28 06:07:50 2005
> ***************
> *** 2191,2196 ****
> --- 2191,2207 ----
> 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)
> {
> diff -Nacr my-cvs/src/backend/nodes/equalfuncs.c my-aproach2/src/backend/nodes/equalfuncs.c
> *** my-cvs/src/backend/nodes/equalfuncs.c Mon Jun 27 00:05:38 2005
> --- my-aproach2/src/backend/nodes/equalfuncs.c Tue Jun 28 06:07:50 2005
> ***************
> *** 1141,1146 ****
> --- 1141,1155 ----
> }
>
> 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);
> diff -Nacr my-cvs/src/backend/parser/gram.y my-aproach2/src/backend/parser/gram.y
> *** my-cvs/src/backend/parser/gram.y Mon Jun 27 00:05:38 2005
> --- my-aproach2/src/backend/parser/gram.y Tue Jun 28 11:26:30 2005
> ***************
> *** 131,139 ****
> }
>
> %type <node> stmt schema_stmt
> ! AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt
> ! AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt
> ! AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
> ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
> CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
> CreateSchemaStmt CreateSeqStmt CreateStmt CreateTableSpaceStmt
> --- 131,139 ----
> }
>
> %type <node> stmt schema_stmt
> ! AlterDatabaseStmt AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt
> ! AlterOwnerStmt AlterSeqStmt AlterTableStmt AlterUserStmt
> ! AlterUserSetStmt AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
> ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
> CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
> CreateSchemaStmt CreateSeqStmt CreateStmt CreateTableSpaceStmt
> ***************
> *** 164,171 ****
>
> %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 <ival> opt_lock lock_type cast_context
> %type <boolean> opt_force opt_or_replace
> --- 164,173 ----
>
> %type <dbehavior> opt_drop_behavior
>
> ! %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
> ***************
> *** 346,352 ****
> 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
> CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
> CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
>
> --- 348,354 ----
> CACHE CALLED CASCADE CASE CAST CHAIN CHAR_P
> CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
> CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
> ! COMMITTED CONNECTIONS CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY CREATE CREATEDB
> CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
> CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
>
> ***************
> *** 377,383 ****
> LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
> LOCK_P
>
> ! MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
>
> NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
> NOCREATEUSER NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P
> --- 379,385 ----
> LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
> LOCK_P
>
> ! MATCH MAX MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
>
> NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
> NOCREATEUSER NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P
> ***************
> *** 490,496 ****
> ;
>
> stmt :
> ! AlterDatabaseSetStmt
> | AlterDomainStmt
> | AlterFunctionStmt
> | AlterGroupStmt
> --- 492,499 ----
> ;
>
> stmt :
> ! AlterDatabaseStmt
> ! | AlterDatabaseSetStmt
> | AlterDomainStmt
> | AlterFunctionStmt
> | AlterGroupStmt
> ***************
> *** 688,693 ****
> --- 691,700 ----
> {
> $$ = makeDefElem("createuser", (Node *)makeInteger(FALSE));
> }
> + | MAX CONNECTIONS Iconst
> + {
> + $$ = makeDefElem("maxconnections", (Node *)makeInteger($3));
> + }
> | IN_P GROUP_P user_list
> {
> $$ = makeDefElem("groupElts", (Node *)$3);
> ***************
> *** 4294,4299 ****
> --- 4301,4310 ----
> {
> $$ = makeDefElem("encoding", NULL);
> }
> + | MAX CONNECTIONS opt_equal Iconst
> + {
> + $$ = makeDefElem("maxconnections", (Node *)makeInteger($4));
> + }
> | OWNER opt_equal name
> {
> $$ = makeDefElem("owner", (Node *)makeString($3));
> ***************
> *** 4320,4325 ****
> --- 4331,4346 ----
> *
> *****************************************************************************/
>
> + 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
> {
> ***************
> *** 4340,4345 ****
> --- 4361,4379 ----
> ;
>
>
> + alterdb_opt_list:
> + alterdb_opt_list alterdb_opt_item { $$ = lappend($1, $2); }
> + | /* EMPTY */ { $$ = NIL; }
> + ;
> +
> + alterdb_opt_item:
> + MAX CONNECTIONS opt_equal Iconst
> + {
> + $$ = makeDefElem("maxconnections", (Node *)makeInteger($4));
> + }
> + ;
> +
> +
> /*****************************************************************************
> *
> * DROP DATABASE
> ***************
> *** 7770,7775 ****
> --- 7804,7810 ----
> | COMMENT
> | COMMIT
> | COMMITTED
> + | CONNECTIONS
> | CONSTRAINTS
> | CONVERSION_P
> | COPY
> ***************
> *** 7835,7840 ****
> --- 7870,7876 ----
> | LOCATION
> | LOCK_P
> | MATCH
> + | MAX
> | MAXVALUE
> | MINUTE_P
> | MINVALUE
> diff -Nacr my-cvs/src/backend/parser/keywords.c my-aproach2/src/backend/parser/keywords.c
> *** my-cvs/src/backend/parser/keywords.c Mon Jun 27 00:05:40 2005
> --- my-aproach2/src/backend/parser/keywords.c Tue Jun 28 06:07:50 2005
> ***************
> *** 82,87 ****
> --- 82,88 ----
> {"comment", COMMENT},
> {"commit", COMMIT},
> {"committed", COMMITTED},
> + {"connections", CONNECTIONS},
> {"constraint", CONSTRAINT},
> {"constraints", CONSTRAINTS},
> {"conversion", CONVERSION_P},
> ***************
> *** 198,203 ****
> --- 199,205 ----
> {"location", LOCATION},
> {"lock", LOCK_P},
> {"match", MATCH},
> + {"max", MAX},
> {"maxvalue", MAXVALUE},
> {"minute", MINUTE_P},
> {"minvalue", MINVALUE},
> diff -Nacr my-cvs/src/backend/storage/ipc/procarray.c my-aproach2/src/backend/storage/ipc/procarray.c
> *** my-cvs/src/backend/storage/ipc/procarray.c Sat Jun 18 00:32:46 2005
> --- my-aproach2/src/backend/storage/ipc/procarray.c Tue Jun 28 06:07:50 2005
> ***************
> *** 734,739 ****
> --- 734,790 ----
> }
>
>
> + /*
> + * 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 && proc->databaseId == databaseid)
> + count++;
> + }
> +
> + LWLockRelease(ProcArrayLock);
> +
> + return count;
> + }
> +
> + /*
> + * CountUserBackends --- count backends that are used by specified user
> + */
> + int
> + CountUserBackends(AclId userid)
> + {
> + 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 && proc->userId == userid)
> + count++;
> + }
> +
> + LWLockRelease(ProcArrayLock);
> +
> + return count;
> + }
> +
> +
> #define XidCacheRemove(i) \
> do { \
> MyProc->subxids.xids[i] = MyProc->subxids.xids[MyProc->subxids.nxids - 1]; \
> diff -Nacr my-cvs/src/backend/storage/lmgr/proc.c my-aproach2/src/backend/storage/lmgr/proc.c
> *** my-cvs/src/backend/storage/lmgr/proc.c Sat Jun 18 00:32:46 2005
> --- my-aproach2/src/backend/storage/lmgr/proc.c Tue Jun 28 06:39:46 2005
> ***************
> *** 254,259 ****
> --- 254,260 ----
> MyProc->xmin = InvalidTransactionId;
> MyProc->pid = MyProcPid;
> MyProc->databaseId = MyDatabaseId;
> + MyProc->userId = GetSessionUserId();
> MyProc->lwWaiting = false;
> MyProc->lwExclusive = false;
> MyProc->lwWaitLink = NULL;
> diff -Nacr my-cvs/src/backend/tcop/utility.c my-aproach2/src/backend/tcop/utility.c
> *** my-cvs/src/backend/tcop/utility.c Wed Jun 22 23:14:30 2005
> --- my-aproach2/src/backens/tcop/utility.c Tue Jun 28 06:07:50 2005
> ***************
> *** 276,281 ****
> --- 276,282 ----
>
> switch (nodeTag(parsetree))
> {
> + case T_AlterDatabaseStmt:
> case T_AlterDatabaseSetStmt:
> case T_AlterDomainStmt:
> case T_AlterFunctionStmt:
> ***************
> *** 786,791 ****
> --- 787,796 ----
>
> case T_CreatedbStmt:
> createdb((CreatedbStmt *) parsetree);
> + break;
> +
> + case T_AlterDatabaseStmt:
> + AlterDatabase((AlterDatabaseStmt *) parsetree);
> break;
>
> case T_AlterDatabaseSetStmt:
> diff -Nacr my-cvs/src/backend/utils/init/miscinit.c my-aproach2/src/backend/utils/init/miscinit.c
> *** my-cvs/src/backend/utils/init/miscinit.c Mon Jun 20 04:17:30 2005
> --- my-aproach2/src/backend/utils/init/miscinit.c Tue Jun 28 06:41:40 2005
> ***************
> *** 315,320 ****
> --- 315,321 ----
> Datum datum;
> bool isnull;
> AclId usesysid;
> + Form_pg_shadow userform;
>
> /*
> * Don't do scans if we're bootstrapping, none of the system catalogs
> ***************
> *** 333,344 ****
> (errcode(ERRCODE_UNDEFINED_OBJECT),
> errmsg("user \"%s\" does not exist", username)));
>
> ! usesysid = ((Form_pg_shadow) GETSTRUCT(userTup))->usesysid;
>
> AuthenticatedUserId = usesysid;
> ! AuthenticatedUserIsSuperuser = ((Form_pg_shadow) GETSTRUCT(userTup))->usesuper;
>
> SetSessionUserId(usesysid); /* sets CurrentUserId too */
>
> /* Record username and superuser status as GUC settings too */
> SetConfigOption("session_authorization", username,
> --- 334,358 ----
> (errcode(ERRCODE_UNDEFINED_OBJECT),
> errmsg("user \"%s\" does not exist", username)));
>
> ! userform = ((Form_pg_shadow) GETSTRUCT(userTup));
> ! usesysid = userform->usesysid;
>
> AuthenticatedUserId = usesysid;
> ! AuthenticatedUserIsSuperuser = userform->usesuper;
>
> SetSessionUserId(usesysid); /* sets CurrentUserId too */
> +
> + /*
> + * Check connection limit for user
> + */
> + if (userform->usemaxconn > 0 && !AuthenticatedUserIsSuperuser &&
> + CountUserBackends(AuthenticatedUserId) > userform->usemaxconn)
> + {
> + ereport(FATAL,
> + (errcode(ERRCODE_TOO_MANY_CONNECTIONS),
> + errmsg("sorry, too many clients already for user \"%s\"",
> + username)));
> + }
>
> /* Record username and superuser status as GUC settings too */
> SetConfigOption("session_authorization", username,
> diff -Nacr my-cvs/src/backend/utils/init/postinit.c my-aproach2/src/backend/utils/init/postinit.c
> *** my-cvs/src/backend/utils/init/postinit.c Fri Jun 24 03:06:26 2005
> --- my-aproach2/src/backend/utils/init/postinit.c Tue Jun 28 10:11:10 2005
> ***************
> *** 50,55 ****
> --- 50,56 ----
> static void InitCommunication(void);
> static void ShutdownPostgres(int code, Datum arg);
> static bool ThereIsAtLeastOneUser(void);
> + static bool FindMyUser(const char *name, AclId *user_id);
>
>
> /*** InitPostgres support ***/
> ***************
> *** 100,105 ****
> --- 101,137 ----
> }
>
> /*
> + * Get user id from flatfiles
> + *
> + * We need this because we need to know userid before
> + * InitProcess() is called
> + */
> + static bool
> + FindMyUser(const char *name, AclId *user_id)
> + {
> + List **line;
> + ListCell *token;
> + char thisname[NAMEDATALEN];
> +
> + if ((line = get_user_line(name)) == NULL)
> + ereport(FATAL,
> + (ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION, /* ? */
> + errmsg("could not find user \"%s\": %m", name)));
> +
> + /* Skip over username */
> + token = list_head(*line);
> + if (token)
> + token = lnext(token);
> + if (token)
> + {
> + *user_id = atoi((char*)lfirst(token));
> + return true;
> + }
> +
> + return false;
> + }
> +
> + /*
> * ReverifyMyDatabase -- recheck info obtained by FindMyDatabase
> *
> * Since FindMyDatabase cannot lock pg_database, the information it read
> ***************
> *** 165,181 ****
> name, MyDatabaseId)));
> }
>
> - /*
> - * 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;")
> - */
> dbform = (Form_pg_database) GETSTRUCT(tup);
> ! if (IsUnderPostmaster && !dbform->datallowconn)
> ! ereport(FATAL,
> ! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> ! errmsg("database \"%s\" is not currently accepting connections",
> name)));
>
> /*
> * OK, we're golden. Next to-do item is to save the encoding
> --- 197,231 ----
> name, MyDatabaseId)));
> }
>
> dbform = (Form_pg_database) GETSTRUCT(tup);
> ! if (IsUnderPostmaster)
> ! {
> ! /*
> ! * 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;")
> ! */
> ! if (!dbform->datallowconn)
> ! {
> ! ereport(FATAL,
> ! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> ! errmsg("database \"%s\" is not currently accepting connections",
> ! name)));
> ! }
> !
> ! /*
> ! * Here we check cxonenction limit for this database
> ! */
> ! if (dbform->datmaxconn > 0 && !superuser() &&
> ! CountDBBackends(MyDatabaseId) > dbform->datmaxconn)
> ! {
> ! ereport(FATAL,
> ! (errcode(ERRCODE_TOO_MANY_CONNECTIONS),
> ! errmsg("sorry, too many clients already for database \"%s\"",
> name)));
> + }
> + }
> +
>
> /*
> * OK, we're golden. Next to-do item is to save the encoding
> ***************
> *** 350,355 ****
> --- 400,424 ----
> * Code after this point assumes we are in the proper directory!
> */
>
> + /*
> + * We need to know userid in InitProcess() so we have read it from
> + * flatfile, real user inicialization is done later
> + */
> + if (IsUnderPostmaster)
> + {
> + AclId userid;
> +
> + if (!FindMyUser(username, &userid))
> + ereport(FATAL,
> + (ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION, /* ? */
> + errmsg("user \"%s\" does not exist",
> + username)));
> +
> + SetSessionUserId(userid);
> + }
> + else
> + SetSessionUserId(BOOTSTRAP_USESYSID);
> +
> /*
> * Set up my per-backend PGPROC struct in shared memory. (We need
> * to know MyDatabaseId before we can do this, since it's entered into
> diff -Nacr my-cvs/src/include/catalog/pg_database.h my-aproach2/src/include/catalog/pg_database.h
> *** my-cvs/src/include/catalog/pg_database.h Thu Apr 14 03:38:20 2005
> --- my-aproach2/src/include/catalog/pg_database.h Tue Jun 28 06:07:50 2005
> ***************
> *** 40,45 ****
> --- 40,46 ----
> int4 encoding; /* character encoding */
> bool datistemplate; /* allowed as CREATE DATABASE template? */
> bool datallowconn; /* new connections allowed? */
> + int4 datmaxconn; /* maximum connections allowed */
> 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,78 ****
> * compiler constants for pg_database
> * ----------------
> */
> ! #define Natts_pg_database 11
> #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
>
> ! DATA(insert OID = 1 ( template1 PGUID ENCODING t t 0 0 0 1663 _null_ _null_ ));
> DESCR("Default template database");
> #define TemplateDbOid 1
>
> --- 60,80 ----
> * compiler constants for pg_database
> * ----------------
> */
> ! #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_datmaxconn 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 0 1663 _null_ _null_ ));
> DESCR("Default template database");
> #define TemplateDbOid 1
>
> diff -Nacr my-cvs/src/include/catalog/pg_shadow.h my-aproach2/src/include/catalog/pg_shadow.h
> *** my-cvs/src/include/catalog/pg_shadow.h Thu Apr 14 03:38:22 2005
> --- my-aproach2/src/include/catalog/pg_shadow.h Tue Jun 28 06:07:50 2005
> ***************
> *** 36,41 ****
> --- 36,42 ----
> bool usecreatedb;
> bool usesuper; /* read this field via superuser() only */
> bool usecatupd;
> + int4 usemaxconn; /* maximum connections allowed */
>
> /* remaining fields may be null; use heap_getattr to read them! */
> text passwd;
> ***************
> *** 54,68 ****
> * compiler constants for pg_shadow
> * ----------------
> */
> ! #define Natts_pg_shadow 8
> #define Anum_pg_shadow_usename 1
> #define Anum_pg_shadow_usesysid 2
> #define Anum_pg_shadow_usecreatedb 3
> #define Anum_pg_shadow_usesuper 4
> #define Anum_pg_shadow_usecatupd 5
> ! #define Anum_pg_shadow_passwd 6
> ! #define Anum_pg_shadow_valuntil 7
> ! #define Anum_pg_shadow_useconfig 8
>
> /* ----------------
> * initial contents of pg_shadow
> --- 55,70 ----
> * compiler constants for pg_shadow
> * ----------------
> */
> ! #define Natts_pg_shadow 9
> #define Anum_pg_shadow_usename 1
> #define Anum_pg_shadow_usesysid 2
> #define Anum_pg_shadow_usecreatedb 3
> #define Anum_pg_shadow_usesuper 4
> #define Anum_pg_shadow_usecatupd 5
> ! #define Anum_pg_shadow_usemaxconn 6
> ! #define Anum_pg_shadow_passwd 7
> ! #define Anum_pg_shadow_valuntil 8
> ! #define Anum_pg_shadow_useconfig 9
>
> /* ----------------
> * initial contents of pg_shadow
> ***************
> *** 71,77 ****
> * user choices.
> * ----------------
> */
> ! DATA(insert ( "POSTGRES" PGUID t t t _null_ _null_ _null_ ));
>
> #define BOOTSTRAP_USESYSID 1
>
> --- 73,79 ----
> * user choices.
> * ----------------
> */
> ! DATA(insert ( "POSTGRES" PGUID t t t 0 _null_ _null_ _null_ ));
>
> #define BOOTSTRAP_USESYSID 1
>
> diff -Nacr my-cvs/src/include/commands/dbcommands.h my-aproach2/src/include/commands/dbcommands.h
> *** my-cvs/src/include/commands/dbcommands.h Mon Jun 06 19:01:26 2005
> --- my-aproach2/src/include/commands/dbcommands.h Tue Jun 28 06:07:50 2005
> ***************
> *** 64,69 ****
> --- 64,70 ----
> 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, AclId newOwnerSysId);
>
> diff -Nacr my-cvs/src/include/nodes/nodes.h my-aproach2/src/include/nodes/nodes.h
> *** my-cvs/src/include/nodes/nodes.h Mon Jun 27 00:05:42 2005
> --- my-aproach2/src/include/nodes/nodes.h Tue Jun 28 06:07:50 2005
> ***************
> *** 272,277 ****
> --- 272,278 ----
> T_ReindexStmt,
> T_CheckPointStmt,
> T_CreateSchemaStmt,
> + T_AlterDatabaseStmt,
> T_AlterDatabaseSetStmt,
> T_AlterUserSetStmt,
> T_CreateConversionStmt,
> diff -Nacr my-cvs/src/include/nodes/parsenodes.h my-aproach2/src/include/nodes/parsenodes.h
> *** my-cvs/src/include/nodes/parsenodes.h Wed Jun 22 23:14:32 2005
> --- my-aproach2/src/include/nodes/parsenodes.h Tue Jun 28 06:07:50 2005
> ***************
> *** 1620,1625 ****
> --- 1620,1632 ----
> * Alter Database
> * ----------------------
> */
> + typedef struct AlterDatabaseStmt
> + {
> + NodeTag type;
> + char *dbname; /* name of database to create */
> + List *options; /* List of DefElem nodes */
> + } AlterDatabaseStmt;
> +
> typedef struct AlterDatabaseSetStmt
> {
> NodeTag type;
> diff -Nacr my-cvs/src/include/storage/proc.h my-aproach2/src/include/storage/proc.h
> *** my-cvs/src/include/storage/proc.h Sat Jun 18 00:32:50 2005
> --- my-aproach2/src/include/storage/proc.h Tue Jun 28 06:07:50 2005
> ***************
> *** 71,76 ****
> --- 71,77 ----
>
> int pid; /* This backend's process id, or 0 */
> Oid databaseId; /* OID of database this backend is using */
> + AclId userId; /* user connected to this backend */
>
> /* Info about LWLock the process is currently waiting for, if any. */
> bool lwWaiting; /* true if waiting for an LW lock */
> diff -Nacr my-cvs/src/include/storage/procarray.h my-aproach2/src/include/storage/procarray.h
> *** my-cvs/src/include/storage/procarray.h Sat Jun 18 00:32:50 2005
> --- my-aproach2/src/include/storage/procarray.h Tue Jun 28 06:07:50 2005
> ***************
> *** 31,36 ****
> --- 31,38 ----
> extern bool DatabaseHasActiveBackends(Oid databaseId, bool ignoreMyself);
>
> extern int CountActiveBackends(void);
> + extern int CountDBBackends(Oid databaseid);
> + extern int CountUserBackends(AclId userid);
>
> extern void XidCacheRemoveRunningXids(TransactionId xid,
> int nxids, TransactionId *xids);
> diff -Nacr my-cvs/src/tools/pgindent/pgindent my-aproach2/src/tools/pgindent/pgindent
> *** my-cvs/src/tools/pgindent/pgindent Thu Oct 07 16:15:50 2004
> --- my-aproach2/src/tools/pgindent/pgindent Tue Jun 28 06:07:50 2005
> ***************
> *** 175,180 ****
> --- 175,181 ----
> -TAllocSetContext \
> -TAllocateDesc \
> -TAllocateDescKind \
> + -TAlterDatabaseStmt \
> -TAlterDatabaseSetStmt \
> -TAlterDomainStmt \
> -TAlterGroupStmt \

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073