[Users] postgresqldb.sh mistakes

[Pablo Delgado]

Hi Dev Group,

I just upgraded to openser1.1-tls with a postgresql db server. During this I found that the script for postgresql 
still did not work well. It gave me a few errors and it was not granting permissions correctly, so I dug in and checked
and it seems things were out of order or created as if it were mysql. So I edit it so that every thing works 
(or at least what I could find. ;-) )

I am not sure what flags you use for your patches I used the following command to produce this patch:
diff -Naur postgresqldb.sh.old postgresqldb.sh.new > posgresqldb.sh.patch

If someone had already fixed these then, sorry for the email.


---------------start-----------------------------------
--- postgresqldb.sh.orig        2006-07-24 16:57:57.000000000 -0400
+++ postgresqldb.sh     2006-07-24 17:01:07.000000000 -0400
@@ -53,7 +53,7 @@
 fi
 # address of MySQL server
 if [ -z "$DBHOST" ]; then
-       DBHOST="204.13.2.20"
+       DBHOST="localhost"
 fi
 # user with full privileges over DBNAME database
 if [ -z "$DBRWUSER" ]; then
@@ -266,13 +266,8 @@
                'root at localhost', '2002-09-04 19:37:45', '$DUMMY_DATE',
                '57DaSIPuCm52UNe54LF545750cfdL48OMZfroM53', 'o', '', '',
                '$HA1', '$SIP_DOMAIN', '$HA1B',
-               '$PHPLIB_ID' );
+               '$PHPLIB_ID' );"

-               INSERT INTO admin_privileges ($USERCOL, domain, priv_name, priv_value)
-               VALUES ('admin', '$SIP_DOMAIN', 'is_admin', '1');
-
-               INSERT INTO admin_privileges ($USERCOL, domain, priv_name, priv_value)
-               VALUES ('admin', '$SIP_DOMAIN', 'change_privileges', '1');"
 elif [ $# -eq 2 ] ; then
        # if 3rd param set, don't create any initial user
        INITIAL_USER=""
@@ -665,7 +660,15 @@
 CREATE INDEX fromip_idx ON sip_trace (fromip);
 CREATE INDEX callid_idx ON sip_trace (callid);

-
+/*
+ * Table for prefix_domain module
+ * Note: Missing (pablo)
+ */
+CREATE TABLE prefix_domain (
+  prefix VARCHAR(32) NOT NULL PRIMARY KEY,
+  domain VARCHAR(255) NOT NULL DEFAULT ''
+);
+
 /* add an admin user "admin" with password==$DEFAULT_PW,
  * so that one can try it out on quick start
  */
@@ -675,8 +678,16 @@
 /*
  * GRANT permissions
  */
-
-$GRANT_CMD
+CREATE USER $DBRWUSER WITH PASSWORD '$DEFAULT_PW';
+CREATE USER $DBROUSER WITH PASSWORD '$RO_PW';
+GRANT ALL PRIVILEGES ON TABLE version, acc, aliases, dbaliases, grp,
+                location, missed_calls, subscriber, silo, silo_mid_seq, domain,
+                uri, usr_preferences, trusted,
+                speed_dial, gw, gw_grp, gw_grp_grp_id_seq, lcr, sip_trace TO $DBRWUSER;
+GRANT SELECT ON TABLE version, acc, aliases, dbaliases, grp,
+                location, missed_calls, subscriber, silo, silo_mid_seq, domain,
+                uri, usr_preferences, trusted,
+                speed_dial, gw, gw_grp, gw_grp_grp_id_seq, lcr, sip_trace TO $DBROUSER;

 EOF

@@ -692,23 +703,6 @@

 serweb_create () # pars: <database name>
 {
-#if [ $# -eq 1 ] ; then
-#      if [ -z "$SIP_DOMAIN" ] ; then
-#              prompt_realm
-#      fi
-#      INITIAL_INSERT="
-#              INSERT INTO admin_privileges ($USERCOL, domain, priv_name, priv_value)
-#              VALUES ('admin', '$SIP_DOMAIN', 'is_admin', '1');
-#              INSERT INTO admin_privileges ($USERCOL, domain, priv_name, priv_value)
-#              VALUES ('admin', '$SIP_DOMAIN', 'change_privileges', '1');"
-#elif [ $# -eq 2 ] ; then
-#      # if 3rd param set, don't do any initial insert
-#      INITIAL_INSERT=""
-#else
-#      echo "serweb_create function takes one or two params"
-#      exit 1
-#fi
-
 echo "creating serweb tables into $1 ..."

 sql_query <<EOF
@@ -843,6 +837,19 @@
   PRIMARY KEY  ($USERCOL,priv_name,priv_value,domain)
 ) $TABLE_TYPE;

+/*
+ * Table for active sessions - webstuff
+ * Note: was missing (pablo)
+ */
+CREATE TABLE active_sessions (
+  sid varchar(32) NOT NULL default '',
+  name varchar(32) NOT NULL default '',
+  val text,
+  changed varchar(14) NOT NULL default '',
+  PRIMARY KEY  (name,sid)
+) $TABLE_TYPE;
+create index chaned on active_sessions (changed);
+

 /*
  * emulate mysql proprietary functions used by the serweb
@@ -851,6 +858,12 @@
 CREATE FUNCTION "truncate" (numeric,int) RETURNS numeric AS 'SELECT trunc(\$1,\$2);' LANGUAGE 'sql';
 create function unix_timestamp(timestamp) returns integer as 'select date_part(''epoch'', \$1)::int4 as result' language 'sql';

+/*
+ * grant permission
+ */
+GRANT ALL PRIVILEGES ON TABLE usr_preferences_types, pending, phonebook, server_monitoring, server_monitoring_agg, admin_privileges, active_sessions TO $DBR
WUSER;
+GRANT SELECT ON TABLE usr_preferences_types, pending, phonebook, server_monitoring, server_monitoring_agg, admin_privileges, active_sessions to $DBRWUSER;
+
 $INITIAL_INSERT
 EOF
---------------------end--------------------------------------------



I full

Pablo Delgado
Digiport Miami, LLC 
200 SE 1st Street #604 
Miami, FL 33131 
p.305-424-0016
f. 305-503-5408 
e. pablo at digiportmiami.com  

+++ 
 
>From it's Data Center in Miami, Florida, Digiport provides colocation, managed servers, secure hosting and business services including VoIP PBX, Corporate Email and Sales Force Management Systems. With 24/7 manned security and IT support, over 15 carrier connections, primary and backup generation systems, and hardened telecommunications infrastructure, Digiport provides vital telecommunications continuity for your business.