[Serusers] PostgreSQL

[Greg Fausak]

That looks right to me.
Did you start postgres with the tcp flag? (I think -i)?
Normally I believe it uses domain sockets, you have
To enable tcp/ip.

To be sure try psql:

Psql -h hostname -p 5432 -U user database

What do the log files say?

-g


> -----Original Message-----
> From: serusers-bounces at lists.iptel.org 
> [mailto:serusers-bounces at lists.iptel.org] On Behalf Of Takefumi Naito
> Sent: Saturday, July 26, 2003 2:42 AM
> To: serusers at lists.iptel.org
> Subject: [Serusers] PostgreSQL
> 
> 
> 
> Thank you for your reply.
> 
> I tried, but location database hasn't run yet.
> 
> The machine I use are installed SER and PostgreSQL.
> 
> First, what I did is created a database "ser" 
> and created table "location", "subscriber" and "grp"
> according to the way you teached me.
> 
> Then I did following procedure.
> 
>  $psql ser
>  
>  postgres=# create user seruser with password 'seruserpass';
>  postgres=# Grant all on table location to seruser;
>  postgres=# Grant all on table subscriber to seruser;
>  postgres=# Grant all on table grp to seruser;
> 
> In the ser.cfg file, I set db_url 
> "sql://seruser:seruserpass@localhost:5432/ser" 
>  for three modules: userloc, group and auth_db.
> 
> Is that right?
> Could you point my mistake out?
> 
> Best regards.
> Takefumi
> 
> 
> > Hi,
> > 
> > Wow, I didn't think anybody was going to ever use it!
> > 
> > I've been running, in production, for several months.
> > The driver seems to work.
> > 
> > First, it goes without saying, but you have to get Postgres
> > installed on your machine, and perhaps two machines.
> > If you are running postgres on the same machine that is doing
> > the SER process, then one machine will do, otherwise, install
> > Postgres on the machine that runs the database, and on the machine
> > that compiles (and runs) SER.
> > 
> > Second, start the database and create a couple of tables:
> > 
> > CREATE TABLE subscriber (
> >     phplib_id character varying(32) DEFAULT '' NOT NULL,
> >     user_id character varying(100) DEFAULT '' NOT NULL,
> >     "password" character varying(25) DEFAULT '' NOT NULL,
> >     first_name character varying(25) DEFAULT '' NOT NULL,
> >     last_name character varying(45) DEFAULT '' NOT NULL,
> >     phone character varying(15) DEFAULT '' NOT NULL,
> >     email_address character varying(50) DEFAULT '' NOT NULL,
> >     datetime_created timestamp with time zone NOT NULL,
> >     datetime_modified timestamp with time zone NOT NULL,
> >     confirmation character varying(64) DEFAULT '' NOT NULL,
> >     flag character(1) DEFAULT 'o' NOT NULL,
> >     sendnotification character varying(50) DEFAULT '' NOT NULL,
> >     greeting character varying(50) DEFAULT '' NOT NULL,
> >     ha1 character varying(128) DEFAULT '' NOT NULL,
> >     "domain" character varying(128) DEFAULT '' NOT NULL,
> >     ha1b character varying(128) DEFAULT '' NOT NULL,
> >     perms character varying(32),
> >     allow_find character(1) DEFAULT '0' NOT NULL,
> >     timezone character varying(128),
> >     first_reg timestamp with time zone,
> >     last_reg timestamp with time zone
> > );
> > 
> > CREATE UNIQUE INDEX subscriber_primary ON subscriber USING btree
> > ("domain", user_id);
> > 
> > CREATE TABLE "location" (
> >     user_id character varying(50) DEFAULT '' NOT NULL,
> >     "domain" character varying(100) DEFAULT '' NOT NULL,
> >     contact character varying(255) DEFAULT '' NOT NULL,
> >     expires timestamp with time zone,
> >     q numeric(10,2),
> >     callid character varying(255),
> >     cseq numeric(11,0),
> >     last_modified timestamp with time zone DEFAULT now() NOT NULL,
> >     replicate numeric(10,0),
> >     state numeric(3,0)
> > );
> > 
> > CREATE INDEX location_primary ON "location" USING btree (user_id);
> > 
> > CREATE TABLE grp (
> >     user_id character varying(50) NOT NULL,
> >     grp character varying(50) NOT NULL,
> >     last_modified timestamp with time zone DEFAULT now() NOT NULL,
> >     "domain" character varying(128)
> > );
> > 
> > CREATE UNIQUE INDEX grp_primary ON grp USING btree (user_id, grp);
> > 
> > Create a user in the database, for instance:
> > 
> > Create user seruser with password 'seruserpass';
> > 
> > Grant permission on the tables to the newly created user:
> > 
> > Grant all on table location to seruser;
> > Grant all on table subscriber to seruser;
> > Grant all on table grp to seruser;
> > 
> > Ok, this is the basic database.
> > 
> > You need this line in the ser.cfg file:
> > 
> > loadmodule "/usr/ser/lib/ser/modules/postgres.so"
> > 
> > modparam("usrloc","db_mode",1)
> > modparam("usrloc","user_column","user_id")
> > modparam("usrloc","db_url",
> >         "sql://seruser:seruserpass@db.host.name.or.ip:5432/dbname")
> > 
> > modparam("group","user_column","user_id")
> > modparam("group","db_url",
> >         "sql://seruser:seruserpass@db.host.name.or.ip:5432/dbname")
> > 
> > modparam("auth_db","user_column","user_id")
> > modparam("auth_db","db_url",
> >         "sql://seruser:seruserpass@db.host.name.or.ip:5432/dbname")
> > 
> > By the way, I had problems changing the column names.   
> I've got many
> > other
> > tables and applications that refer to the column names as 
> they were in
> > 0.8.10.  Since the column name change didn't really affect 
> anything but
> > the
> > name, I left them the way they were.  Changing them would be
> > too much work for me! 
> > 
> > That is it.  Oh, there is one other thing.
> > Your serctl program won't work.  Use the serctl call serpgctl and
> > rename it serctl if you wish.
> > 
> > Good luck,
> > ---greg
> > Greg Fausak
> > Addaline.com, Inc.
> > greg at addaline.com
> > www.addaline.com
> > 
> 
> _______________________________________________
> Serusers mailing list
> serusers at lists.iptel.org
> http://lists.iptel.org/mailman/listinfo/serusers
>