So here's my situation:  I've been working to get all 
authentications consolidated into a central database.  With 
pure-ftpd, apache (using mod_auth_pgsql), and other software, I've 
been able to define a clean, normalized database structure for 
storing all of my E-mail addresses and passwords.

I've been using E-mail addresses as the usernames everywhere, so 
that our clients can use the exact same email address and password 
to log in anywhere.

In our admin interface, domain admins can check boxes to 
enable/disable i.e. ftp access per E-mail address.

This updates boolean values in a master passwords table, which also 
contains cleartext passwords.

The other software then use views, one exists for ftp_passwords, one 
exists for stats_passwords, etc, and when possible, we use 
PostgreSQL's md5() function to provide only encrypted versions of 
the passwords to the programs for security purposes.  The views 
only show accounts with the relevant boolean value set to true.

I've been naively believing vpopmail to have good and proper 
database support (meaning a good configuration file where I can 
define the queries to insert/update/delete/read authentication 
information).  HAHAHA.

So I recompiled vpopmail with postgresql support, and discovered a 
big can of unpleasant worms awaiting.  An empty vpopmail.pgsql file 
appeared in /var/vpopmail/etc, with no indication anywhere in any 
of the documentation of what it's supposed to contain.  vpopmail 
presumes on it's own that it should log in with the default 
database superuser account named 'postgres' which doesn't exist on 
all my servers, using no password which also won't work on most of 
my servers, connecting to a database named vpopmail which certainly 
doesn't exist (nor do I want it to - I want to use new tables/views 
in my existing database).

Nevermind for the time being, disable local password checking and 
make a postgres user and a database named vpopmail owned by that 
user.  Suddenly errors start showing up in the postgres log because 
vpopmail is querying tables that don't exist.  Maybe this happens 
during compilation?  Recompiled, nope.  Still don't exist.  Try a 
vadduser, it creates 3 tables (ugh), but still there's lots of 
failed attempts to access a vpopmail table.

Finally found some clues in vpgsql.h where it appears I must make 
massive changes hardcoded in C to do what I want, meaning upgrading 
is going to be a severe nightmare.  *sigh*

Found this lovely note too:
Note :
  You should not permit end-users to have shell access to this
  server.  PostgreSQL by default allows any local user to access any
  database on the server. You can certainly tighten the security of
  the default PostgreSQL installation, but it is pretty much futile
  considering that vpopmail stores the PostgresSQL login/pass in the
  "libvpopmail.a" file.  It is straightforward for any knowledgeable
  local user to be able to extract the user/pass from this file

Well users don't have shell access, but this seems really dumb.

The username and password, along with database and query 
definitions, belong in a configuration file readable only by root 
(and thus a SUID vchkpw, if you choose to +s it (necessary for 
qmail-smtpd to work)).

The PostgreSQL support seems really really bad right now, lacking 
any flexibility and buggy besides.  I'm quite tempted to encourage 
scrapping it altogether and writing it anew.  Is anybody using the 
existing postgresql support in vpopmail?  I'm guessing not since it 
doesn't work without lots of manual effort...

If I endeavor to rewrite it in better form, would it be accepted 
into the main project?  I will need help from somebody who is more 
familiar with C, as I'm not much beyond a web (i.e. PHP/Perl) 
programmer, but I am very good with the database stuff.

With PostgreSQL, the configuration doesn't even need to be *that* 
extensive if the code is written with the expectation of reading 
from views and updating via stored procedures, since they would be 
easy to change to the end user's needs.  But full configuration 
options would allow one to use stored procedures or they not as 
they desire which might be simpler for many, and this could also be 
beneficial to add to the mysql module I assume, since mysql doesn't 
support either.

A quick search shows that postfix does this better, though it 
doesn't appear they store very much in the DB from a glance:
http://www.postfix.org/PGSQL_README.html

Cheers,
-- 
Casey Allen Shobe | http://casey.shobe.info
[EMAIL PROTECTED] | cell 425-443-4653
AIM & Yahoo:  SomeLinuxGuy | ICQ:  1494523
SeattleServer.com, Inc. | http://www.seattleserver.com

Reply via email to