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)
Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.16
diff -c -r1.16 system_views.sql
*** src/backend/catalog/system_views.sql 28 Jun 2005 05:08:52 -0000
1.16
--- src/backend/catalog/system_views.sql 24 Jul 2005 12:22:08 -0000
***************
*** 14,19 ****
--- 14,20 ----
rolcreatedb,
rolcatupdate,
rolcanlogin,
+ rolmaxconn,
'********'::text as rolpassword,
rolvaliduntil,
rolconfig
***************
*** 26,31 ****
--- 27,33 ----
rolcreatedb AS usecreatedb,
rolsuper AS usesuper,
rolcatupdate AS usecatupd,
+ rolmaxconn AS usemaxconn,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
rolconfig AS useconfig
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.64
diff -c -r1.64 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 18 Jul 2005 19:12:09 -0000 1.64
--- src/bin/pg_dump/pg_dumpall.c 24 Jul 2005 12:22:35 -0000
***************
*** 394,409 ****
PGresult *res;
int i;
! if (server_version >= 70100)
res = executeQuery(conn,
"SELECT usename, usesysid,
passwd, usecreatedb, "
! "usesuper, valuntil, "
"(usesysid = (SELECT datdba
FROM pg_database WHERE datname = 'template0')) AS clusterowner "
"FROM pg_shadow");
else
res = executeQuery(conn,
"SELECT usename, usesysid,
passwd, usecreatedb, "
! "usesuper, valuntil, "
"(usesysid = (SELECT datdba
FROM pg_database WHERE datname = 'template1')) AS clusterowner "
"FROM pg_shadow");
--- 394,415 ----
PGresult *res;
int i;
! if (server_version >= 80100)
! res = executeQuery(conn,
! "SELECT usename, usesysid,
passwd, usecreatedb, "
! "usesuper, valuntil,
usemaxconn, "
! "(usesysid = (SELECT datdba
FROM pg_database WHERE datname = 'template0')) AS clusterowner "
! "FROM pg_shadow");
! else if (server_version >= 70100)
res = executeQuery(conn,
"SELECT usename, usesysid,
passwd, usecreatedb, "
! "usesuper, valuntil, '0' AS
usemaxconn, "
"(usesysid = (SELECT datdba
FROM pg_database WHERE datname = 'template0')) AS clusterowner "
"FROM pg_shadow");
else
res = executeQuery(conn,
"SELECT usename, usesysid,
passwd, usecreatedb, "
! "usesuper, valuntil, '0' AS
usemaxconn, "
"(usesysid = (SELECT datdba
FROM pg_database WHERE datname = 'template1')) AS clusterowner "
"FROM pg_shadow");
***************
*** 453,458 ****
--- 459,468 ----
appendPQExpBuffer(buf, " VALID UNTIL '%s'",
PQgetvalue(res, i,
5));
+ if (strcmp(PQgetvalue(res, i, 6), "0") != 0)
+ appendPQExpBuffer(buf, " MAX CONNECTIONS '%s'",
+ PQgetvalue(res, i,
6));
+
appendPQExpBuffer(buf, ";\n");
printf("%s", buf->data);
***************
*** 612,623 ****
printf("--\n-- Database creation\n--\n\n");
! if (server_version >= 80000)
res = executeQuery(conn,
"SELECT datname, "
"coalesce(usename, (select
usename from pg_shadow where usesysid=(select datdba from pg_database where
datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "datistemplate, datacl, "
"(SELECT spcname FROM
pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba =
usesysid) "
"WHERE datallowconn ORDER BY
1");
--- 622,642 ----
printf("--\n-- Database creation\n--\n\n");
! if (server_version >= 80100)
res = executeQuery(conn,
"SELECT datname, "
"coalesce(usename, (select
usename from pg_shadow where usesysid=(select datdba from pg_database where
datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "datistemplate, datacl,
datmaxconn, "
! "(SELECT spcname FROM
pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
! "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba =
usesysid) "
! "WHERE datallowconn ORDER BY
1");
! else if (server_version >= 80000)
! res = executeQuery(conn,
! "SELECT datname, "
! "coalesce(usename, (select
usename from pg_shadow where usesysid=(select datdba from pg_database where
datname='template0'))), "
!
"pg_encoding_to_char(d.encoding), "
! "datistemplate, datacl, '0'
as datmaxconn, "
"(SELECT spcname FROM
pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba =
usesysid) "
"WHERE datallowconn ORDER BY
1");
***************
*** 626,632 ****
"SELECT datname, "
"coalesce(usename, (select
usename from pg_shadow where usesysid=(select datdba from pg_database where
datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "datistemplate, datacl, "
"'pg_default' AS
dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba =
usesysid) "
"WHERE datallowconn ORDER BY
1");
--- 645,651 ----
"SELECT datname, "
"coalesce(usename, (select
usename from pg_shadow where usesysid=(select datdba from pg_database where
datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "datistemplate, datacl, '0'
as datmaxconn, "
"'pg_default' AS
dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba =
usesysid) "
"WHERE datallowconn ORDER BY
1");
***************
*** 637,643 ****
"(select usename from pg_shadow where
usesysid=datdba), "
"(select usename from
pg_shadow where usesysid=(select datdba from pg_database where
datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "datistemplate, '' as
datacl, "
"'pg_default' AS
dattablespace "
"FROM pg_database d "
"WHERE datallowconn ORDER BY
1");
--- 656,662 ----
"(select usename from pg_shadow where
usesysid=datdba), "
"(select usename from
pg_shadow where usesysid=(select datdba from pg_database where
datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "datistemplate, '' as
datacl, '0' as datmaxconn, "
"'pg_default' AS
dattablespace "
"FROM pg_database d "
"WHERE datallowconn ORDER BY
1");
***************
*** 652,658 ****
"(select usename from pg_shadow where
usesysid=datdba), "
"pg_encoding_to_char(d.encoding), "
"'f' as datistemplate, "
! "'' as datacl, "
"'pg_default' AS
dattablespace "
"FROM pg_database d "
"ORDER BY 1");
--- 671,677 ----
"(select usename from pg_shadow where
usesysid=datdba), "
"pg_encoding_to_char(d.encoding), "
"'f' as datistemplate, "
! "'' as datacl, '0' as
datmaxconn, "
"'pg_default' AS
dattablespace "
"FROM pg_database d "
"ORDER BY 1");
***************
*** 666,672 ****
char *dbencoding = PQgetvalue(res, i, 2);
char *dbistemplate = PQgetvalue(res, i, 3);
char *dbacl = PQgetvalue(res, i, 4);
! char *dbtablespace = PQgetvalue(res, i, 5);
char *fdbname;
buf = createPQExpBuffer();
--- 685,692 ----
char *dbencoding = PQgetvalue(res, i, 2);
char *dbistemplate = PQgetvalue(res, i, 3);
char *dbacl = PQgetvalue(res, i, 4);
! char *dbmaxconn = PQgetvalue(res, i, 5);
! char *dbtablespace = PQgetvalue(res, i, 6);
char *fdbname;
buf = createPQExpBuffer();
***************
*** 698,703 ****
--- 718,727 ----
appendPQExpBuffer(buf, " TABLESPACE = %s",
fmtId(dbtablespace));
+ if (strcmp(dbmaxconn, "0") != 0)
+ appendPQExpBuffer(buf, " MAX CONNECTIONS = %s",
+
fmtId(dbmaxconn));
+
appendPQExpBuffer(buf, ";\n");
if (strcmp(dbistemplate, "t") == 0)
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings