[Users] postgresqldb.sh mistakes

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@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(a)digiportmiami.com +++