1/*
2   $Id: README.pgsql,v 1.15 2007-05-22 03:58:58 rwidmer Exp $
3*/
4--------------------------------------------------------------------------
5
6Using vpopmail with PostgreSQL is not very common.
7The PostgreSQL modules are understood to be functional, but because it not
8as popular as using CDB or MySQL auth systems, you should be wary of
9implementing the PostgreSQL system on a production server.
10
11The PostgreSQL backend has improved greatly as of vpopmail 5.4.8, and many
12have been using it on production servers.
13
14--------------------------------------------------------------------------
15
16If you are upgrading an existing PostgreSQL installation (that is using
17valiases) to version 5.4.8 or later for the first time, please run the
18following script to convert the columns from char to varchar:
19
20BEGIN;
21ALTER TABLE valias RENAME alias TO alias2;
22ALTER TABLE valias RENAME domain TO domain2;
23ALTER TABLE valias RENAME valias_line TO valias_line2;
24ALTER TABLE valias ADD alias varchar(32);
25ALTER TABLE valias ADD domain varchar(64);
26ALTER TABLE valias ADD valias_line varchar(160);
27UPDATE valias SET alias=trim(alias2), domain=trim(domain2),
28valias_line=trim(valias_line2);
29ALTER TABLE valias ALTER alias SET NOT NULL;
30ALTER TABLE valias ALTER domain SET NOT NULL;
31ALTER TABLE valias ALTER valias_line SET NOT NULL;
32ALTER TABLE valias DROP alias2;
33ALTER TABLE valias DROP domain2;
34ALTER TABLE valias DROP valias_line2;
35COMMIT;
36VACUUM ANALYZE valias;
37
38(Thanks to Sven Willenberger for the previous script.)
39
40------------------------------------------------------------------------------
412003/Dec/29 : Michael Bowe <mbowe@pipeline.com.au>
42
43A QUICK GUIDE TO VPOPMAIL WITH POSTGRESQL
44~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
45Full doc available from :
46http://www.bowe.id.au/michael/isp/vpopmail-postgresql.htm
47
48Note :
49  You should not permit end-users to have shell access to this server.
50  PostgreSQL by default allows any local user to access any database on
51  the server. You can certainly tighten the security of the default
52  PostgreSQL installation, but it is pretty much futile considering that
53  vpopmail stores the PostgresSQL login/pass in the "libvpopmail.a" file.
54  It is straightforward for any knowledgeable local user to be able to
55  extract the user/pass from this file
56
57
58PostgreSQL:
59
60Setup an account for the PostgreSQL server to run under :
61
62	useradd postgres
63
64Download and unpack the source
65
66	cd /usr/local/src
67	wget ftp://ftp.au.postgresql.org/pub/postgresql/v7.3.4/postgresql-7.3.4.tar.gz
68	tar xzf postgresql-7.3.4.tar.gz
69	chown -r root.root postgresql-7.3.4
70	cd postgresql-7.3.4
71
72Compile source (installs to /usr/local/pgsql)
73
74	./configure
75	gmake
76	gmake install
77
78Create the data directory
79
80	mkdir /usr/local/pgsql/data
81	chown postgres /usr/local/pgsql/data
82
83Run the installation script that creates/verifies all the various
84system-use tables etc
85
86	su postgres
87	/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
88
89Fire up the server
90
91	/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data > /usr/local/pgsql/data/serverlog 2>&1 &
92
93At this point the PostgreSQL daemons should be running. A good way
94to verify this is to use this command :
95
96	ps axf
97
98If all is well, you should be able to see something like this :
99
100	388 pts/1 S 0:00 /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
101	389 pts/1 S 0:00   \_ postgres: stats buffer process
102	391 pts/1 S 0:00       \_ postgres: stats collector process
103
104(If you received errors, look in the file /usr/local/pgsql/data/serverlog
105for debugging info)
106
107Configure PostgreSQL so it is running all the time from bootup onwards
108
109	# exit back to the root user from the postgres su
110	exit
111	cp /usr/local/src/postgresql-7.3.4/contrib/start-scripts/linux /etc/rc.d/init.d/postgres
112	chmod 744 /etc/rc.d/init.d/postgres
113	chkconfig --add postgres
114
115vpopmail:
116
117Make the user accounts
118
119	# If you are using RH8.0, you will probably need to run this following command,
120	# because RH8.0 comes preconfigured with UID/GID 89 allocated to postfix
121	#
122	# userdel postfix
123
124	groupadd -g 89 vchkpw
125	useradd -g vchkpw -u 89 vpopmail
126
127	# We recommend you use the user and group id's of 89. The FreeBSD folks
128	# have reserved 89 for the group and 89 for the user for vpopmail.  Feel
129	# free to have the OS assign the group/user id (for example, Solaris won't
130	# allow gid 89).
131
132Download and unpack the source
133
134	cd /usr/local/src
135	wget http://telia.dl.sourceforge.net/sourceforge/vpopmail/vpopmail-5.4.4.tar.gz
136	tar xzf vpopmail-5.4.4.tar.gz
137	chown -R root.root vpopmail-5.4.4
138	cd vpopmail-5.4.4
139
140Create the a vpopmail database in PostgreSQL
141
142	/usr/local/pgsql/bin/createdb --username=postgres --owner=postgres vpopmail
143
144Now, build the program with a configure something like this :
145
146	./configure \
147	  --disable-roaming-users \
148	  --enable-logging=p \
149	  --disable-ip-alias-domains \
150	  --disable-passwd \
151	  --enable-clear-passwd \
152	  --disable-domain-quotas \
153	  --enable-auth-module=pgsql \
154	  --disable-many-domains \
155	  --enable-auth-logging \
156	  --enable-sql-logging \
157	  --enable-valias
158
159	make
160	make install-strip
161
162------------------------------------------------------------------------------
163PREVIOUS VPOPMAIL / PGSQL DOCUMENTATION :
164
1652002/02/22 : N.Fung <nfung@classY.jp>
166
167Notes on translating vmysql.c to vpgsql.c
168* strings in SQL statements are enclosed with ' and not ".
169* there is no "replace into" in pgsql.
170* 'user' is a reserved column name! Changed 'user' to 'userid'.
171
172To get it going become DBA of PostgreSQL server. Then:
173
1741. /path/to/pgsql/bin/createuser vpopmail
175
176   (no need to grant vpopmail dba rights)
177
1782. /path/to/pgsql/bin/createdb vpopmail
179
180If you want to change "vpopmail", make sure you edit vpgsql.h and compile.
181
182---ends---
183
184