[prev in list] [next in list] [prev in thread] [next in thread] 

List:       vchkpw
Subject:    RE: [vchkpw] vpopmail postgresql support == UGH!!!!
From:       "Charles J. Boening" <charlieb () cot ! net>
Date:       2005-07-18 23:35:48
Message-ID: 20DF280A12FBD1478CDCDEA366ACE077167F2B () apollo ! calore ! local
[Download RAW message or body]

Casey,

I'm using PostgreSQL support in a production environment.  I actually
have installed it on production systems for customers as well.

Myself and others have done work on the PostgreSQL code.  It's not
pretty but it does work.  It will not create the tables if they exist.
It's not elegant, but basically the code executes the query.  If the
query fails, it assumes the table doesn't exist and creates it.  This
code has been in there for ages (still is isn't it?).  Again, not the
most elegant solution but it does work.

Basically you need to create a view called vpopmail.  You'll also have
to either create a table to store vpopmail specific columns or add them
to your existing structure.  You also need to edit the vpgsql.h file to
match your database, username and password.  I believe the
vpopmail.pgsql file is there to put the database, username and password
in.  I think the vpopmail.mysql one is working but I'm not sure about
the PostgreSQL one.  The reason it was never in a configuration file
before was for efficiency.  That's just one less file open and read.  On
a very busy system, the less you have to touch files the better.
Remember, vpopmail isn't a daemon but a set of programs (mostly
vdelivermail and vchkpw) that run for every mail delivered.  Take a
system handling hundreds of thousands of mails a day and one less file
to deal with may be a blessing.  :)

I don't believe the changes you're talking about are that extensive.  It
just takes a little understanding of what's going on and how to work
around it or with it.  The first time I looked at the code, it took me a
while to get what was going on.  Of course my C could be better and I
wasn't familiar with the PostgreSQL functions.

Ideally a database abstraction layer of some sort would be great.  That
would get rid of a lot of redundant code.  I think the problem is time.
Those of us who have worked on the PostgreSQL support have done it in
our spare time.  The work has been primarily to get it working at all.
:)  The other problem is lack of interest for PostgreSQL support.  We're
the minority in a MySQL world.

As I've said, it does work.

Charlie




> -----Original Message-----
> From: Casey Allen Shobe [mailto:lists@seattleserver.com] 
> Sent: Monday, July 18, 2005 3:01 PM
> To: vchkpw@inter7.com
> Subject: [vchkpw] vpopmail postgresql support == UGH!!!!
> 
> 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 
> cshobe@seattleserver.com | cell 425-443-4653 AIM & Yahoo:  
> SomeLinuxGuy | ICQ:  1494523 SeattleServer.com, Inc. | 
> http://www.seattleserver.com
> 
> 

[prev in list] [next in list] [prev in thread] [next in thread]