Re: per user/database connections limit again - Mailing list pgsql-patches

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