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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
