[Serusers] PostgreSQL

[Takefumi Naito]

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
>