per user/database connections limit again

Started by Petr Jelinekabout 21 years ago26 messagespatches

petr@2ndquadrant.com

about 21 years ago

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)

Bruce Momjian

bruce@momjian.us

about 21 years ago

In reply to: Petr Jelinek (#1)

Re: per user/database connections limit again

We will need these:

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

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

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

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

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

Petr Jelinek wrote:

Hi,

I attached second try of per-database and per-user connection limit for
your review.

This time I am using information stored in ProcArray to get number of
connections - I modified PGPROC struct to also include userid.

Limits for user and database are stored in catalog tables. This aproach
led to implementation of "universal" ALTER DATABASE query (I followed
ALTER USER ad ALTER DATABASE ... RENAME implementatons). So queries for
setting maximum connections look like this: CREATE|ALTER DATABASE|USER
name MAX CONNECTIONS = 20;
Maximum connections defaults to zero which means unlimited (limited by
global maximum only) and isn't enforced for superusers.

The actual check for maximum conenctions is done in ReverifyMyDatabase
for database and InitializeSessionUser for user because we don't have
information from system catalog before so we don't know how many
connections are allowed.

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

Diff is made against cvs from today morning GMT (apply with -p1 if you
want to test it) - cvs is down now so I can't make diff against repository.

--
Regards
Petr Jelinek (PJMODOS)

diff -Nacr my-cvs/src/backend/commands/dbcommands.c my-aproach2/src/backend/commands/dbcommands.c
*** my-cvs/src/backend/commands/dbcommands.c	Sun Jun 26 00:47:30 2005
--- my-aproach2/src/backend/commands/dbcommands.c	Tue Jun 28 11:26:08 2005
***************
*** 53,60 ****

/* non-export function prototypes */
static bool get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
! 			int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
! 			Oid *dbLastSysOidP,
TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
Oid *dbTablespace);
static bool have_createdb_privilege(void);
--- 53,60 ----

/* non-export function prototypes */
static bool get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
! 			int *encodingP, int *dbMaxConnP, bool *dbIsTemplateP, 
! 			bool *dbAllowConnP,	Oid *dbLastSysOidP,
TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
Oid *dbTablespace);
static bool have_createdb_privilege(void);
***************
*** 74,79 ****
--- 74,80 ----
int			src_encoding;
bool		src_istemplate;
bool		src_allowconn;
+ 	int			src_maxconn;
Oid			src_lastsysoid;
TransactionId src_vacuumxid;
TransactionId src_frozenxid;
***************
*** 91,100 ****
--- 92,103 ----
DefElem    *downer = NULL;
DefElem    *dtemplate = NULL;
DefElem    *dencoding = NULL;
+ 	DefElem    *dmaxconn = NULL;
char	   *dbname = stmt->dbname;
char	   *dbowner = NULL;
const char *dbtemplate = NULL;
int			encoding = -1;
+ 	int			dbmaxconn = -1;

#ifndef WIN32
char		buf[2 * MAXPGPATH + 100];
***************
*** 140,145 ****
--- 143,156 ----
errmsg("conflicting or redundant options")));
dencoding = defel;
}
+ 		else if (strcmp(defel->defname, "maxconnections") == 0)
+ 		{
+ 			if (dmaxconn)
+ 				ereport(ERROR,
+ 						(errcode(ERRCODE_SYNTAX_ERROR),
+ 						 errmsg("conflicting or redundant options")));
+ 			dmaxconn = defel;
+ 		}
else if (strcmp(defel->defname, "location") == 0)
{
ereport(WARNING,
***************
*** 185,190 ****
--- 196,203 ----
elog(ERROR, "unrecognized node type: %d",
nodeTag(dencoding->arg));
}
+ 	if (dmaxconn && dmaxconn->arg)
+ 		dbmaxconn = intVal(dmaxconn->arg);

/* obtain sysid of proposed owner */
if (dbowner)
***************
*** 218,224 ****
* idea, so accept possibility of race to create.  We will check again
* after we grab the exclusive lock.
*/
! 	if (get_db_info(dbname, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_DATABASE),
--- 231,237 ----
* idea, so accept possibility of race to create.  We will check again
* after we grab the exclusive lock.
*/
! 	if (get_db_info(dbname, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_DATABASE),
***************
*** 231,238 ****
dbtemplate = "template1";		/* Default template database name */

if (!get_db_info(dbtemplate, &src_dboid, &src_owner, &src_encoding,
! 					 &src_istemplate, &src_allowconn, &src_lastsysoid,
! 					 &src_vacuumxid, &src_frozenxid, &src_deftablespace))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
errmsg("template database \"%s\" does not exist", dbtemplate)));
--- 244,252 ----
dbtemplate = "template1";		/* Default template database name */

if (!get_db_info(dbtemplate, &src_dboid, &src_owner, &src_encoding,
! 					 &src_maxconn, &src_istemplate, &src_allowconn, 
! 					 &src_lastsysoid, &src_vacuumxid, &src_frozenxid, 
! 					 &src_deftablespace))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
errmsg("template database \"%s\" does not exist", dbtemplate)));
***************
*** 266,271 ****
--- 280,289 ----
if (encoding < 0)
encoding = src_encoding;

+ 	/* If dbmaxconn is defaulted, use source's dbmaxconn */
+ 	if (dbmaxconn < 0)
+ 		dbmaxconn = src_maxconn;
+ 
/* Some encodings are client only */
if (!PG_VALID_BE_ENCODING(encoding))
ereport(ERROR,
***************
*** 461,467 ****
pg_database_rel = heap_open(DatabaseRelationId, ExclusiveLock);

/* Check to see if someone else created same DB name meanwhile. */
! 	if (get_db_info(dbname, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL))
{
/* Don't hold lock while doing recursive remove */
--- 479,485 ----
pg_database_rel = heap_open(DatabaseRelationId, ExclusiveLock);

/* Check to see if someone else created same DB name meanwhile. */
! 	if (get_db_info(dbname, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL))
{
/* Don't hold lock while doing recursive remove */
***************
*** 487,492 ****
--- 505,511 ----
new_record[Anum_pg_database_encoding - 1] = Int32GetDatum(encoding);
new_record[Anum_pg_database_datistemplate - 1] = BoolGetDatum(false);
new_record[Anum_pg_database_datallowconn - 1] = BoolGetDatum(true);
+ 	new_record[Anum_pg_database_datmaxconn - 1] = Int32GetDatum(dbmaxconn);
new_record[Anum_pg_database_datlastsysoid - 1] = ObjectIdGetDatum(src_lastsysoid);
new_record[Anum_pg_database_datvacuumxid - 1] = TransactionIdGetDatum(src_vacuumxid);
new_record[Anum_pg_database_datfrozenxid - 1] = TransactionIdGetDatum(src_frozenxid);
***************
*** 588,594 ****
*/
pgdbrel = heap_open(DatabaseRelationId, ExclusiveLock);

! 	if (!get_db_info(dbname, &db_id, &db_owner, NULL,
&db_istemplate, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
--- 607,613 ----
*/
pgdbrel = heap_open(DatabaseRelationId, ExclusiveLock);

! 	if (!get_db_info(dbname, &db_id, &db_owner, NULL, NULL,
&db_istemplate, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
***************
*** 784,789 ****
--- 803,894 ----

/*
+  * ALTER DATABASE name ...
+  */
+ void
+ AlterDatabase(AlterDatabaseStmt *stmt)
+ {
+ 	Datum		new_record[Natts_pg_database];
+ 	char		new_record_nulls[Natts_pg_database];
+ 	char		new_record_repl[Natts_pg_database];
+ 	Relation	rel;
+ 	HeapTuple	tuple,
+ 				newtuple;
+ 	ScanKeyData scankey;
+ 	SysScanDesc scan;
+ 	ListCell   *option;
+ 	int			maxconn = -1;	/* Maximum connections allowed */
+ 
+ 	DefElem    *dmaxconn = NULL;
+ 
+ 	/* Extract options from the statement node tree */
+ 	foreach(option, stmt->options)
+ 	{
+ 		DefElem    *defel = (DefElem *) lfirst(option);
+ 
+ 		if (strcmp(defel->defname, "maxconnections") == 0)
+ 		{
+ 			if (dmaxconn)
+ 				ereport(ERROR,
+ 						(errcode(ERRCODE_SYNTAX_ERROR),
+ 						 errmsg("conflicting or redundant options")));
+ 			dmaxconn = defel;
+ 		}
+ 	}
+ 
+ 	if (dmaxconn)
+ 		maxconn = intVal(dmaxconn->arg);
+ 
+ 	/*
+ 	 * We don't need ExclusiveLock since we aren't updating the
+ 	 * flat file.
+ 	 */
+ 	rel = heap_open(DatabaseRelationId, RowExclusiveLock);
+ 	ScanKeyInit(&scankey,
+ 				Anum_pg_database_datname,
+ 				BTEqualStrategyNumber, F_NAMEEQ,
+ 				NameGetDatum(stmt->dbname));
+ 	scan = systable_beginscan(rel, DatabaseNameIndexId, true,
+ 							  SnapshotNow, 1, &scankey);
+ 	tuple = systable_getnext(scan);
+ 	if (!HeapTupleIsValid(tuple))
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_UNDEFINED_DATABASE),
+ 				 errmsg("database \"%s\" does not exist", stmt->dbname)));
+ 
+ 	if (!(superuser()
+ 		|| ((Form_pg_database) GETSTRUCT(tuple))->datdba == GetUserId()))
+ 		aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
+ 					   stmt->dbname);
+ 
+ 	/*
+ 	 * Build an updated tuple, perusing the information just obtained
+ 	 */
+ 	MemSet(new_record, 0, sizeof(new_record));
+ 	MemSet(new_record_nulls, ' ', sizeof(new_record_nulls));
+ 	MemSet(new_record_repl, ' ', sizeof(new_record_repl));
+ 
+ 	if (maxconn >= 0)
+ 	{
+ 		new_record[Anum_pg_database_datmaxconn - 1] = Int32GetDatum(maxconn);
+ 		new_record_repl[Anum_pg_database_datmaxconn - 1] = 'r';
+ 	}
+ 
+ 	newtuple = heap_modifytuple(tuple, RelationGetDescr(rel), new_record,
+ 								 new_record_nulls, new_record_repl);
+ 	simple_heap_update(rel, &tuple->t_self, newtuple);
+ 
+ 	/* Update indexes */
+ 	CatalogUpdateIndexes(rel, newtuple);
+ 
+ 	systable_endscan(scan);
+ 
+ 	/* Close pg_database, but keep lock till commit */
+ 	heap_close(rel, NoLock);
+ }
+ 
+ 
+ /*
* ALTER DATABASE name SET ...
*/
void
***************
*** 973,980 ****

static bool
get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
! 			int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
! 			Oid *dbLastSysOidP,
TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
Oid *dbTablespace)
{
--- 1078,1085 ----

static bool
get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
! 			int *encodingP, int *dbMaxConnP, bool *dbIsTemplateP, 
! 			bool *dbAllowConnP,	Oid *dbLastSysOidP,
TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
Oid *dbTablespace)
{
***************
*** 1019,1024 ****
--- 1124,1132 ----
/* allowing connections? */
if (dbAllowConnP)
*dbAllowConnP = dbform->datallowconn;
+ 		/* maximum connections */
+ 		if (dbMaxConnP)
+ 			*dbMaxConnP = dbform->datmaxconn;
/* last system OID used in database */
if (dbLastSysOidP)
*dbLastSysOidP = dbform->datlastsysoid;
diff -Nacr my-cvs/src/backend/commands/user.c my-aproach2/src/backend/commands/user.c
*** my-cvs/src/backend/commands/user.c	Thu Apr 14 22:03:24 2005
--- my-aproach2/src/backend/commands/user.c	Tue Jun 28 11:26:18 2005
***************
*** 64,69 ****
--- 64,70 ----
int			sysid = 0;		/* PgSQL system id (valid if havesysid) */
bool		createdb = false;		/* Can the user create databases? */
bool		createuser = false;		/* Can this user create users? */
+ 	int			maxconn = false;		/* maximum connections allowed */
List	   *groupElts = NIL;	/* The groups the user is a member of */
char	   *validUntil = NULL;		/* The time the login is valid
* until */
***************
*** 73,78 ****
--- 74,80 ----
DefElem    *dcreateuser = NULL;
DefElem    *dgroupElts = NULL;
DefElem    *dvalidUntil = NULL;
+ 	DefElem    *dmaxconn = NULL;

/* Extract options from the statement node tree */
foreach(option, stmt->options)
***************
*** 117,122 ****
--- 119,132 ----
errmsg("conflicting or redundant options")));
dcreateuser = defel;
}
+ 		else if (strcmp(defel->defname, "maxconnections") == 0)
+ 		{
+ 			if (dmaxconn)
+ 				ereport(ERROR,
+ 						(errcode(ERRCODE_SYNTAX_ERROR),
+ 						 errmsg("conflicting or redundant options")));
+ 			dmaxconn = defel;
+ 		}
else if (strcmp(defel->defname, "groupElts") == 0)
{
if (dgroupElts)
***************
*** 142,147 ****
--- 152,165 ----
createdb = intVal(dcreatedb->arg) != 0;
if (dcreateuser)
createuser = intVal(dcreateuser->arg) != 0;
+ 	if (dmaxconn)
+ 	{
+ 		maxconn = intVal(dmaxconn->arg);
+ 		if (maxconn < 0)
+ 			ereport(ERROR,
+ 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ 					 errmsg("MAX CONNECTIONS must not be negative")));
+ 	}
if (dsysid)
{
sysid = intVal(dsysid->arg);
***************
*** 233,238 ****
--- 251,257 ----
new_record[Anum_pg_shadow_usesuper - 1] = BoolGetDatum(createuser);
/* superuser gets catupd right by default */
new_record[Anum_pg_shadow_usecatupd - 1] = BoolGetDatum(createuser);
+ 	new_record[Anum_pg_shadow_usemaxconn - 1] = Int32GetDatum(maxconn);

if (password)
{
***************
*** 317,328 ****
--- 336,349 ----
char		encrypted_password[MD5_PASSWD_LEN + 1];
int			createdb = -1;	/* Can the user create databases? */
int			createuser = -1;	/* Can this user create users? */
+ 	int			maxconn = -1;	/* Maximum connections allowed */
char	   *validUntil = NULL;		/* The time the login is valid
* until */
DefElem    *dpassword = NULL;
DefElem    *dcreatedb = NULL;
DefElem    *dcreateuser = NULL;
DefElem    *dvalidUntil = NULL;
+ 	DefElem    *dmaxconn = NULL;

/* Extract options from the statement node tree */
foreach(option, stmt->options)
***************
*** 359,364 ****
--- 380,393 ----
errmsg("conflicting or redundant options")));
dcreateuser = defel;
}
+ 		else if (strcmp(defel->defname, "maxconnections") == 0)
+ 		{
+ 			if (dmaxconn)
+ 				ereport(ERROR,
+ 						(errcode(ERRCODE_SYNTAX_ERROR),
+ 						 errmsg("conflicting or redundant options")));
+ 			dmaxconn = defel;
+ 		}
else if (strcmp(defel->defname, "validUntil") == 0)
{
if (dvalidUntil)
***************
*** 376,381 ****
--- 405,412 ----
createdb = intVal(dcreatedb->arg);
if (dcreateuser)
createuser = intVal(dcreateuser->arg);
+ 	if (dmaxconn)
+ 		maxconn = intVal(dmaxconn->arg);
if (dvalidUntil)
validUntil = strVal(dvalidUntil->arg);
if (dpassword)
***************
*** 427,432 ****
--- 458,469 ----
{
new_record[Anum_pg_shadow_usecreatedb - 1] = BoolGetDatum(createdb > 0);
new_record_repl[Anum_pg_shadow_usecreatedb - 1] = 'r';
+ 	}
+ 
+ 	if (maxconn >= 0)
+ 	{
+ 		new_record[Anum_pg_shadow_usemaxconn - 1] = Int32GetDatum(maxconn);
+ 		new_record_repl[Anum_pg_shadow_usemaxconn - 1] = 'r';
}

/*
diff -Nacr my-cvs/src/backend/nodes/copyfuncs.c my-aproach2/src/backend/nodes/copyfuncs.c
*** my-cvs/src/backend/nodes/copyfuncs.c	Mon Jun 27 00:05:38 2005
--- my-aproach2/src/backend/nodes/copyfuncs.c	Tue Jun 28 06:07:50 2005
***************
*** 2191,2196 ****
--- 2191,2207 ----
return newnode;
}

+ static AlterDatabaseStmt *
+ _copyAlterDatabaseStmt(AlterDatabaseStmt *from)
+ {
+ 	AlterDatabaseStmt *newnode = makeNode(AlterDatabaseStmt);
+ 
+ 	COPY_STRING_FIELD(dbname);
+ 	COPY_NODE_FIELD(options);
+ 
+ 	return newnode;
+ }
+ 
static AlterDatabaseSetStmt *
_copyAlterDatabaseSetStmt(AlterDatabaseSetStmt *from)
{
diff -Nacr my-cvs/src/backend/nodes/equalfuncs.c my-aproach2/src/backend/nodes/equalfuncs.c
*** my-cvs/src/backend/nodes/equalfuncs.c	Mon Jun 27 00:05:38 2005
--- my-aproach2/src/backend/nodes/equalfuncs.c	Tue Jun 28 06:07:50 2005
***************
*** 1141,1146 ****
--- 1141,1155 ----
}

static bool
+ _equalAlterDatabaseStmt(AlterDatabaseStmt *a, AlterDatabaseStmt *b)
+ {
+ 	COMPARE_STRING_FIELD(dbname);
+ 	COMPARE_NODE_FIELD(options);
+ 
+ 	return true;
+ }
+ 
+ static bool
_equalAlterDatabaseSetStmt(AlterDatabaseSetStmt *a, AlterDatabaseSetStmt *b)
{
COMPARE_STRING_FIELD(dbname);
diff -Nacr my-cvs/src/backend/parser/gram.y my-aproach2/src/backend/parser/gram.y
*** my-cvs/src/backend/parser/gram.y	Mon Jun 27 00:05:38 2005
--- my-aproach2/src/backend/parser/gram.y	Tue Jun 28 11:26:30 2005
***************
*** 131,139 ****
}

%type <node>	stmt schema_stmt
! 		AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt
! 		AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt
! 		AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
CreateSchemaStmt CreateSeqStmt CreateStmt CreateTableSpaceStmt
--- 131,139 ----
}

%type <node> stmt schema_stmt
! AlterDatabaseStmt AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt
! AlterOwnerStmt AlterSeqStmt AlterTableStmt AlterUserStmt
! AlterUserSetStmt AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
CreateSchemaStmt CreateSeqStmt CreateStmt CreateTableSpaceStmt
***************
*** 164,171 ****

%type <dbehavior> opt_drop_behavior

! %type <list> createdb_opt_list copy_opt_list transaction_mode_list
! %type <defelt> createdb_opt_item copy_opt_item transaction_mode_item

%type <ival>	opt_lock lock_type cast_context
%type <boolean>	opt_force opt_or_replace
--- 164,173 ----

%type <dbehavior> opt_drop_behavior

! %type <list> createdb_opt_list alterdb_opt_list copy_opt_list
! transaction_mode_list
! %type <defelt> createdb_opt_item alterdb_opt_item copy_opt_item
! transaction_mode_item

%type <ival> opt_lock lock_type cast_context
%type <boolean> opt_force opt_or_replace
***************
*** 346,352 ****
CACHE CALLED CASCADE CASE CAST CHAIN CHAR_P
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
! COMMITTED CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY CREATE CREATEDB
CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

--- 348,354 ----
CACHE CALLED CASCADE CASE CAST CHAIN CHAR_P
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
! 	COMMITTED CONNECTIONS CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY CREATE CREATEDB
CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

***************
*** 377,383 ****
LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
LOCK_P

! MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE

NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
NOCREATEUSER NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P
--- 379,385 ----
LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
LOCK_P

! MATCH MAX MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE

NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
NOCREATEUSER NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P
***************
*** 490,496 ****
;

stmt :
! 			AlterDatabaseSetStmt
| AlterDomainStmt
| AlterFunctionStmt
| AlterGroupStmt
--- 492,499 ----
;

stmt :
! 			AlterDatabaseStmt
! 			| AlterDatabaseSetStmt
| AlterDomainStmt
| AlterFunctionStmt
| AlterGroupStmt
***************
*** 688,693 ****
--- 691,700 ----
{
$$ = makeDefElem("createuser", (Node *)makeInteger(FALSE));
}
+ 			| MAX CONNECTIONS Iconst
+ 				{
+ 					$$ = makeDefElem("maxconnections", (Node *)makeInteger($3));
+ 				}
| IN_P GROUP_P user_list
{
$$ = makeDefElem("groupElts", (Node *)$3);
***************
*** 4294,4299 ****
--- 4301,4310 ----
{
$$ = makeDefElem("encoding", NULL);
}
+ 			| MAX CONNECTIONS opt_equal Iconst
+ 				{
+ 					$$ = makeDefElem("maxconnections", (Node *)makeInteger($4));
+ 				}
| OWNER opt_equal name
{
$$ = makeDefElem("owner", (Node *)makeString($3));
***************
*** 4320,4325 ****
--- 4331,4346 ----
*
*****************************************************************************/

+ AlterDatabaseStmt:
+  			ALTER DATABASE database_name opt_with alterdb_opt_list
+ 				 {
+ 					AlterDatabaseStmt *n = makeNode(AlterDatabaseStmt);
+ 					n->dbname = $3;
+ 					n->options = $5;
+ 					$$ = (Node *)n;
+ 				 }
+ 		;
+ 
AlterDatabaseSetStmt:
ALTER DATABASE database_name SET set_rest
{
***************
*** 4340,4345 ****
--- 4361,4379 ----
;

+ alterdb_opt_list:
+ 			alterdb_opt_list alterdb_opt_item		{ $$ = lappend($1, $2); }
+ 			| /* EMPTY */							{ $$ = NIL; }
+ 		;
+ 
+ alterdb_opt_item:
+ 			MAX CONNECTIONS opt_equal Iconst
+ 				{
+ 					$$ = makeDefElem("maxconnections", (Node *)makeInteger($4));
+ 				}
+ 		;
+ 
+ 
/*****************************************************************************
*
*		DROP DATABASE
***************
*** 7770,7775 ****
--- 7804,7810 ----
| COMMENT
| COMMIT
| COMMITTED
+ 			| CONNECTIONS
| CONSTRAINTS
| CONVERSION_P
| COPY
***************
*** 7835,7840 ****
--- 7870,7876 ----
| LOCATION
| LOCK_P
| MATCH
+ 			| MAX
| MAXVALUE
| MINUTE_P
| MINVALUE
diff -Nacr my-cvs/src/backend/parser/keywords.c my-aproach2/src/backend/parser/keywords.c
*** my-cvs/src/backend/parser/keywords.c	Mon Jun 27 00:05:40 2005
--- my-aproach2/src/backend/parser/keywords.c	Tue Jun 28 06:07:50 2005
***************
*** 82,87 ****
--- 82,88 ----
{"comment", COMMENT},
{"commit", COMMIT},
{"committed", COMMITTED},
+ 	{"connections", CONNECTIONS},
{"constraint", CONSTRAINT},
{"constraints", CONSTRAINTS},
{"conversion", CONVERSION_P},
***************
*** 198,203 ****
--- 199,205 ----
{"location", LOCATION},
{"lock", LOCK_P},
{"match", MATCH},
+ 	{"max", MAX},
{"maxvalue", MAXVALUE},
{"minute", MINUTE_P},
{"minvalue", MINVALUE},
diff -Nacr my-cvs/src/backend/storage/ipc/procarray.c my-aproach2/src/backend/storage/ipc/procarray.c
*** my-cvs/src/backend/storage/ipc/procarray.c	Sat Jun 18 00:32:46 2005
--- my-aproach2/src/backend/storage/ipc/procarray.c	Tue Jun 28 06:07:50 2005
***************
*** 734,739 ****
--- 734,790 ----
}

+ /*
+  * CountDBBackends --- count backends that are using specified database
+  */
+ int
+ CountDBBackends(Oid databaseid)
+ {
+ 	ProcArrayStruct *arrayP = procArray;
+ 	int			count = 0;
+ 	int			index;
+ 
+ 	LWLockAcquire(ProcArrayLock, LW_SHARED);
+ 
+ 	for (index = 0; index < arrayP->numProcs; index++)
+ 	{
+ 		PGPROC	   *proc = arrayP->procs[index];
+ 
+ 		if (proc->pid != 0 && proc->databaseId == databaseid)
+ 			count++;
+ 	}
+ 
+ 	LWLockRelease(ProcArrayLock);
+ 
+ 	return count;
+ }
+ 
+ /*
+  * CountUserBackends --- count backends that are used by specified user
+  */
+ int
+ CountUserBackends(AclId userid)
+ {
+ 	ProcArrayStruct *arrayP = procArray;
+ 	int			count = 0;
+ 	int			index;
+ 
+ 	LWLockAcquire(ProcArrayLock, LW_SHARED);
+ 
+ 	for (index = 0; index < arrayP->numProcs; index++)
+ 	{
+ 		PGPROC	   *proc = arrayP->procs[index];
+ 
+ 		if (proc->pid != 0 && proc->userId == userid)
+ 			count++;
+ 	}
+ 
+ 	LWLockRelease(ProcArrayLock);
+ 
+ 	return count;
+ }
+ 
+ 
#define XidCacheRemove(i) \
do { \
MyProc->subxids.xids[i] = MyProc->subxids.xids[MyProc->subxids.nxids - 1]; \
diff -Nacr my-cvs/src/backend/storage/lmgr/proc.c my-aproach2/src/backend/storage/lmgr/proc.c
*** my-cvs/src/backend/storage/lmgr/proc.c	Sat Jun 18 00:32:46 2005
--- my-aproach2/src/backend/storage/lmgr/proc.c	Tue Jun 28 06:39:46 2005
***************
*** 254,259 ****
--- 254,260 ----
MyProc->xmin = InvalidTransactionId;
MyProc->pid = MyProcPid;
MyProc->databaseId = MyDatabaseId;
+ 	MyProc->userId = GetSessionUserId();
MyProc->lwWaiting = false;
MyProc->lwExclusive = false;
MyProc->lwWaitLink = NULL;
diff -Nacr my-cvs/src/backend/tcop/utility.c my-aproach2/src/backend/tcop/utility.c
*** my-cvs/src/backend/tcop/utility.c	Wed Jun 22 23:14:30 2005
--- my-aproach2/src/backens/tcop/utility.c	Tue Jun 28 06:07:50 2005
***************
*** 276,281 ****
--- 276,282 ----

switch (nodeTag(parsetree))
{
+ 		case T_AlterDatabaseStmt:
case T_AlterDatabaseSetStmt:
case T_AlterDomainStmt:
case T_AlterFunctionStmt:
***************
*** 786,791 ****
--- 787,796 ----

case T_CreatedbStmt:
createdb((CreatedbStmt *) parsetree);
+ 			break;
+ 
+ 		case T_AlterDatabaseStmt:
+ 			AlterDatabase((AlterDatabaseStmt *) parsetree);
break;

case T_AlterDatabaseSetStmt:
diff -Nacr my-cvs/src/backend/utils/init/miscinit.c my-aproach2/src/backend/utils/init/miscinit.c
*** my-cvs/src/backend/utils/init/miscinit.c	Mon Jun 20 04:17:30 2005
--- my-aproach2/src/backend/utils/init/miscinit.c	Tue Jun 28 06:41:40 2005
***************
*** 315,320 ****
--- 315,321 ----
Datum		datum;
bool		isnull;
AclId		usesysid;
+ 	Form_pg_shadow	userform;

/*
* Don't do scans if we're bootstrapping, none of the system catalogs
***************
*** 333,344 ****
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("user \"%s\" does not exist", username)));

! usesysid = ((Form_pg_shadow) GETSTRUCT(userTup))->usesysid;

AuthenticatedUserId = usesysid;
! AuthenticatedUserIsSuperuser = ((Form_pg_shadow) GETSTRUCT(userTup))->usesuper;

SetSessionUserId(usesysid); /* sets CurrentUserId too */

/* Record username and superuser status as GUC settings too */
SetConfigOption("session_authorization", username,
--- 334,358 ----
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("user \"%s\" does not exist", username)));

! userform = ((Form_pg_shadow) GETSTRUCT(userTup));
! usesysid = userform->usesysid;

AuthenticatedUserId = usesysid;
! AuthenticatedUserIsSuperuser = userform->usesuper;

SetSessionUserId(usesysid); /* sets CurrentUserId too */
+ 
+ 	/*
+ 	 * Check connection limit for user
+ 	 */
+ 	if (userform->usemaxconn > 0 && !AuthenticatedUserIsSuperuser &&
+ 			CountUserBackends(AuthenticatedUserId) > userform->usemaxconn)
+ 	{
+ 		ereport(FATAL,
+ 			(errcode(ERRCODE_TOO_MANY_CONNECTIONS),
+ 		 errmsg("sorry, too many clients already for user \"%s\"",
+ 			username)));
+ 	}

/* Record username and superuser status as GUC settings too */
SetConfigOption("session_authorization", username,
diff -Nacr my-cvs/src/backend/utils/init/postinit.c my-aproach2/src/backend/utils/init/postinit.c
*** my-cvs/src/backend/utils/init/postinit.c	Fri Jun 24 03:06:26 2005
--- my-aproach2/src/backend/utils/init/postinit.c	Tue Jun 28 10:11:10 2005
***************
*** 50,55 ****
--- 50,56 ----
static void InitCommunication(void);
static void ShutdownPostgres(int code, Datum arg);
static bool ThereIsAtLeastOneUser(void);
+ static bool FindMyUser(const char *name, AclId *user_id);

/*** InitPostgres support ***/
***************
*** 100,105 ****
--- 101,137 ----
}

/*
+  * Get user id from flatfiles 
+  * 
+  * We need this because we need to know userid before 
+  * InitProcess() is called
+  */
+ static bool
+ FindMyUser(const char *name, AclId *user_id)
+ {
+ 	List	  **line; 
+ 	ListCell   *token; 
+ 	char		thisname[NAMEDATALEN];
+ 
+ 	if ((line = get_user_line(name)) == NULL)
+ 		ereport(FATAL,
+ 				(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION, /* ? */
+ 				 errmsg("could not find user \"%s\": %m", name)));
+ 
+ 	/* Skip over username */
+ 	token = list_head(*line);
+ 	if (token)
+ 		token = lnext(token);
+ 	if (token)
+ 	{
+ 		*user_id = atoi((char*)lfirst(token));
+ 		return true;
+ 	}
+ 
+ 	return false;
+ }
+ 
+ /*
* ReverifyMyDatabase -- recheck info obtained by FindMyDatabase
*
* Since FindMyDatabase cannot lock pg_database, the information it read
***************
*** 165,181 ****
name, MyDatabaseId)));
}

- /*
- * Also check that the database is currently allowing connections.
- * (We do not enforce this in standalone mode, however, so that there is
- * a way to recover from "UPDATE pg_database SET datallowconn = false;")
- */
dbform = (Form_pg_database) GETSTRUCT(tup);
! if (IsUnderPostmaster && !dbform->datallowconn)
! ereport(FATAL,
! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
! errmsg("database \"%s\" is not currently accepting connections",
name)));

/*
* OK, we're golden.  Next to-do item is to save the encoding
--- 197,231 ----
name, MyDatabaseId)));
}

dbform = (Form_pg_database) GETSTRUCT(tup);
! if (IsUnderPostmaster)
! {
! /*
! * Also check that the database is currently allowing connections.
! * (We do not enforce this in standalone mode, however, so that there is
! * a way to recover from "UPDATE pg_database SET datallowconn = false;")
! */
! if (!dbform->datallowconn)
! {
! ereport(FATAL,
! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
! errmsg("database \"%s\" is not currently accepting connections",
! name)));
! }
!
! /*
! * Here we check cxonenction limit for this database
! */
! if (dbform->datmaxconn > 0 && !superuser() &&
! CountDBBackends(MyDatabaseId) > dbform->datmaxconn)
! {
! ereport(FATAL,
! (errcode(ERRCODE_TOO_MANY_CONNECTIONS),
! errmsg("sorry, too many clients already for database \"%s\"",
name)));
+ }
+ }
+

/*
* OK, we're golden.  Next to-do item is to save the encoding
***************
*** 350,355 ****
--- 400,424 ----
* Code after this point assumes we are in the proper directory!
*/

+ 	/*
+ 	 * We need to know userid in InitProcess() so we have read it from
+ 	 * flatfile, real user inicialization is done later
+ 	 */
+ 	if (IsUnderPostmaster)
+ 	{
+ 		AclId userid;
+ 
+ 		if (!FindMyUser(username, &userid))
+ 			ereport(FATAL,
+ 					(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION, /* ? */
+ 					 errmsg("user \"%s\" does not exist",
+ 							username)));
+ 
+ 		SetSessionUserId(userid);
+ 	}
+ 	else
+ 		SetSessionUserId(BOOTSTRAP_USESYSID);
+ 	
/*
* Set up my per-backend PGPROC struct in shared memory.	(We need
* to know MyDatabaseId before we can do this, since it's entered into
diff -Nacr my-cvs/src/include/catalog/pg_database.h my-aproach2/src/include/catalog/pg_database.h
*** my-cvs/src/include/catalog/pg_database.h	Thu Apr 14 03:38:20 2005
--- my-aproach2/src/include/catalog/pg_database.h	Tue Jun 28 06:07:50 2005
***************
*** 40,45 ****
--- 40,46 ----
int4		encoding;		/* character encoding */
bool		datistemplate;	/* allowed as CREATE DATABASE template? */
bool		datallowconn;	/* new connections allowed? */
+ 	int4		datmaxconn;		/* maximum connections allowed */
Oid			datlastsysoid;	/* highest OID to consider a system OID */
TransactionId datvacuumxid; /* all XIDs before this are vacuumed */
TransactionId datfrozenxid; /* all XIDs before this are frozen */
***************
*** 59,78 ****
*		compiler constants for pg_database
* ----------------
*/
! #define Natts_pg_database				11
#define Anum_pg_database_datname		1
#define Anum_pg_database_datdba			2
#define Anum_pg_database_encoding		3
#define Anum_pg_database_datistemplate	4
#define Anum_pg_database_datallowconn	5
! #define Anum_pg_database_datlastsysoid	6
! #define Anum_pg_database_datvacuumxid	7
! #define Anum_pg_database_datfrozenxid	8
! #define Anum_pg_database_dattablespace	9
! #define Anum_pg_database_datconfig		10
! #define Anum_pg_database_datacl			11

! DATA(insert OID = 1 ( template1 PGUID ENCODING t t 0 0 0 1663 _null_ _null_ ));
DESCR("Default template database");
#define TemplateDbOid 1

--- 60,80 ----
*		compiler constants for pg_database
* ----------------
*/
! #define Natts_pg_database				12
#define Anum_pg_database_datname		1
#define Anum_pg_database_datdba			2
#define Anum_pg_database_encoding		3
#define Anum_pg_database_datistemplate	4
#define Anum_pg_database_datallowconn	5
! #define Anum_pg_database_datmaxconn		6
! #define Anum_pg_database_datlastsysoid	7
! #define Anum_pg_database_datvacuumxid	8
! #define Anum_pg_database_datfrozenxid	9
! #define Anum_pg_database_dattablespace	10
! #define Anum_pg_database_datconfig		11
! #define Anum_pg_database_datacl			12

! DATA(insert OID = 1 ( template1 PGUID ENCODING t t 0 0 0 0 1663 _null_ _null_ ));
DESCR("Default template database");
#define TemplateDbOid 1

diff -Nacr my-cvs/src/include/catalog/pg_shadow.h my-aproach2/src/include/catalog/pg_shadow.h
*** my-cvs/src/include/catalog/pg_shadow.h	Thu Apr 14 03:38:22 2005
--- my-aproach2/src/include/catalog/pg_shadow.h	Tue Jun 28 06:07:50 2005
***************
*** 36,41 ****
--- 36,42 ----
bool		usecreatedb;
bool		usesuper;		/* read this field via superuser() only */
bool		usecatupd;
+ 	int4		usemaxconn;		/* maximum connections allowed */

/* remaining fields may be null; use heap_getattr to read them! */
text passwd;
***************
*** 54,68 ****
* compiler constants for pg_shadow
* ----------------
*/
! #define Natts_pg_shadow 8
#define Anum_pg_shadow_usename 1
#define Anum_pg_shadow_usesysid 2
#define Anum_pg_shadow_usecreatedb 3
#define Anum_pg_shadow_usesuper 4
#define Anum_pg_shadow_usecatupd 5
! #define Anum_pg_shadow_passwd 6
! #define Anum_pg_shadow_valuntil 7
! #define Anum_pg_shadow_useconfig 8

/* ----------------
*		initial contents of pg_shadow
--- 55,70 ----
*		compiler constants for pg_shadow
* ----------------
*/
! #define Natts_pg_shadow					9
#define Anum_pg_shadow_usename			1
#define Anum_pg_shadow_usesysid			2
#define Anum_pg_shadow_usecreatedb		3
#define Anum_pg_shadow_usesuper			4
#define Anum_pg_shadow_usecatupd		5
! #define Anum_pg_shadow_usemaxconn		6
! #define Anum_pg_shadow_passwd			7
! #define Anum_pg_shadow_valuntil			8
! #define Anum_pg_shadow_useconfig		9

/* ----------------
* initial contents of pg_shadow
***************
*** 71,77 ****
* user choices.
* ----------------
*/
! DATA(insert ( "POSTGRES" PGUID t t t _null_ _null_ _null_ ));

#define BOOTSTRAP_USESYSID 1

--- 73,79 ----
* user choices.
* ----------------
*/
! DATA(insert ( "POSTGRES" PGUID t t t 0 _null_ _null_ _null_ ));

#define BOOTSTRAP_USESYSID 1

diff -Nacr my-cvs/src/include/commands/dbcommands.h my-aproach2/src/include/commands/dbcommands.h
*** my-cvs/src/include/commands/dbcommands.h	Mon Jun 06 19:01:26 2005
--- my-aproach2/src/include/commands/dbcommands.h	Tue Jun 28 06:07:50 2005
***************
*** 64,69 ****
--- 64,70 ----
extern void createdb(const CreatedbStmt *stmt);
extern void dropdb(const char *dbname);
extern void RenameDatabase(const char *oldname, const char *newname);
+ extern void AlterDatabase(AlterDatabaseStmt *stmt);
extern void AlterDatabaseSet(AlterDatabaseSetStmt *stmt);
extern void AlterDatabaseOwner(const char *dbname, AclId newOwnerSysId);

diff -Nacr my-cvs/src/include/nodes/nodes.h my-aproach2/src/include/nodes/nodes.h
*** my-cvs/src/include/nodes/nodes.h	Mon Jun 27 00:05:42 2005
--- my-aproach2/src/include/nodes/nodes.h	Tue Jun 28 06:07:50 2005
***************
*** 272,277 ****
--- 272,278 ----
T_ReindexStmt,
T_CheckPointStmt,
T_CreateSchemaStmt,
+ 	T_AlterDatabaseStmt,
T_AlterDatabaseSetStmt,
T_AlterUserSetStmt,
T_CreateConversionStmt,
diff -Nacr my-cvs/src/include/nodes/parsenodes.h my-aproach2/src/include/nodes/parsenodes.h
*** my-cvs/src/include/nodes/parsenodes.h	Wed Jun 22 23:14:32 2005
--- my-aproach2/src/include/nodes/parsenodes.h	Tue Jun 28 06:07:50 2005
***************
*** 1620,1625 ****
--- 1620,1632 ----
*	Alter Database
* ----------------------
*/
+ typedef struct AlterDatabaseStmt
+ {
+ 	NodeTag		type;
+ 	char	   *dbname;			/* name of database to create */
+ 	List	   *options;		/* List of DefElem nodes */
+ } AlterDatabaseStmt;
+ 
typedef struct AlterDatabaseSetStmt
{
NodeTag		type;
diff -Nacr my-cvs/src/include/storage/proc.h my-aproach2/src/include/storage/proc.h
*** my-cvs/src/include/storage/proc.h	Sat Jun 18 00:32:50 2005
--- my-aproach2/src/include/storage/proc.h	Tue Jun 28 06:07:50 2005
***************
*** 71,76 ****
--- 71,77 ----

int pid; /* This backend's process id, or 0 */
Oid databaseId; /* OID of database this backend is using */
+ AclId userId; /* user connected to this backend */

/* Info about LWLock the process is currently waiting for, if any. */
bool		lwWaiting;		/* true if waiting for an LW lock */
diff -Nacr my-cvs/src/include/storage/procarray.h my-aproach2/src/include/storage/procarray.h
*** my-cvs/src/include/storage/procarray.h	Sat Jun 18 00:32:50 2005
--- my-aproach2/src/include/storage/procarray.h	Tue Jun 28 06:07:50 2005
***************
*** 31,36 ****
--- 31,38 ----
extern bool DatabaseHasActiveBackends(Oid databaseId, bool ignoreMyself);

extern int	CountActiveBackends(void);
+ extern int	CountDBBackends(Oid databaseid);
+ extern int	CountUserBackends(AclId userid);

extern void XidCacheRemoveRunningXids(TransactionId xid,
int nxids, TransactionId *xids);
diff -Nacr my-cvs/src/tools/pgindent/pgindent my-aproach2/src/tools/pgindent/pgindent
*** my-cvs/src/tools/pgindent/pgindent	Thu Oct 07 16:15:50 2004
--- my-aproach2/src/tools/pgindent/pgindent	Tue Jun 28 06:07:50 2005
***************
*** 175,180 ****
--- 175,181 ----
-TAllocSetContext \
-TAllocateDesc \
-TAllocateDescKind \
+ -TAlterDatabaseStmt \
-TAlterDatabaseSetStmt \
-TAlterDomainStmt \
-TAlterGroupStmt \

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

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

Alvaro Herrera

alvherre@surnet.cl

about 21 years ago

In reply to: Bruce Momjian (#2)

Re: per user/database connections limit again

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)

Bruce Momjian

bruce@momjian.us

about 21 years ago

In reply to: Alvaro Herrera (#3)

Re: per user/database connections limit again

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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Stephen Frost

sfrost@snowman.net

about 21 years ago

In reply to: Petr Jelinek (#1)

Re: per user/database connections limit again

* Petr Jelinek (pjmodos@parba.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

Petr Jelinek

petr@2ndquadrant.com

about 21 years ago

In reply to: Stephen Frost (#5)

Re: per user/database connections limit again

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)

Alvaro Herrera

alvherre@surnet.cl

about 21 years ago

In reply to: Petr Jelinek (#6)

Re: per user/database connections limit again

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)

Petr Jelinek

petr@2ndquadrant.com

about 21 years ago

In reply to: Alvaro Herrera (#7)

Re: per user/database connections limit again

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)

Bruce Momjian

bruce@momjian.us

about 21 years ago

In reply to: Petr Jelinek (#6)

Re: per user/database connections limit again

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)));
}

/*
* OK, we're golden.  Next to-do item is to save the encoding
--- 196,230 ----
name, MyDatabaseId)));
}

/*
* 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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

#10

Petr Jelinek

petr@2ndquadrant.com

about 21 years ago

In reply to: Bruce Momjian (#9)

Re: per user/database connections limit again

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)

#11

Bruce Momjian

bruce@momjian.us

about 21 years ago

In reply to: Petr Jelinek (#6)

Re: per user/database connections limit again

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

--- 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)));
}

/*
* OK, we're golden.  Next to-do item is to save the encoding
--- 196,230 ----
name, MyDatabaseId)));
}

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

/* ----------------
*		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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

#12

Tom Lane

tgl@sss.pgh.pa.us

about 21 years ago

In reply to: Bruce Momjian (#11)

Re: per user/database connections limit again

Bruce Momjian <pgman@candle.pha.pa.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

#13

Petr Jelinek

petr@2ndquadrant.com

about 21 years ago

In reply to: Bruce Momjian (#11)

Re: per user/database connections limit again

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)

#14

Bruce Momjian

bruce@momjian.us

about 21 years ago

In reply to: Petr Jelinek (#13)

Re: per user/database connections limit again

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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

#15

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Petr Jelinek (#6)

Re: per user/database connections limit again

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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

#16

Petr Jelinek

petr@2ndquadrant.com

almost 21 years ago

In reply to: Bruce Momjian (#15)

Re: per user/database connections limit again

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)

#17

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Petr Jelinek (#16)

Re: per user/database connections limit again

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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

#18

Petr Jelinek

petr@2ndquadrant.com

almost 21 years ago

In reply to: Bruce Momjian (#17)

Re: per user/database connections limit again

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

--
Regards
Petr Jelinek (PJMODOS)

#19

Tom Lane

tgl@sss.pgh.pa.us

almost 21 years ago

In reply to: Bruce Momjian (#15)

Re: per user/database connections limit again

Bruce Momjian <pgman@candle.pha.pa.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

#20

Peter Eisentraut

peter_e@gmx.net

almost 21 years ago

In reply to: Tom Lane (#12)

Re: per user/database connections limit again

Am Montag, 25. Juli 2005 18:31 schrieb Tom Lane:

Bruce Momjian <pgman@candle.pha.pa.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/

#21

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Peter Eisentraut (#20)

#22

Bruce Momjian

bruce@momjian.us

almost 21 years ago

In reply to: Bruce Momjian (#21)