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