[Zope] Getting Postgres and exUserFolder to work with Zope

[Heimo Laukkanen]

Hi,

I spent a couple of hours trying to get Zope to work with postgres.
There simply was not one good guide or howto available, all the rigth
info was avaialble -- but on different mailinglists. Here is what I did,
hope it helps someone else -- or perhaps someone will get a spark from
this and write a decent howto. 

Atleast after this mail people will find the info from one place.

Using: 
Zope Zope 2.3.2
Python 1.52
ExUserFolder 0-8-0
psycopg-0.99.6
postgresql-7.1.3
egenix-mx-base-2.0.2


1) Getting all the pieces together

Postgresql - http://www.postgresql.org/

ExUserFolder - http://exuserfolder.sourceforge.net/

psycopg (Postgres database adapter ) -
http://www.zope.org/Members/fog/psycopg

egenix mx Extensions -
http://www.lemburg.com/files/python/eGenix-mx-Extensions.html


2 ) Installing Postgres

Downloaded source-distribution and unpacked it to my tmp. cd to the
postgres directory.

Read from the manual how to install. Here is it snipped in short form.

./configure 
gmake 
gmake install 
adduser postgres 
mkdir /usr/local/pgsql/data 
chown postgres /usr/local/pgsql/data 
su - postgres 
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data 
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 & 
/usr/local/pgsql/bin/createdb test 
/usr/local/pgsql/bin/psql test 

After this make sure that the lib-files are in a place that Zope can
find them!

3 ) Install egenix mx Extensions

Downloaded source-distribution, since only that has the header-files we
need while compiling psycopg. Unpack into temp. cd to the directory.

python setup.py install


4) Installing psycopg

Unpack to the temp. cd to the directory. Make sure you know where your
egenix-sources are, postgres lib-files and postgres includes. Then give
then in config. Example below.

./configure
--with-mxdatetime-includes=../egenix-mx-base-2.0.2/mx/DateTime/mxDateTime/
--with-postgres-libraries=/usr/local/pgsql/lib
--with-postgres-includes=/usr/local/pgsql/include

make
make install

5) Copying files to rigth places 

This part was frustrating and propably has had everyone wondering for
some time. I found this thing to be helpful, tip was also found on
different mailing lists.

copy mxDateTime.so 
from:       .../python1.5/site-packages/mx/DateTime/mxDateTime.so
to:         .../python1.5/site-packages/mxDateTime.so

so that psycopgmodule.so can find it when zope is started up.

And similarly make sure that the psycopgmodule is located in 
the psycopgmodule.so got placed in
 
 .../python1.5/site-packages/psycopgmodule.so

6) Installing exUserFolder

Just unpack it and copy to zope/Products - where ever you have it.

7) Create database table for authentication

Within the exUserFolder distribution comes a file pgAndreasScheme.sql.
>From that we will use only one table for now.

This is just for testing purposes. 

su postgres

psql template1

CREATE TABLE "passwd" (
        "username" character varying(64) UNIQUE NOT NULL,
        "password" character varying(64) NOT NULL,
        "roles" character varying(255),
        Constraint "passwd_pkey" Primary Key ("username")
);

\q

You have created your table.

8) Restart zope

9) Go into management interface

You should see Z psycopg Database connection in the list available for
selection. If not, go to the control panel and product management and
see what is the traceback on that product. Propably some of the modules
is not found - which can be because you lib-files are somewhwere whwre
they should not be ,-)

Try to add Z psycopg Database connection.

For connection string use:
dbname = template1 user = postgres

Ok.

Everything should work.

You can test it by clicking the test-tab in Z psycopg and trying
following sql-query: select * from passwd;

10) Add exUserFolder

Warning. Don't do this on the root folder!

Select postgres authentication source.

And select rest as you like. Click next.

For authentication properties write:

Table Name: passwd
Username Column: username
Password column: password
Roles Column: roles

Etc.

Have fun. Hope this helps.

-huima