Re: per user/database connections limit again

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)

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

On Sat, Jul 02, 2005 at 04:28:48PM -0400, Bruce Momjian wrote:

> 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.

It needs to be updated to account for the roles patch.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"El Maquinismo fue proscrito so pena de cosquilleo hasta la muerte"
(Ijon Tichy en Viajes, Stanislaw Lem)

Alvaro Herrera wrote:
> On Sat, Jul 02, 2005 at 04:28:48PM -0400, Bruce Momjian wrote:
>
> > 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.
>
> It needs to be updated to account for the roles patch.

I was afraid of that. :-( Thanks.

--
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

* Petr Jelinek (pjmodos(at)parba(dot)cz) wrote:
> + if (!(superuser()
> + || ((Form_pg_database) GETSTRUCT(tuple))->datdba == GetUserId()))
> + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
> + stmt->dbname);

This should almost certainly be a pg_database_ownercheck() call instead.

The rest needs to be updated for roles, but looks like it should be
pretty easy to do. Much of it just needs to be repatched, the parts
that do need to be changed look to be pretty simple changes.

I believe the use of SessionUserId is probably correct in this patch.
This does mean that this patch will only be for canlogin roles, but that
seems like it's probably correct. Handling roles w/ members would
require much more thought.

Thanks,

Stephen

Stephen Frost wrote:

>This should almost certainly be a pg_database_ownercheck() call instead.
>
>
Right there wasn't pg_database_ownercheck at the time I was writing it,
fixed

>The rest needs to be updated for roles, but looks like it should be
>pretty easy to do. Much of it just needs to be repatched, the parts
>that do need to be changed look to be pretty simple changes.
>
>
Done.

>I believe the use of SessionUserId is probably correct in this patch.
>This does mean that this patch will only be for canlogin roles, but that
>seems like it's probably correct. Handling roles w/ members would
>require much more thought.
>
>
I don't think that having max connection for roles w/ members is doable
because you can have 5 roles which has 1 user as member and each role
has different number of max conections and there is no right way to
decide what to do.

New version which works with roles is attached (diffed against cvs),
everything else is mostly same.
I also had to readd roleid to flatfiles because I need it in
InitProcess() function.

--
Regards
Petr Jelinek (PJMODOS)

On Mon, Jul 04, 2005 at 01:08:05AM +0200, Petr Jelinek wrote:
> Stephen Frost wrote:

> New version which works with roles is attached (diffed against cvs),
> everything else is mostly same.
> I also had to readd roleid to flatfiles because I need it in
> InitProcess() function.

I was wondering if there was some way to defer the user check till a
later time, when the pg_authid relation could be checked? Not sure if
that's a good idea, but it may help reduce the impact of the change, and
thus chances that it'd be rejected.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"La espina, desde que nace, ya pincha" (Proverbio africano)

Alvaro Herrera wrote:

>I was wondering if there was some way to defer the user check till a
>later time, when the pg_authid relation could be checked? Not sure if
>that's a good idea, but it may help reduce the impact of the change, and
>thus chances that it'd be rejected.
>
>
Well It can, but it would mean one more lock on procarray and I didn't
want that and like I said, MyDatabaseId is read from flatfile too.
Auth flatfile is used only on two other places which I also patched so I
don't see this as a problem (it's used in hba.c to check role membership
and in crypt.c for password verification)

--
Regards
Petr Jelinek (PJMODOS)

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

I am ready to apply this patch. Would you make the additional changes
you suggested? Is there any way to see the limits except to query
pg_authid?

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

Petr Jelinek wrote:
> Stephen Frost wrote:
>
> >This should almost certainly be a pg_database_ownercheck() call instead.
> >
> >
> Right there wasn't pg_database_ownercheck at the time I was writing it,
> fixed
>
> >The rest needs to be updated for roles, but looks like it should be
> >pretty easy to do. Much of it just needs to be repatched, the parts
> >that do need to be changed look to be pretty simple changes.
> >
> >
> Done.
>
> >I believe the use of SessionUserId is probably correct in this patch.
> >This does mean that this patch will only be for canlogin roles, but that
> >seems like it's probably correct. Handling roles w/ members would
> >require much more thought.
> >
> >
> I don't think that having max connection for roles w/ members is doable
> because you can have 5 roles which has 1 user as member and each role
> has different number of max conections and there is no right way to
> decide what to do.
>
>
> New version which works with roles is attached (diffed against cvs),
> everything else is mostly same.
> I also had to readd roleid to flatfiles because I need it in
> InitProcess() function.
>
> --
> Regards
> Petr Jelinek (PJMODOS)
>
>

> Index: src/backend/commands/dbcommands.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/commands/dbcommands.c,v
> retrieving revision 1.164
> diff -c -r1.164 dbcommands.c
> *** src/backend/commands/dbcommands.c 30 Jun 2005 00:00:50 -0000 1.164
> --- src/backend/commands/dbcommands.c 3 Jul 2005 22:47:39 -0000
> ***************
> *** 53,60 ****
>
> /* non-export function prototypes */
> static bool get_db_info(const char *name, Oid *dbIdP, Oid *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, Oid *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 OID 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);
> ***************
> *** 587,593 ****
> */
> pgdbrel = heap_open(DatabaseRelationId, ExclusiveLock);
>
> ! if (!get_db_info(dbname, &db_id, NULL, NULL,
> &db_istemplate, NULL, NULL, NULL, NULL, NULL))
> ereport(ERROR,
> (errcode(ERRCODE_UNDEFINED_DATABASE),
> --- 606,612 ----
> */
> pgdbrel = heap_open(DatabaseRelationId, ExclusiveLock);
>
> ! if (!get_db_info(dbname, &db_id, NULL, NULL, NULL,
> &db_istemplate, NULL, NULL, NULL, NULL, NULL))
> ereport(ERROR,
> (errcode(ERRCODE_UNDEFINED_DATABASE),
> ***************
> *** 783,788 ****
> --- 802,892 ----
>
>
> /*
> + * 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 (!have_createdb_privilege())
> + 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
> ***************
> *** 971,978 ****
>
> static bool
> get_db_info(const char *name, Oid *dbIdP, Oid *ownerIdP,
> ! int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
> ! Oid *dbLastSysOidP,
> TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
> Oid *dbTablespace)
> {
> --- 1075,1082 ----
>
> static bool
> get_db_info(const char *name, Oid *dbIdP, Oid *ownerIdP,
> ! int *encodingP, int *dbMaxConnP, bool *dbIsTemplateP,
> ! bool *dbAllowConnP, Oid *dbLastSysOidP,
> TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
> Oid *dbTablespace)
> {
> ***************
> *** 1017,1022 ****
> --- 1121,1129 ----
> /* 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;
> Index: src/backend/commands/user.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/commands/user.c,v
> retrieving revision 1.155
> diff -c -r1.155 user.c
> *** src/backend/commands/user.c 29 Jun 2005 20:34:13 -0000 1.155
> --- src/backend/commands/user.c 3 Jul 2005 22:47:54 -0000
> ***************
> *** 85,90 ****
> --- 85,91 ----
> bool createrole = false; /* Can this user create roles? */
> bool createdb = false; /* Can the user create databases? */
> bool canlogin = false; /* Can this user login? */
> + int maxconn = 0; /* 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 */
> ***************
> *** 94,99 ****
> --- 95,101 ----
> DefElem *dcreaterole = NULL;
> DefElem *dcreatedb = NULL;
> DefElem *dcanlogin = NULL;
> + DefElem *dmaxconn = NULL;
> DefElem *daddroleto = NULL;
> DefElem *drolemembers = NULL;
> DefElem *dadminmembers = NULL;
> ***************
> *** 155,160 ****
> --- 157,170 ----
> errmsg("conflicting or redundant options")));
> dcanlogin = 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, "addroleto") == 0)
> {
> if (daddroleto)
> ***************
> *** 202,207 ****
> --- 212,230 ----
> createdb = intVal(dcreatedb->arg) != 0;
> if (dcanlogin)
> canlogin = intVal(dcanlogin->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 (!canlogin && maxconn > 0)
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> + errmsg("MAX CONNECTIONS can be specified only for roles which can login")));
> + }
> if (daddroleto)
> addroleto = (List *) daddroleto->arg;
> if (drolemembers)
> ***************
> *** 265,270 ****
> --- 288,294 ----
> /* 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_rolmaxconn - 1] = Int32GetDatum(maxconn);
>
> if (password)
> {
> ***************
> *** 369,374 ****
> --- 393,399 ----
> int createrole = -1; /* Can this user create roles? */
> int createdb = -1; /* Can the user create databases? */
> int canlogin = -1; /* Can this user login? */
> + int maxconn = -1; /* maximum connections allowed */
> List *rolemembers = NIL; /* roles to be added/removed */
> char *validUntil = NULL; /* time the login is valid until */
> DefElem *dpassword = NULL;
> ***************
> *** 376,381 ****
> --- 401,407 ----
> DefElem *dcreaterole = NULL;
> DefElem *dcreatedb = NULL;
> DefElem *dcanlogin = NULL;
> + DefElem *dmaxconn = NULL;
> DefElem *drolemembers = NULL;
> DefElem *dvalidUntil = NULL;
> Oid roleid;
> ***************
> *** 431,436 ****
> --- 457,470 ----
> errmsg("conflicting or redundant options")));
> dcanlogin = 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, "rolemembers") == 0 &&
> stmt->action != 0)
> {
> ***************
> *** 463,468 ****
> --- 497,515 ----
> createdb = intVal(dcreatedb->arg);
> if (dcanlogin)
> canlogin = intVal(dcanlogin->arg);
> + if (dmaxconn)
> + {
> + maxconn = intVal(dmaxconn->arg);
> + if (maxconn < 0)
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> + errmsg("MAX CONNECTIONS must not be negative")));
> +
> + if (canlogin == 0 && maxconn > 0)
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> + errmsg("MAX CONNECTIONS can be specified only for roles which can login")));
> + }
> if (drolemembers)
> rolemembers = (List *) drolemembers->arg;
> if (dvalidUntil)
> ***************
> *** 502,507 ****
> --- 549,555 ----
> !(createrole < 0 &&
> createdb < 0 &&
> canlogin < 0 &&
> + maxconn < 0 &&
> !rolemembers &&
> !validUntil &&
> password &&
> ***************
> *** 553,558 ****
> --- 601,612 ----
> new_record_repl[Anum_pg_authid_rolcanlogin - 1] = 'r';
> }
>
> + if (maxconn >= 0)
> + {
> + new_record[Anum_pg_authid_rolmaxconn - 1] = Int32GetDatum(maxconn);
> + new_record_repl[Anum_pg_authid_rolmaxconn - 1] = 'r';
> + }
> +
> /* password */
> if (password)
> {
> Index: src/backend/libpq/crypt.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/libpq/crypt.c,v
> retrieving revision 1.64
> diff -c -r1.64 crypt.c
> *** src/backend/libpq/crypt.c 29 Jun 2005 22:51:54 -0000 1.64
> --- src/backend/libpq/crypt.c 3 Jul 2005 22:47:57 -0000
> ***************
> *** 42,52 ****
> if ((line = get_role_line(role)) == NULL)
> return STATUS_ERROR;
>
> ! /* Skip over rolename */
> token = list_head(*line);
> if (token)
> token = lnext(token);
> if (token)
> {
> shadow_pass = (char *) lfirst(token);
> token = lnext(token);
> --- 42,54 ----
> if ((line = get_role_line(role)) == NULL)
> return STATUS_ERROR;
>
> ! /* Skip over rolename and roleid */
> token = list_head(*line);
> if (token)
> token = lnext(token);
> if (token)
> + token = lnext(token);
> + if (token)
> {
> shadow_pass = (char *) lfirst(token);
> token = lnext(token);
> Index: src/backend/libpq/hba.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/libpq/hba.c,v
> retrieving revision 1.144
> diff -c -r1.144 hba.c
> *** src/backend/libpq/hba.c 28 Jun 2005 22:16:45 -0000 1.144
> --- src/backend/libpq/hba.c 3 Jul 2005 22:48:12 -0000
> ***************
> *** 494,505 ****
> return true;
>
> /*
> ! * skip over the role name, password, valuntil, examine all the
> * membership entries
> */
> ! if (list_length(*line) < 4)
> return false;
> ! for_each_cell(line_item, lnext(lnext(lnext(list_head(*line)))))
> {
> if (strcmp((char *) lfirst(line_item), role) == 0)
> return true;
> --- 494,505 ----
> return true;
>
> /*
> ! * skip over the role name, id, password, valuntil, examine all the
> * membership entries
> */
> ! if (list_length(*line) < 5)
> return false;
> ! for_each_cell(line_item, lnext(lnext(lnext(lnext(list_head(*line))))))
> {
> if (strcmp((char *) lfirst(line_item), role) == 0)
> return true;
> Index: src/backend/nodes/copyfuncs.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
> retrieving revision 1.311
> diff -c -r1.311 copyfuncs.c
> *** src/backend/nodes/copyfuncs.c 2 Jul 2005 23:00:39 -0000 1.311
> --- src/backend/nodes/copyfuncs.c 3 Jul 2005 22:48:36 -0000
> ***************
> *** 2204,2209 ****
> --- 2204,2220 ----
> 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)
> {
> ***************
> *** 3010,3015 ****
> --- 3021,3029 ----
> case T_CreatedbStmt:
> retval = _copyCreatedbStmt(from);
> break;
> + case T_AlterDatabaseStmt:
> + retval = _copyAlterDatabaseStmt(from);
> + break;
> case T_AlterDatabaseSetStmt:
> retval = _copyAlterDatabaseSetStmt(from);
> break;
> Index: src/backend/nodes/equalfuncs.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
> retrieving revision 1.248
> diff -c -r1.248 equalfuncs.c
> *** src/backend/nodes/equalfuncs.c 2 Jul 2005 23:00:39 -0000 1.248
> --- src/backend/nodes/equalfuncs.c 3 Jul 2005 22:48:53 -0000
> ***************
> *** 1152,1157 ****
> --- 1152,1166 ----
> }
>
> 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);
> ***************
> *** 2058,2063 ****
> --- 2067,2075 ----
> case T_CreatedbStmt:
> retval = _equalCreatedbStmt(a, b);
> break;
> + case T_AlterDatabaseStmt:
> + retval = _equalAlterDatabaseStmt(a, b);
> + break;
> case T_AlterDatabaseSetStmt:
> retval = _equalAlterDatabaseSetStmt(a, b);
> break;
> Index: src/backend/parser/gram.y
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
> retrieving revision 2.501
> diff -c -r2.501 gram.y
> *** src/backend/parser/gram.y 29 Jun 2005 20:34:13 -0000 2.501
> --- src/backend/parser/gram.y 3 Jul 2005 22:50:20 -0000
> ***************
> *** 131,139 ****
> }
>
> %type <node> stmt schema_stmt
> ! AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt
> ! AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt
> ! AlterRoleStmt AlterRoleSetStmt
> AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
> ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
> CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
> --- 131,139 ----
> }
>
> %type <node> stmt schema_stmt
> ! AlterDatabaseStmt AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt
> ! AlterOwnerStmt AlterSeqStmt AlterTableStmt
> ! AlterUserStmt AlterUserSetStmt AlterRoleStmt AlterRoleSetStmt
> AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
> ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
> CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
> ***************
> *** 165,172 ****
>
> %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
> --- 165,174 ----
>
> %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
> ***************
> *** 342,348 ****
> 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
> CREATEROLE CREATEUSER CROSS CSV CURRENT_DATE CURRENT_ROLE CURRENT_TIME
> CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
>
> --- 344,350 ----
> 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
> CREATEROLE CREATEUSER CROSS CSV CURRENT_DATE CURRENT_ROLE CURRENT_TIME
> CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
>
> ***************
> *** 373,379 ****
> LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
> LOCK_P LOGIN_P
>
> ! MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
>
> NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
> NOCREATEROLE NOCREATEUSER NOLOGIN_P NONE NOSUPERUSER NOT NOTHING NOTIFY
> --- 375,381 ----
> LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
> LOCK_P LOGIN_P
>
> ! MATCH MAX MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
>
> NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
> NOCREATEROLE NOCREATEUSER NOLOGIN_P NONE NOSUPERUSER NOT NOTHING NOTIFY
> ***************
> *** 486,492 ****
> ;
>
> stmt :
> ! AlterDatabaseSetStmt
> | AlterDomainStmt
> | AlterFunctionStmt
> | AlterGroupStmt
> --- 488,495 ----
> ;
>
> stmt :
> ! AlterDatabaseStmt
> ! | AlterDatabaseSetStmt
> | AlterDomainStmt
> | AlterFunctionStmt
> | AlterGroupStmt
> ***************
> *** 663,668 ****
> --- 666,675 ----
> {
> $$ = makeDefElem("canlogin", (Node *)makeInteger(FALSE));
> }
> + | MAX CONNECTIONS Iconst
> + {
> + $$ = makeDefElem("maxconnections", (Node *)makeInteger($3));
> + }
> | IN_P ROLE name_list
> {
> $$ = makeDefElem("addroleto", (Node *)$3);
> ***************
> *** 4455,4460 ****
> --- 4462,4471 ----
> {
> $$ = makeDefElem("encoding", NULL);
> }
> + | MAX CONNECTIONS opt_equal Iconst
> + {
> + $$ = makeDefElem("maxconnections", (Node *)makeInteger($4));
> + }
> | OWNER opt_equal name
> {
> $$ = makeDefElem("owner", (Node *)makeString($3));
> ***************
> *** 4481,4486 ****
> --- 4492,4507 ----
> *
> *****************************************************************************/
>
> + 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
> {
> ***************
> *** 4501,4506 ****
> --- 4522,4540 ----
> ;
>
>
> + 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
> ***************
> *** 7941,7946 ****
> --- 7975,7981 ----
> | COMMENT
> | COMMIT
> | COMMITTED
> + | CONNECTIONS
> | CONSTRAINTS
> | CONVERSION_P
> | COPY
> ***************
> *** 8009,8014 ****
> --- 8044,8050 ----
> | LOCK_P
> | LOGIN_P
> | MATCH
> + | MAX
> | MAXVALUE
> | MINUTE_P
> | MINVALUE
> Index: src/backend/parser/keywords.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/parser/keywords.c,v
> retrieving revision 1.162
> diff -c -r1.162 keywords.c
> *** src/backend/parser/keywords.c 29 Jun 2005 20:34:14 -0000 1.162
> --- src/backend/parser/keywords.c 3 Jul 2005 22:50:24 -0000
> ***************
> *** 83,88 ****
> --- 83,89 ----
> {"comment", COMMENT},
> {"commit", COMMIT},
> {"committed", COMMITTED},
> + {"connections", CONNECTIONS},
> {"constraint", CONSTRAINT},
> {"constraints", CONSTRAINTS},
> {"conversion", CONVERSION_P},
> ***************
> *** 203,208 ****
> --- 204,210 ----
> {"lock", LOCK_P},
> {"login", LOGIN_P},
> {"match", MATCH},
> + {"max", MAX},
> {"maxvalue", MAXVALUE},
> {"minute", MINUTE_P},
> {"minvalue", MINVALUE},
> Index: src/backend/storage/ipc/procarray.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/storage/ipc/procarray.c,v
> retrieving revision 1.3
> diff -c -r1.3 procarray.c
> *** src/backend/storage/ipc/procarray.c 17 Jun 2005 22:32:45 -0000 1.3
> --- src/backend/storage/ipc/procarray.c 3 Jul 2005 22:50:36 -0000
> ***************
> *** 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(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 && proc->roleId == roleid)
> + count++;
> + }
> +
> + LWLockRelease(ProcArrayLock);
> +
> + return count;
> + }
> +
> +
> #define XidCacheRemove(i) \
> do { \
> MyProc->subxids.xids[i] = MyProc->subxids.xids[MyProc->subxids.nxids - 1]; \
> Index: src/backend/storage/lmgr/proc.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/storage/lmgr/proc.c,v
> retrieving revision 1.160
> diff -c -r1.160 proc.c
> *** src/backend/storage/lmgr/proc.c 17 Jun 2005 22:32:45 -0000 1.160
> --- src/backend/storage/lmgr/proc.c 3 Jul 2005 22:50:51 -0000
> ***************
> *** 254,259 ****
> --- 254,260 ----
> MyProc->xmin = InvalidTransactionId;
> MyProc->pid = MyProcPid;
> MyProc->databaseId = MyDatabaseId;
> + MyProc->roleId = GetSessionUserId();
> MyProc->lwWaiting = false;
> MyProc->lwExclusive = false;
> MyProc->lwWaitLink = NULL;
> Index: src/backend/tcop/utility.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/tcop/utility.c,v
> retrieving revision 1.240
> diff -c -r1.240 utility.c
> *** src/backend/tcop/utility.c 30 Jun 2005 00:00:51 -0000 1.240
> --- src/backend/tcop/utility.c 3 Jul 2005 22:51:06 -0000
> ***************
> *** 275,280 ****
> --- 275,281 ----
>
> switch (nodeTag(parsetree))
> {
> + case T_AlterDatabaseStmt:
> case T_AlterDatabaseSetStmt:
> case T_AlterDomainStmt:
> case T_AlterFunctionStmt:
> ***************
> *** 788,793 ****
> --- 789,798 ----
> createdb((CreatedbStmt *) parsetree);
> break;
>
> + case T_AlterDatabaseStmt:
> + AlterDatabase((AlterDatabaseStmt *) parsetree);
> + break;
> +
> case T_AlterDatabaseSetStmt:
> AlterDatabaseSet((AlterDatabaseSetStmt *) parsetree);
> break;
> ***************
> *** 1504,1509 ****
> --- 1509,1518 ----
> tag = "CREATE DATABASE";
> break;
>
> + case T_AlterDatabaseStmt:
> + tag = "ALTER DATABASE";
> + break;
> +
> case T_AlterDatabaseSetStmt:
> tag = "ALTER DATABASE";
> break;
> Index: src/backend/utils/init/flatfiles.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/utils/init/flatfiles.c,v
> retrieving revision 1.11
> diff -c -r1.11 flatfiles.c
> *** src/backend/utils/init/flatfiles.c 29 Jun 2005 20:34:15 -0000 1.11
> --- src/backend/utils/init/flatfiles.c 3 Jul 2005 22:51:18 -0000
> ***************
> *** 629,635 ****
> ListCell *mem;
>
> fputs_quote(arole->rolname, fp);
> ! fputs(" ", fp);
> fputs_quote(arole->rolpassword, fp);
> fputs(" ", fp);
> fputs_quote(arole->rolvaliduntil, fp);
> --- 629,635 ----
> ListCell *mem;
>
> fputs_quote(arole->rolname, fp);
> ! fprintf(fp, " %u ", arole->roleid);
> fputs_quote(arole->rolpassword, fp);
> fputs(" ", fp);
> fputs_quote(arole->rolvaliduntil, fp);
> Index: src/backend/utils/init/miscinit.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/utils/init/miscinit.c,v
> retrieving revision 1.144
> diff -c -r1.144 miscinit.c
> *** src/backend/utils/init/miscinit.c 28 Jun 2005 22:16:45 -0000 1.144
> --- src/backend/utils/init/miscinit.c 3 Jul 2005 22:51:29 -0000
> ***************
> *** 39,44 ****
> --- 39,45 ----
> #include "utils/guc.h"
> #include "utils/lsyscache.h"
> #include "utils/syscache.h"
> + #include "storage/procarray.h"
>
>
> ProcessingMode Mode = InitProcessing;
> ***************
> *** 347,352 ****
> --- 348,365 ----
>
> SetSessionUserId(roleid); /* sets CurrentUserId too */
>
> + /*
> + * Check connection limit for user
> + */
> + if (rform->rolmaxconn > 0 && !AuthenticatedUserIsSuperuser &&
> + CountUserBackends(AuthenticatedUserId) > rform->rolmaxconn)
> + {
> + ereport(FATAL,
> + (errcode(ERRCODE_TOO_MANY_CONNECTIONS),
> + errmsg("sorry, too many clients already for role \"%s\"",
> + rolename)));
> + }
> +
> /* Record username and superuser status as GUC settings too */
> SetConfigOption("session_authorization", rolename,
> PGC_BACKEND, PGC_S_OVERRIDE);
> Index: src/backend/utils/init/postinit.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/utils/init/postinit.c,v
> retrieving revision 1.151
> diff -c -r1.151 postinit.c
> *** src/backend/utils/init/postinit.c 28 Jun 2005 19:51:23 -0000 1.151
> --- src/backend/utils/init/postinit.c 3 Jul 2005 22:51:37 -0000
> ***************
> *** 47,52 ****
> --- 47,53 ----
>
>
> static bool FindMyDatabase(const char *name, Oid *db_id, Oid *db_tablespace);
> + static bool FindMyRole(const char *name, Oid *role_id);
> static void ReverifyMyDatabase(const char *name);
> static void InitCommunication(void);
> static void ShutdownPostgres(int code, Datum arg);
> ***************
> *** 101,106 ****
> --- 102,136 ----
> }
>
> /*
> + * Get roleid from flatfiles
> + *
> + * We need this because we need to know userid before
> + * InitProcess() is called
> + */
> + static bool
> + FindMyRole(const char *name, Oid *role_id)
> + {
> + List **line;
> + ListCell *token;
> +
> + if ((line = get_role_line(name)) == NULL)
> + ereport(FATAL,
> + (ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION,
> + errmsg("could not find role \"%s\"", name)));
> +
> + token = list_head(*line);
> + if (token)
> + token = lnext(token);
> + if (token)
> + {
> + *role_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
> ***************
> *** 166,182 ****
> 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
> --- 196,230 ----
> 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
> ***************
> *** 352,357 ****
> --- 400,424 ----
> */
>
> /*
> + * We need to know roleid in InitProcess() so we have read it from
> + * flatfile, real user inicialization is done later
> + */
> + if (IsUnderPostmaster)
> + {
> + Oid roleid;
> +
> + if (!FindMyRole(username, &roleid))
> + ereport(FATAL,
> + (ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION,
> + errmsg("role \"%s\" does not exist",
> + username)));
> +
> + SetSessionUserId(roleid);
> + }
> + else
> + SetSessionUserId(BOOTSTRAP_SUPERUSERID);
> +
> + /*
> * 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
> * the PGPROC struct.)
> Index: src/include/catalog/pg_authid.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_authid.h,v
> retrieving revision 1.1
> diff -c -r1.1 pg_authid.h
> *** src/include/catalog/pg_authid.h 28 Jun 2005 05:09:05 -0000 1.1
> --- src/include/catalog/pg_authid.h 3 Jul 2005 22:51:50 -0000
> ***************
> *** 48,53 ****
> --- 48,54 ----
> bool rolcreatedb; /* allowed to create databases? */
> bool rolcatupdate; /* allowed to alter catalogs manually? */
> bool rolcanlogin; /* allowed to log in as session user? */
> + int4 rolmaxconn; /* maximum connections allowed */
>
> /* remaining fields may be null; use heap_getattr to read them! */
> text rolpassword; /* password, if any */
> ***************
> *** 69,84 ****
> * compiler constants for pg_authid
> * ----------------
> */
> ! #define Natts_pg_authid 9
> #define Anum_pg_authid_rolname 1
> #define Anum_pg_authid_rolsuper 2
> #define Anum_pg_authid_rolcreaterole 3
> #define Anum_pg_authid_rolcreatedb 4
> #define Anum_pg_authid_rolcatupdate 5
> #define Anum_pg_authid_rolcanlogin 6
> ! #define Anum_pg_authid_rolpassword 7
> ! #define Anum_pg_authid_rolvaliduntil 8
> ! #define Anum_pg_authid_rolconfig 9
>
> /* ----------------
> * initial contents of pg_authid
> --- 70,86 ----
> * compiler constants for pg_authid
> * ----------------
> */
> ! #define Natts_pg_authid 10
> #define Anum_pg_authid_rolname 1
> #define Anum_pg_authid_rolsuper 2
> #define Anum_pg_authid_rolcreaterole 3
> #define Anum_pg_authid_rolcreatedb 4
> #define Anum_pg_authid_rolcatupdate 5
> #define Anum_pg_authid_rolcanlogin 6
> ! #define Anum_pg_authid_rolmaxconn 7
> ! #define Anum_pg_authid_rolpassword 8
> ! #define Anum_pg_authid_rolvaliduntil 9
> ! #define Anum_pg_authid_rolconfig 10
>
> /* ----------------
> * initial contents of pg_authid
> ***************
> *** 87,93 ****
> * user choices.
> * ----------------
> */
> ! DATA(insert OID = 10 ( "POSTGRES" t t t t t _null_ _null_ _null_ ));
>
> #define BOOTSTRAP_SUPERUSERID 10
>
> --- 89,95 ----
> * user choices.
> * ----------------
> */
> ! DATA(insert OID = 10 ( "POSTGRES" t t t t t 0 _null_ _null_ _null_ ));
>
> #define BOOTSTRAP_SUPERUSERID 10
>
> Index: src/include/catalog/pg_database.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_database.h,v
> retrieving revision 1.36
> diff -c -r1.36 pg_database.h
> *** src/include/catalog/pg_database.h 28 Jun 2005 05:09:06 -0000 1.36
> --- src/include/catalog/pg_database.h 3 Jul 2005 22:51:51 -0000
> ***************
> *** 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
>
> Index: src/include/commands/dbcommands.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/commands/dbcommands.h,v
> retrieving revision 1.39
> diff -c -r1.39 dbcommands.h
> *** src/include/commands/dbcommands.h 28 Jun 2005 05:09:12 -0000 1.39
> --- src/include/commands/dbcommands.h 3 Jul 2005 22:51:53 -0000
> ***************
> *** 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, Oid newOwnerId);
>
> Index: src/include/nodes/nodes.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/nodes/nodes.h,v
> retrieving revision 1.172
> diff -c -r1.172 nodes.h
> *** src/include/nodes/nodes.h 28 Jun 2005 05:09:13 -0000 1.172
> --- src/include/nodes/nodes.h 3 Jul 2005 22:52:00 -0000
> ***************
> *** 270,275 ****
> --- 270,276 ----
> T_ReindexStmt,
> T_CheckPointStmt,
> T_CreateSchemaStmt,
> + T_AlterDatabaseStmt,
> T_AlterDatabaseSetStmt,
> T_AlterRoleSetStmt,
> T_CreateConversionStmt,
> Index: src/include/nodes/parsenodes.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
> retrieving revision 1.285
> diff -c -r1.285 parsenodes.h
> *** src/include/nodes/parsenodes.h 28 Jun 2005 19:51:24 -0000 1.285
> --- src/include/nodes/parsenodes.h 3 Jul 2005 22:52:25 -0000
> ***************
> *** 1611,1616 ****
> --- 1611,1623 ----
> * 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;
> Index: src/include/storage/proc.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/storage/proc.h,v
> retrieving revision 1.79
> diff -c -r1.79 proc.h
> *** src/include/storage/proc.h 17 Jun 2005 22:32:50 -0000 1.79
> --- src/include/storage/proc.h 3 Jul 2005 22:52:29 -0000
> ***************
> *** 71,76 ****
> --- 71,77 ----
>
> int pid; /* This backend's process id, or 0 */
> Oid databaseId; /* OID of database this backend is using */
> + Oid roleId; /* OID of role using conencted to backend */
>
> /* Info about LWLock the process is currently waiting for, if any. */
> bool lwWaiting; /* true if waiting for an LW lock */
> Index: src/include/storage/procarray.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/storage/procarray.h,v
> retrieving revision 1.2
> diff -c -r1.2 procarray.h
> *** src/include/storage/procarray.h 17 Jun 2005 22:32:50 -0000 1.2
> --- src/include/storage/procarray.h 3 Jul 2005 22:52:30 -0000
> ***************
> *** 31,36 ****
> --- 31,38 ----
> 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);
> Index: src/tools/pgindent/pgindent
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/tools/pgindent/pgindent,v
> retrieving revision 1.75
> diff -c -r1.75 pgindent
> *** src/tools/pgindent/pgindent 28 Jun 2005 23:55:30 -0000 1.75
> --- src/tools/pgindent/pgindent 3 Jul 2005 22:53:03 -0000
> ***************
> *** 177,182 ****
> --- 177,183 ----
> -TAllocSetContext \
> -TAllocateDesc \
> -TAllocateDescKind \
> + -TAlterDatabaseStmt \
> -TAlterDatabaseSetStmt \
> -TAlterDomainStmt \
> -TAlterFunctionStmt \

--
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

Bruce Momjian napsal(a):

>I am ready to apply this patch. Would you make the additional changes
>you suggested? Is there any way to see the limits except to query
>pg_authid?
>
Yes I will - pg_dump is already done (I attached it because it should be
aplied with orginal patch), documentation depends partly on roles doc so
it will prolly have to wait.

I also added limit to pg_roles and pg_shadow views when I was patching
pg_dump so you can get it from them.

--
Regards
Petr Jelinek (PJMODOS)

The new syntax for this command is CREATE/ALTER DATABASE/USER:

| MAX CONNECTIONS Iconst

This adds 'max' as a keyword, though at a fairly unreserved level, I
think. Should we use the syntax LIMIT CONNECTIONS so we don't have to
add MAX as a keyword at all?

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

Petr Jelinek wrote:
> Stephen Frost wrote:
>
> >This should almost certainly be a pg_database_ownercheck() call instead.
> >
> >
> Right there wasn't pg_database_ownercheck at the time I was writing it,
> fixed
>
> >The rest needs to be updated for roles, but looks like it should be
> >pretty easy to do. Much of it just needs to be repatched, the parts
> >that do need to be changed look to be pretty simple changes.
> >
> >
> Done.
>
> >I believe the use of SessionUserId is probably correct in this patch.
> >This does mean that this patch will only be for canlogin roles, but that
> >seems like it's probably correct. Handling roles w/ members would
> >require much more thought.
> >
> >
> I don't think that having max connection for roles w/ members is doable
> because you can have 5 roles which has 1 user as member and each role
> has different number of max conections and there is no right way to
> decide what to do.
>
>
> New version which works with roles is attached (diffed against cvs),
> everything else is mostly same.
> I also had to readd roleid to flatfiles because I need it in
> InitProcess() function.
>
> --
> Regards
> Petr Jelinek (PJMODOS)
>
>

> Index: src/backend/commands/dbcommands.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/commands/dbcommands.c,v
> retrieving revision 1.164
> diff -c -r1.164 dbcommands.c
> *** src/backend/commands/dbcommands.c 30 Jun 2005 00:00:50 -0000 1.164
> --- src/backend/commands/dbcommands.c 3 Jul 2005 22:47:39 -0000
> ***************
> *** 53,60 ****
>
> /* non-export function prototypes */
> static bool get_db_info(const char *name, Oid *dbIdP, Oid *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, Oid *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 OID 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);
> ***************
> *** 587,593 ****
> */
> pgdbrel = heap_open(DatabaseRelationId, ExclusiveLock);
>
> ! if (!get_db_info(dbname, &db_id, NULL, NULL,
> &db_istemplate, NULL, NULL, NULL, NULL, NULL))
> ereport(ERROR,
> (errcode(ERRCODE_UNDEFINED_DATABASE),
> --- 606,612 ----
> */
> pgdbrel = heap_open(DatabaseRelationId, ExclusiveLock);
>
> ! if (!get_db_info(dbname, &db_id, NULL, NULL, NULL,
> &db_istemplate, NULL, NULL, NULL, NULL, NULL))
> ereport(ERROR,
> (errcode(ERRCODE_UNDEFINED_DATABASE),
> ***************
> *** 783,788 ****
> --- 802,892 ----
>
>
> /*
> + * 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 (!have_createdb_privilege())
> + 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
> ***************
> *** 971,978 ****
>
> static bool
> get_db_info(const char *name, Oid *dbIdP, Oid *ownerIdP,
> ! int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
> ! Oid *dbLastSysOidP,
> TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
> Oid *dbTablespace)
> {
> --- 1075,1082 ----
>
> static bool
> get_db_info(const char *name, Oid *dbIdP, Oid *ownerIdP,
> ! int *encodingP, int *dbMaxConnP, bool *dbIsTemplateP,
> ! bool *dbAllowConnP, Oid *dbLastSysOidP,
> TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
> Oid *dbTablespace)
> {
> ***************
> *** 1017,1022 ****
> --- 1121,1129 ----
> /* 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;
> Index: src/backend/commands/user.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/commands/user.c,v
> retrieving revision 1.155
> diff -c -r1.155 user.c
> *** src/backend/commands/user.c 29 Jun 2005 20:34:13 -0000 1.155
> --- src/backend/commands/user.c 3 Jul 2005 22:47:54 -0000
> ***************
> *** 85,90 ****
> --- 85,91 ----
> bool createrole = false; /* Can this user create roles? */
> bool createdb = false; /* Can the user create databases? */
> bool canlogin = false; /* Can this user login? */
> + int maxconn = 0; /* 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 */
> ***************
> *** 94,99 ****
> --- 95,101 ----
> DefElem *dcreaterole = NULL;
> DefElem *dcreatedb = NULL;
> DefElem *dcanlogin = NULL;
> + DefElem *dmaxconn = NULL;
> DefElem *daddroleto = NULL;
> DefElem *drolemembers = NULL;
> DefElem *dadminmembers = NULL;
> ***************
> *** 155,160 ****
> --- 157,170 ----
> errmsg("conflicting or redundant options")));
> dcanlogin = 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, "addroleto") == 0)
> {
> if (daddroleto)
> ***************
> *** 202,207 ****
> --- 212,230 ----
> createdb = intVal(dcreatedb->arg) != 0;
> if (dcanlogin)
> canlogin = intVal(dcanlogin->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 (!canlogin && maxconn > 0)
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> + errmsg("MAX CONNECTIONS can be specified only for roles which can login")));
> + }
> if (daddroleto)
> addroleto = (List *) daddroleto->arg;
> if (drolemembers)
> ***************
> *** 265,270 ****
> --- 288,294 ----
> /* 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_rolmaxconn - 1] = Int32GetDatum(maxconn);
>
> if (password)
> {
> ***************
> *** 369,374 ****
> --- 393,399 ----
> int createrole = -1; /* Can this user create roles? */
> int createdb = -1; /* Can the user create databases? */
> int canlogin = -1; /* Can this user login? */
> + int maxconn = -1; /* maximum connections allowed */
> List *rolemembers = NIL; /* roles to be added/removed */
> char *validUntil = NULL; /* time the login is valid until */
> DefElem *dpassword = NULL;
> ***************
> *** 376,381 ****
> --- 401,407 ----
> DefElem *dcreaterole = NULL;
> DefElem *dcreatedb = NULL;
> DefElem *dcanlogin = NULL;
> + DefElem *dmaxconn = NULL;
> DefElem *drolemembers = NULL;
> DefElem *dvalidUntil = NULL;
> Oid roleid;
> ***************
> *** 431,436 ****
> --- 457,470 ----
> errmsg("conflicting or redundant options")));
> dcanlogin = 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, "rolemembers") == 0 &&
> stmt->action != 0)
> {
> ***************
> *** 463,468 ****
> --- 497,515 ----
> createdb = intVal(dcreatedb->arg);
> if (dcanlogin)
> canlogin = intVal(dcanlogin->arg);
> + if (dmaxconn)
> + {
> + maxconn = intVal(dmaxconn->arg);
> + if (maxconn < 0)
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> + errmsg("MAX CONNECTIONS must not be negative")));
> +
> + if (canlogin == 0 && maxconn > 0)
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> + errmsg("MAX CONNECTIONS can be specified only for roles which can login")));
> + }
> if (drolemembers)
> rolemembers = (List *) drolemembers->arg;
> if (dvalidUntil)
> ***************
> *** 502,507 ****
> --- 549,555 ----
> !(createrole < 0 &&
> createdb < 0 &&
> canlogin < 0 &&
> + maxconn < 0 &&
> !rolemembers &&
> !validUntil &&
> password &&
> ***************
> *** 553,558 ****
> --- 601,612 ----
> new_record_repl[Anum_pg_authid_rolcanlogin - 1] = 'r';
> }
>
> + if (maxconn >= 0)
> + {
> + new_record[Anum_pg_authid_rolmaxconn - 1] = Int32GetDatum(maxconn);
> + new_record_repl[Anum_pg_authid_rolmaxconn - 1] = 'r';
> + }
> +
> /* password */
> if (password)
> {
> Index: src/backend/libpq/crypt.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/libpq/crypt.c,v
> retrieving revision 1.64
> diff -c -r1.64 crypt.c
> *** src/backend/libpq/crypt.c 29 Jun 2005 22:51:54 -0000 1.64
> --- src/backend/libpq/crypt.c 3 Jul 2005 22:47:57 -0000
> ***************
> *** 42,52 ****
> if ((line = get_role_line(role)) == NULL)
> return STATUS_ERROR;
>
> ! /* Skip over rolename */
> token = list_head(*line);
> if (token)
> token = lnext(token);
> if (token)
> {
> shadow_pass = (char *) lfirst(token);
> token = lnext(token);
> --- 42,54 ----
> if ((line = get_role_line(role)) == NULL)
> return STATUS_ERROR;
>
> ! /* Skip over rolename and roleid */
> token = list_head(*line);
> if (token)
> token = lnext(token);
> if (token)
> + token = lnext(token);
> + if (token)
> {
> shadow_pass = (char *) lfirst(token);
> token = lnext(token);
> Index: src/backend/libpq/hba.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/libpq/hba.c,v
> retrieving revision 1.144
> diff -c -r1.144 hba.c
> *** src/backend/libpq/hba.c 28 Jun 2005 22:16:45 -0000 1.144
> --- src/backend/libpq/hba.c 3 Jul 2005 22:48:12 -0000
> ***************
> *** 494,505 ****
> return true;
>
> /*
> ! * skip over the role name, password, valuntil, examine all the
> * membership entries
> */
> ! if (list_length(*line) < 4)
> return false;
> ! for_each_cell(line_item, lnext(lnext(lnext(list_head(*line)))))
> {
> if (strcmp((char *) lfirst(line_item), role) == 0)
> return true;
> --- 494,505 ----
> return true;
>
> /*
> ! * skip over the role name, id, password, valuntil, examine all the
> * membership entries
> */
> ! if (list_length(*line) < 5)
> return false;
> ! for_each_cell(line_item, lnext(lnext(lnext(lnext(list_head(*line))))))
> {
> if (strcmp((char *) lfirst(line_item), role) == 0)
> return true;
> Index: src/backend/nodes/copyfuncs.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
> retrieving revision 1.311
> diff -c -r1.311 copyfuncs.c
> *** src/backend/nodes/copyfuncs.c 2 Jul 2005 23:00:39 -0000 1.311
> --- src/backend/nodes/copyfuncs.c 3 Jul 2005 22:48:36 -0000
> ***************
> *** 2204,2209 ****
> --- 2204,2220 ----
> 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)
> {
> ***************
> *** 3010,3015 ****
> --- 3021,3029 ----
> case T_CreatedbStmt:
> retval = _copyCreatedbStmt(from);
> break;
> + case T_AlterDatabaseStmt:
> + retval = _copyAlterDatabaseStmt(from);
> + break;
> case T_AlterDatabaseSetStmt:
> retval = _copyAlterDatabaseSetStmt(from);
> break;
> Index: src/backend/nodes/equalfuncs.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
> retrieving revision 1.248
> diff -c -r1.248 equalfuncs.c
> *** src/backend/nodes/equalfuncs.c 2 Jul 2005 23:00:39 -0000 1.248
> --- src/backend/nodes/equalfuncs.c 3 Jul 2005 22:48:53 -0000
> ***************
> *** 1152,1157 ****
> --- 1152,1166 ----
> }
>
> 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);
> ***************
> *** 2058,2063 ****
> --- 2067,2075 ----
> case T_CreatedbStmt:
> retval = _equalCreatedbStmt(a, b);
> break;
> + case T_AlterDatabaseStmt:
> + retval = _equalAlterDatabaseStmt(a, b);
> + break;
> case T_AlterDatabaseSetStmt:
> retval = _equalAlterDatabaseSetStmt(a, b);
> break;
> Index: src/backend/parser/gram.y
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
> retrieving revision 2.501
> diff -c -r2.501 gram.y
> *** src/backend/parser/gram.y 29 Jun 2005 20:34:13 -0000 2.501
> --- src/backend/parser/gram.y 3 Jul 2005 22:50:20 -0000
> ***************
> *** 131,139 ****
> }
>
> %type <node> stmt schema_stmt
> ! AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt
> ! AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt
> ! AlterRoleStmt AlterRoleSetStmt
> AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
> ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
> CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
> --- 131,139 ----
> }
>
> %type <node> stmt schema_stmt
> ! AlterDatabaseStmt AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt
> ! AlterOwnerStmt AlterSeqStmt AlterTableStmt
> ! AlterUserStmt AlterUserSetStmt AlterRoleStmt AlterRoleSetStmt
> AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
> ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
> CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
> ***************
> *** 165,172 ****
>
> %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
> --- 165,174 ----
>
> %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
> ***************
> *** 342,348 ****
> 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
> CREATEROLE CREATEUSER CROSS CSV CURRENT_DATE CURRENT_ROLE CURRENT_TIME
> CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
>
> --- 344,350 ----
> 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
> CREATEROLE CREATEUSER CROSS CSV CURRENT_DATE CURRENT_ROLE CURRENT_TIME
> CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
>
> ***************
> *** 373,379 ****
> LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
> LOCK_P LOGIN_P
>
> ! MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
>
> NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
> NOCREATEROLE NOCREATEUSER NOLOGIN_P NONE NOSUPERUSER NOT NOTHING NOTIFY
> --- 375,381 ----
> LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
> LOCK_P LOGIN_P
>
> ! MATCH MAX MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
>
> NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
> NOCREATEROLE NOCREATEUSER NOLOGIN_P NONE NOSUPERUSER NOT NOTHING NOTIFY
> ***************
> *** 486,492 ****
> ;
>
> stmt :
> ! AlterDatabaseSetStmt
> | AlterDomainStmt
> | AlterFunctionStmt
> | AlterGroupStmt
> --- 488,495 ----
> ;
>
> stmt :
> ! AlterDatabaseStmt
> ! | AlterDatabaseSetStmt
> | AlterDomainStmt
> | AlterFunctionStmt
> | AlterGroupStmt
> ***************
> *** 663,668 ****
> --- 666,675 ----
> {
> $$ = makeDefElem("canlogin", (Node *)makeInteger(FALSE));
> }
> + | MAX CONNECTIONS Iconst
> + {
> + $$ = makeDefElem("maxconnections", (Node *)makeInteger($3));
> + }
> | IN_P ROLE name_list
> {
> $$ = makeDefElem("addroleto", (Node *)$3);
> ***************
> *** 4455,4460 ****
> --- 4462,4471 ----
> {
> $$ = makeDefElem("encoding", NULL);
> }
> + | MAX CONNECTIONS opt_equal Iconst
> + {
> + $$ = makeDefElem("maxconnections", (Node *)makeInteger($4));
> + }
> | OWNER opt_equal name
> {
> $$ = makeDefElem("owner", (Node *)makeString($3));
> ***************
> *** 4481,4486 ****
> --- 4492,4507 ----
> *
> *****************************************************************************/
>
> + 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
> {
> ***************
> *** 4501,4506 ****
> --- 4522,4540 ----
> ;
>
>
> + 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
> ***************
> *** 7941,7946 ****
> --- 7975,7981 ----
> | COMMENT
> | COMMIT
> | COMMITTED
> + | CONNECTIONS
> | CONSTRAINTS
> | CONVERSION_P
> | COPY
> ***************
> *** 8009,8014 ****
> --- 8044,8050 ----
> | LOCK_P
> | LOGIN_P
> | MATCH
> + | MAX
> | MAXVALUE
> | MINUTE_P
> | MINVALUE
> Index: src/backend/parser/keywords.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/parser/keywords.c,v
> retrieving revision 1.162
> diff -c -r1.162 keywords.c
> *** src/backend/parser/keywords.c 29 Jun 2005 20:34:14 -0000 1.162
> --- src/backend/parser/keywords.c 3 Jul 2005 22:50:24 -0000
> ***************
> *** 83,88 ****
> --- 83,89 ----
> {"comment", COMMENT},
> {"commit", COMMIT},
> {"committed", COMMITTED},
> + {"connections", CONNECTIONS},
> {"constraint", CONSTRAINT},
> {"constraints", CONSTRAINTS},
> {"conversion", CONVERSION_P},
> ***************
> *** 203,208 ****
> --- 204,210 ----
> {"lock", LOCK_P},
> {"login", LOGIN_P},
> {"match", MATCH},
> + {"max", MAX},
> {"maxvalue", MAXVALUE},
> {"minute", MINUTE_P},
> {"minvalue", MINVALUE},
> Index: src/backend/storage/ipc/procarray.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/storage/ipc/procarray.c,v
> retrieving revision 1.3
> diff -c -r1.3 procarray.c
> *** src/backend/storage/ipc/procarray.c 17 Jun 2005 22:32:45 -0000 1.3
> --- src/backend/storage/ipc/procarray.c 3 Jul 2005 22:50:36 -0000
> ***************
> *** 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(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 && proc->roleId == roleid)
> + count++;
> + }
> +
> + LWLockRelease(ProcArrayLock);
> +
> + return count;
> + }
> +
> +
> #define XidCacheRemove(i) \
> do { \
> MyProc->subxids.xids[i] = MyProc->subxids.xids[MyProc->subxids.nxids - 1]; \
> Index: src/backend/storage/lmgr/proc.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/storage/lmgr/proc.c,v
> retrieving revision 1.160
> diff -c -r1.160 proc.c
> *** src/backend/storage/lmgr/proc.c 17 Jun 2005 22:32:45 -0000 1.160
> --- src/backend/storage/lmgr/proc.c 3 Jul 2005 22:50:51 -0000
> ***************
> *** 254,259 ****
> --- 254,260 ----
> MyProc->xmin = InvalidTransactionId;
> MyProc->pid = MyProcPid;
> MyProc->databaseId = MyDatabaseId;
> + MyProc->roleId = GetSessionUserId();
> MyProc->lwWaiting = false;
> MyProc->lwExclusive = false;
> MyProc->lwWaitLink = NULL;
> Index: src/backend/tcop/utility.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/tcop/utility.c,v
> retrieving revision 1.240
> diff -c -r1.240 utility.c
> *** src/backend/tcop/utility.c 30 Jun 2005 00:00:51 -0000 1.240
> --- src/backend/tcop/utility.c 3 Jul 2005 22:51:06 -0000
> ***************
> *** 275,280 ****
> --- 275,281 ----
>
> switch (nodeTag(parsetree))
> {
> + case T_AlterDatabaseStmt:
> case T_AlterDatabaseSetStmt:
> case T_AlterDomainStmt:
> case T_AlterFunctionStmt:
> ***************
> *** 788,793 ****
> --- 789,798 ----
> createdb((CreatedbStmt *) parsetree);
> break;
>
> + case T_AlterDatabaseStmt:
> + AlterDatabase((AlterDatabaseStmt *) parsetree);
> + break;
> +
> case T_AlterDatabaseSetStmt:
> AlterDatabaseSet((AlterDatabaseSetStmt *) parsetree);
> break;
> ***************
> *** 1504,1509 ****
> --- 1509,1518 ----
> tag = "CREATE DATABASE";
> break;
>
> + case T_AlterDatabaseStmt:
> + tag = "ALTER DATABASE";
> + break;
> +
> case T_AlterDatabaseSetStmt:
> tag = "ALTER DATABASE";
> break;
> Index: src/backend/utils/init/flatfiles.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/utils/init/flatfiles.c,v
> retrieving revision 1.11
> diff -c -r1.11 flatfiles.c
> *** src/backend/utils/init/flatfiles.c 29 Jun 2005 20:34:15 -0000 1.11
> --- src/backend/utils/init/flatfiles.c 3 Jul 2005 22:51:18 -0000
> ***************
> *** 629,635 ****
> ListCell *mem;
>
> fputs_quote(arole->rolname, fp);
> ! fputs(" ", fp);
> fputs_quote(arole->rolpassword, fp);
> fputs(" ", fp);
> fputs_quote(arole->rolvaliduntil, fp);
> --- 629,635 ----
> ListCell *mem;
>
> fputs_quote(arole->rolname, fp);
> ! fprintf(fp, " %u ", arole->roleid);
> fputs_quote(arole->rolpassword, fp);
> fputs(" ", fp);
> fputs_quote(arole->rolvaliduntil, fp);
> Index: src/backend/utils/init/miscinit.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/utils/init/miscinit.c,v
> retrieving revision 1.144
> diff -c -r1.144 miscinit.c
> *** src/backend/utils/init/miscinit.c 28 Jun 2005 22:16:45 -0000 1.144
> --- src/backend/utils/init/miscinit.c 3 Jul 2005 22:51:29 -0000
> ***************
> *** 39,44 ****
> --- 39,45 ----
> #include "utils/guc.h"
> #include "utils/lsyscache.h"
> #include "utils/syscache.h"
> + #include "storage/procarray.h"
>
>
> ProcessingMode Mode = InitProcessing;
> ***************
> *** 347,352 ****
> --- 348,365 ----
>
> SetSessionUserId(roleid); /* sets CurrentUserId too */
>
> + /*
> + * Check connection limit for user
> + */
> + if (rform->rolmaxconn > 0 && !AuthenticatedUserIsSuperuser &&
> + CountUserBackends(AuthenticatedUserId) > rform->rolmaxconn)
> + {
> + ereport(FATAL,
> + (errcode(ERRCODE_TOO_MANY_CONNECTIONS),
> + errmsg("sorry, too many clients already for role \"%s\"",
> + rolename)));
> + }
> +
> /* Record username and superuser status as GUC settings too */
> SetConfigOption("session_authorization", rolename,
> PGC_BACKEND, PGC_S_OVERRIDE);
> Index: src/backend/utils/init/postinit.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/utils/init/postinit.c,v
> retrieving revision 1.151
> diff -c -r1.151 postinit.c
> *** src/backend/utils/init/postinit.c 28 Jun 2005 19:51:23 -0000 1.151
> --- src/backend/utils/init/postinit.c 3 Jul 2005 22:51:37 -0000
> ***************
> *** 47,52 ****
> --- 47,53 ----
>
>
> static bool FindMyDatabase(const char *name, Oid *db_id, Oid *db_tablespace);
> + static bool FindMyRole(const char *name, Oid *role_id);
> static void ReverifyMyDatabase(const char *name);
> static void InitCommunication(void);
> static void ShutdownPostgres(int code, Datum arg);
> ***************
> *** 101,106 ****
> --- 102,136 ----
> }
>
> /*
> + * Get roleid from flatfiles
> + *
> + * We need this because we need to know userid before
> + * InitProcess() is called
> + */
> + static bool
> + FindMyRole(const char *name, Oid *role_id)
> + {
> + List **line;
> + ListCell *token;
> +
> + if ((line = get_role_line(name)) == NULL)
> + ereport(FATAL,
> + (ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION,
> + errmsg("could not find role \"%s\"", name)));
> +
> + token = list_head(*line);
> + if (token)
> + token = lnext(token);
> + if (token)
> + {
> + *role_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
> ***************
> *** 166,182 ****
> 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
> --- 196,230 ----
> 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
> ***************
> *** 352,357 ****
> --- 400,424 ----
> */
>
> /*
> + * We need to know roleid in InitProcess() so we have read it from
> + * flatfile, real user inicialization is done later
> + */
> + if (IsUnderPostmaster)
> + {
> + Oid roleid;
> +
> + if (!FindMyRole(username, &roleid))
> + ereport(FATAL,
> + (ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION,
> + errmsg("role \"%s\" does not exist",
> + username)));
> +
> + SetSessionUserId(roleid);
> + }
> + else
> + SetSessionUserId(BOOTSTRAP_SUPERUSERID);
> +
> + /*
> * 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
> * the PGPROC struct.)
> Index: src/include/catalog/pg_authid.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_authid.h,v
> retrieving revision 1.1
> diff -c -r1.1 pg_authid.h
> *** src/include/catalog/pg_authid.h 28 Jun 2005 05:09:05 -0000 1.1
> --- src/include/catalog/pg_authid.h 3 Jul 2005 22:51:50 -0000
> ***************
> *** 48,53 ****
> --- 48,54 ----
> bool rolcreatedb; /* allowed to create databases? */
> bool rolcatupdate; /* allowed to alter catalogs manually? */
> bool rolcanlogin; /* allowed to log in as session user? */
> + int4 rolmaxconn; /* maximum connections allowed */
>
> /* remaining fields may be null; use heap_getattr to read them! */
> text rolpassword; /* password, if any */
> ***************
> *** 69,84 ****
> * compiler constants for pg_authid
> * ----------------
> */
> ! #define Natts_pg_authid 9
> #define Anum_pg_authid_rolname 1
> #define Anum_pg_authid_rolsuper 2
> #define Anum_pg_authid_rolcreaterole 3
> #define Anum_pg_authid_rolcreatedb 4
> #define Anum_pg_authid_rolcatupdate 5
> #define Anum_pg_authid_rolcanlogin 6
> ! #define Anum_pg_authid_rolpassword 7
> ! #define Anum_pg_authid_rolvaliduntil 8
> ! #define Anum_pg_authid_rolconfig 9
>
> /* ----------------
> * initial contents of pg_authid
> --- 70,86 ----
> * compiler constants for pg_authid
> * ----------------
> */
> ! #define Natts_pg_authid 10
> #define Anum_pg_authid_rolname 1
> #define Anum_pg_authid_rolsuper 2
> #define Anum_pg_authid_rolcreaterole 3
> #define Anum_pg_authid_rolcreatedb 4
> #define Anum_pg_authid_rolcatupdate 5
> #define Anum_pg_authid_rolcanlogin 6
> ! #define Anum_pg_authid_rolmaxconn 7
> ! #define Anum_pg_authid_rolpassword 8
> ! #define Anum_pg_authid_rolvaliduntil 9
> ! #define Anum_pg_authid_rolconfig 10
>
> /* ----------------
> * initial contents of pg_authid
> ***************
> *** 87,93 ****
> * user choices.
> * ----------------
> */
> ! DATA(insert OID = 10 ( "POSTGRES" t t t t t _null_ _null_ _null_ ));
>
> #define BOOTSTRAP_SUPERUSERID 10
>
> --- 89,95 ----
> * user choices.
> * ----------------
> */
> ! DATA(insert OID = 10 ( "POSTGRES" t t t t t 0 _null_ _null_ _null_ ));
>
> #define BOOTSTRAP_SUPERUSERID 10
>
> Index: src/include/catalog/pg_database.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_database.h,v
> retrieving revision 1.36
> diff -c -r1.36 pg_database.h
> *** src/include/catalog/pg_database.h 28 Jun 2005 05:09:06 -0000 1.36
> --- src/include/catalog/pg_database.h 3 Jul 2005 22:51:51 -0000
> ***************
> *** 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
>
> Index: src/include/commands/dbcommands.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/commands/dbcommands.h,v
> retrieving revision 1.39
> diff -c -r1.39 dbcommands.h
> *** src/include/commands/dbcommands.h 28 Jun 2005 05:09:12 -0000 1.39
> --- src/include/commands/dbcommands.h 3 Jul 2005 22:51:53 -0000
> ***************
> *** 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, Oid newOwnerId);
>
> Index: src/include/nodes/nodes.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/nodes/nodes.h,v
> retrieving revision 1.172
> diff -c -r1.172 nodes.h
> *** src/include/nodes/nodes.h 28 Jun 2005 05:09:13 -0000 1.172
> --- src/include/nodes/nodes.h 3 Jul 2005 22:52:00 -0000
> ***************
> *** 270,275 ****
> --- 270,276 ----
> T_ReindexStmt,
> T_CheckPointStmt,
> T_CreateSchemaStmt,
> + T_AlterDatabaseStmt,
> T_AlterDatabaseSetStmt,
> T_AlterRoleSetStmt,
> T_CreateConversionStmt,
> Index: src/include/nodes/parsenodes.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
> retrieving revision 1.285
> diff -c -r1.285 parsenodes.h
> *** src/include/nodes/parsenodes.h 28 Jun 2005 19:51:24 -0000 1.285
> --- src/include/nodes/parsenodes.h 3 Jul 2005 22:52:25 -0000
> ***************
> *** 1611,1616 ****
> --- 1611,1623 ----
> * 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;
> Index: src/include/storage/proc.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/storage/proc.h,v
> retrieving revision 1.79
> diff -c -r1.79 proc.h
> *** src/include/storage/proc.h 17 Jun 2005 22:32:50 -0000 1.79
> --- src/include/storage/proc.h 3 Jul 2005 22:52:29 -0000
> ***************
> *** 71,76 ****
> --- 71,77 ----
>
> int pid; /* This backend's process id, or 0 */
> Oid databaseId; /* OID of database this backend is using */
> + Oid roleId; /* OID of role using conencted to backend */
>
> /* Info about LWLock the process is currently waiting for, if any. */
> bool lwWaiting; /* true if waiting for an LW lock */
> Index: src/include/storage/procarray.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/include/storage/procarray.h,v
> retrieving revision 1.2
> diff -c -r1.2 procarray.h
> *** src/include/storage/procarray.h 17 Jun 2005 22:32:50 -0000 1.2
> --- src/include/storage/procarray.h 3 Jul 2005 22:52:30 -0000
> ***************
> *** 31,36 ****
> --- 31,38 ----
> 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);
> Index: src/tools/pgindent/pgindent
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/tools/pgindent/pgindent,v
> retrieving revision 1.75
> diff -c -r1.75 pgindent
> *** src/tools/pgindent/pgindent 28 Jun 2005 23:55:30 -0000 1.75
> --- src/tools/pgindent/pgindent 3 Jul 2005 22:53:03 -0000
> ***************
> *** 177,182 ****
> --- 177,183 ----
> -TAllocSetContext \
> -TAllocateDesc \
> -TAllocateDescKind \
> + -TAlterDatabaseStmt \
> -TAlterDatabaseSetStmt \
> -TAlterDomainStmt \
> -TAlterFunctionStmt \

--
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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> The new syntax for this command is CREATE/ALTER DATABASE/USER:
> | MAX CONNECTIONS Iconst
> This adds 'max' as a keyword, though at a fairly unreserved level, I
> think. Should we use the syntax LIMIT CONNECTIONS so we don't have to
> add MAX as a keyword at all?

I didn't like that either. I was thinking of just CONNECTIONS.
LIMIT CONNECTIONS sort of works grammatically, I guess.

regards, tom lane

Bruce Momjian wrote:

>The new syntax for this command is CREATE/ALTER DATABASE/USER:
>
> | MAX CONNECTIONS Iconst
>
>This adds 'max' as a keyword, though at a fairly unreserved level, I
>think. Should we use the syntax LIMIT CONNECTIONS so we don't have to
>add MAX as a keyword at all?
>
Yeah I have no problem with LIMIT CONNECTIONS, will you change it or
should I do it ?

btw where has new keyword to be added to not be added "at a fairly
unreserved level" ? (MAX is also added to keywords.c in that patch)

--
Regards
Petr Jelinek (PJMODOS)

Petr Jelinek wrote:
> Bruce Momjian wrote:
>
> >The new syntax for this command is CREATE/ALTER DATABASE/USER:
> >
> > | MAX CONNECTIONS Iconst
> >
> >This adds 'max' as a keyword, though at a fairly unreserved level, I
> >think. Should we use the syntax LIMIT CONNECTIONS so we don't have to
> >add MAX as a keyword at all?
> >
> Yeah I have no problem with LIMIT CONNECTIONS, will you change it or
> should I do it ?

I will do it.

> btw where has new keyword to be added to not be added "at a fairly
> unreserved level" ? (MAX is also added to keywords.c in that patch)

Right, I will remove the MAX addition. parser/gram.y has this comment:

/*
* Keyword classification lists. Generally, every keyword present in
* the Postgres grammar should appear in exactly one of these lists.
*
* Put a new keyword into the first list that it can go into without causing
* shift or reduce conflicts. The earlier lists define "less reserved"
* categories of keywords.
*/

I will check that your additions are in the right place.

--
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

I have "worked over" your patch and I think it is ready for application.

I changed the syntax to CONNECTION LIMIT, which seems most natural. We
could skip CONNECTION and just use a LIMIT keyword, but that seems too
terse.

I removed your use of the pg_auth flat file. By the time you have the
PROC entry to do your lookups, you might as well just use the system
cache.

There is a race condition in the code because we set our PROC entry
before we check for other entries. If there is one connection left and
two backends do this at the same time, they would both fail, while one
should fail and the other succeed. Without a lock, I see no way to avoid
it so I just commented it in the code.

Also, I felt that zero should mean allow no/zero connections, rather
than representing unlimited connections. I used -1 for unlimited. We
can either document the use of -1, or add syntax to allow NO CONNECTION
LIMIT, or something like that.

The patch requires a catalog version update when applied.

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

Petr Jelinek wrote:
> Stephen Frost wrote:
>
> >This should almost certainly be a pg_database_ownercheck() call instead.
> >
> >
> Right there wasn't pg_database_ownercheck at the time I was writing it,
> fixed
>
> >The rest needs to be updated for roles, but looks like it should be
> >pretty easy to do. Much of it just needs to be repatched, the parts
> >that do need to be changed look to be pretty simple changes.
> >
> >
> Done.
>
> >I believe the use of SessionUserId is probably correct in this patch.
> >This does mean that this patch will only be for canlogin roles, but that
> >seems like it's probably correct. Handling roles w/ members would
> >require much more thought.
> >
> >
> I don't think that having max connection for roles w/ members is doable
> because you can have 5 roles which has 1 user as member and each role
> has different number of max conections and there is no right way to
> decide what to do.
>
>
> New version which works with roles is attached (diffed against cvs),
> everything else is mostly same.
> I also had to readd roleid to flatfiles because I need it in
> InitProcess() function.

--
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

Bruce Momjian wrote:

>I removed your use of the pg_auth flat file. By the time you have the
>PROC entry to do your lookups, you might as well just use the system
>cache.
>
>There is a race condition in the code because we set our PROC entry
>before we check for other entries. If there is one connection left and
>two backends do this at the same time, they would both fail, while one
>should fail and the other succeed. Without a lock, I see no way to avoid
>it so I just commented it in the code.
>
>
Yeah my working version was doing this too but I wanted to avoid lock on
PROC array and that race condition and because pg_auth is loaded anyway
I used it.

>Also, I felt that zero should mean allow no/zero connections, rather
>than representing unlimited connections. I used -1 for unlimited. We
>can either document the use of -1, or add syntax to allow NO CONNECTION
>LIMIT, or something like that.
>
>
Right, maybe we could remove datallowconn from pg_database (in future)
if we can achieve same thing using datconnlimit = 0 ?

>The patch requires a catalog version update when applied.
>
>
Yes, thanks for your work on this patch, I will write documentation for
it in next few days.

--
Regards
Petr Jelinek (PJMODOS)

Petr Jelinek wrote:
> Bruce Momjian wrote:
>
> >I removed your use of the pg_auth flat file. By the time you have the
> >PROC entry to do your lookups, you might as well just use the system
> >cache.
> >
> >There is a race condition in the code because we set our PROC entry
> >before we check for other entries. If there is one connection left and
> >two backends do this at the same time, they would both fail, while one
> >should fail and the other succeed. Without a lock, I see no way to avoid
> >it so I just commented it in the code.
> >
> >
> Yeah my working version was doing this too but I wanted to avoid lock on
> PROC array and that race condition and because pg_auth is loaded anyway
> I used it.

Well, we are locking the PROC array for the db scan as well, so I don't
see a difference for user. Also, I don't see how it would avoid the
race condition. We could scan PROC and then set our user value, but
that would allow possibly too many connections rather than too few.

> >Also, I felt that zero should mean allow no/zero connections, rather
> >than representing unlimited connections. I used -1 for unlimited. We
> >can either document the use of -1, or add syntax to allow NO CONNECTION
> >LIMIT, or something like that.
> >
> >
> Right, maybe we could remove datallowconn from pg_database (in future)
> if we can achieve same thing using datconnlimit = 0 ?

Yes, we certainly could, but I am betting we would need both because if
someone wanted to close down a database, _but_ keep the existing limit
so it could be resetored later, they would still use datallowconn.

> >The patch requires a catalog version update when applied.
> >
> >
> Yes, thanks for your work on this patch, I will write documentation for
> it in next few days.

Thanks.

--
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

Here is promised documentation.
Be warned that both my writing skills and my english are far from good :)

--
Regards
Petr Jelinek (PJMODOS)

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I have "worked over" your patch and I think it is ready for application.

I've made another pass over this and should be able to commit tomorrow
(I'm about to knock off for today, and ran out of time to test
pg_dumpall). One thing I changed was that it didn't make sense to me
for CREATE DATABASE to copy the template database's datconnlimit.
We don't copy its datallowconn or datconfig, so why datconnlimit?

BTW I disagree with removing datallowconn; that is different from
datconnlimit = 0 because it is enforced even against superusers.
(Similar remarks apply for rolcanlogin vs rolconnlimit.)

regards, tom lane

Am Montag, 25. Juli 2005 18:31 schrieb Tom Lane:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > The new syntax for this command is CREATE/ALTER DATABASE/USER:
> > | MAX CONNECTIONS Iconst
> >
> > This adds 'max' as a keyword, though at a fairly unreserved level, I
> > think. Should we use the syntax LIMIT CONNECTIONS so we don't have to
> > add MAX as a keyword at all?
>
> I didn't like that either. I was thinking of just CONNECTIONS.
> LIMIT CONNECTIONS sort of works grammatically, I guess.

Would this not work in the context of the general user-specific ALTER USER ...
SET something = something?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Am Montag, 25. Juli 2005 18:31 schrieb Tom Lane:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > The new syntax for this command is CREATE/ALTER DATABASE/USER:
> > > | MAX CONNECTIONS Iconst
> > >
> > > This adds 'max' as a keyword, though at a fairly unreserved level, I
> > > think. Should we use the syntax LIMIT CONNECTIONS so we don't have to
> > > add MAX as a keyword at all?
> >
> > I didn't like that either. I was thinking of just CONNECTIONS.
> > LIMIT CONNECTIONS sort of works grammatically, I guess.
>
> Would this not work in the context of the general user-specific ALTER USER ...
> SET something = something?

No because it isn't a GUC variable, it is per-user/db value. We could
have used that syntax, but it might confuse people.

--
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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Peter Eisentraut wrote:
>> Would this not work in the context of the general user-specific ALTER USER ...
>> SET something = something?

> No because it isn't a GUC variable, it is per-user/db value. We could
> have used that syntax, but it might confuse people.

Yeah --- casting it as a GUC would create issues like "what is the
global default?". I think treating it as a hard-wired feature is fine.

regards, tom lane

Am Montag, 1. August 2005 16:08 schrieb Bruce Momjian:
> > Would this not work in the context of the general user-specific ALTER
> > USER ... SET something = something?
>
> No because it isn't a GUC variable, it is per-user/db value.

GUC supports per-user/per-db values.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Am Montag, 1. August 2005 16:08 schrieb Bruce Momjian:
>>> Would this not work in the context of the general user-specific ALTER
>>> USER ... SET something = something?
>>
>> No because it isn't a GUC variable, it is per-user/db value.

> GUC supports per-user/per-db values.

But not in the style that we'd want this to work. You couldn't just
invent a single "connection_limit" variable, because a per-user setting
would override a per-database setting, which is not the desired
behavior. You'd have to invent two separate GUC variables, and there
would be nothing except convention enforcing that they be set through
ALTER USER and ALTER DATABASE rather than at other random places.

We could do it that way, but it strikes me as messy and confusing,
and I don't see any actual benefit other than saving a few lines of
(already written) code. In what way would a GUC-based implementation
be more useful than what's there?

regards, tom lane

Peter Eisentraut wrote:

>GUC supports per-user/per-db values.
>
>
We already had discussion here about GUC for this and we agreed that
catalog change is better than new GUC variable in this case.

--
Regards
Petr Jelinek (PJMODOS)