[Serusers] PostgreSQL
Greg Fausak
greg at august.net
Sat Jul 26 17:48:45 CEST 2003
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
>
More information about the sr-users
mailing list