> -----Original Message-----
> From: Casey Allen Shobe [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, July 19, 2005 12:56 AM
> To: vchkpw@inter7.com
> Subject: Re: [vchkpw] vpopmail postgresql support
> On Monday 18 July 2005 23:35, Charles J. Boening wrote:
> > Myself and others have done work on the PostgreSQL code.
> Woo hoo!  Light at the end of the tunnel, if a ways off. ;-)
> > It will not create the tables if they exist.
> It doesn't seem to create the vpopmail table even if it 
> doesn't exist.  I would actually prefer if it didn't create 
> tables at all, but came with an example .sql to load that 
> would work with the default configuration without modification.

Ya ... I think having a .sql file would be cool.  It's not there though
(unless I've missed it).  How about making one and submitting it?  Don't
forget to include statements for indexes and foreign keys.

> > 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.
> I would prefer to add a table for the vpopmail-specific 
> values, and then join it with my password table via a view, 
> if possible providing the password to vpopmail only in 
> encrypted form with PostgreSQL's md5() function.

You should be able to do that.  Just make sure you're vpopmail specific
table has all the fields vpopmail requires.  Vpopmail's design was
intended to be used on it's own.  When you're talking about integrating
with existing data you're kind of on you own since not everyone would be
doing this the same way.  What I did on my installation is to let the
individual software (vpopmail, pure-ftp) keep their own structure.  I
just added columns to include my unique user id and then related them
back to my primary customer database.  Not as elegant as a fully
integrated solution but it works.  This way I'm using the structure the
software provides.

Another thought comes to mind and I think it's where you'd like to see
things go.  Take pure-ftp for example.  In it's configuration file you
define database connectivity and sql statements for returning data.  I
guess the direction that could be taken with vpopmail is to abstract the
database layer and let the user define how the data is stored and
returned.  Of course example/recommended database structure could be
included (.sql file) for those wishing to not customize.  The example
and configuration files would also define what data vpopmail expects to
see back from the database.  One could continue this to include things
like runtime configuration of logging options.  Guess this is a whole
different topic for discussion though.  :)

> However I would need to presumably customize whatever SQL is 
> used to update these tables when stuff like vpasswd or 
> vdeluser is used.  
> In an ideal world, I wouldn't have to use vadd/deluser at 
> all, but only modify the database, but I suppose there's no 
> way this is easily possible with qmail/vpopmail.  Not a 
> really big deal.

This is possible.  Whenever I add a user, I do it from a web page and
add the user to the database only.  When their first piece of mail comes
in or they check their email, their directory structure is built.  Very
slick actually.  I call vdeluser for deleting users since it deletes the
user's directory structure.  I could just as easily call "rm -rf" and
pass the user's directory as stored in the database.  I just figured it
was easier to call vdeluser.

> > You also need to edit the vpgsql.h file to match your database, 
> > username and password.
> This makes things really difficult to manage because I prefer 
> to use my distribution's packing system (Gentoo) to keep 
> updated with new releases, which supports such things as 
> preserving configuration files across releases, but does not 
> have any means to accomodate source code modifications prior 
> to compilation without a customized build for each version.  
> I could preserve the .h across releases, but kind of doubt 
> I'll be able to contain all of the changes necessary within 
> that one file with no changes to the C.
> > The reason it was never in a configuration file before was for 
> > efficiency.  That's just one less file open and read.
> vpgsql.h is opened and read, so how is that different from a 
> normal configuration file being opened and read?  In addition 
> the cdb file has to be queried too, though I understand that 
> to be efficient.

The settings from vpgsql.h are compiled in.  This file is not used at
runtime.  As mentioned below though, the configuration file may end up
sitting in a memory buffer.  This would still require I/O though.  Very
fast I/O though.  :)  The CDB is a necessary evil.  You have to store
the user data somewhere.  Configuration compiled into the binary is more
efficient at the expense of flexibility.  Maybe the way here is optional
configuration files.  If the file is there it's used.  If not, then
compiled options are used.  But then again, if you're going through the
process of checking for the file you may as well open it.  See comment
right below about vpopmail daemon.

> > Remember, vpopmail isn't a daemon but a set of programs
> Humm, I guess this is where the separation between qmail and 
> vpopmail shows it's ugly side.

I wouldn't say ugly side.  Qmail even calls external programs such as
qmail-remote, qmail-local, and qmail-inject.  I seem to recall on the
list someone talking about a vpopmail daemon.  Not sure of it's status
or progress but this would be the way to go.  Much more efficient.

> > Ideally a database abstraction layer of some sort would be great.
> So maybe to avoid extensive configuration reading the most 
> efficient approach would indeed be to do everything through 
> stored procedures and views which are defined in an example 
> .sql file that the end user is expected to use, but may 
> change freely as desired to match his database.  It would 
> certainly be easier for me to code that way I'm sure, as I 
> have extensive PostgreSQL knowledge but not so much C, and 
> any customizations could be housed entirely in the .sql file. 
>  The default file could even be changed in future releases of 
> vpopmail without breaking older unchanged databases assuming 
> the procedure names renamed the same.

Doesn't matter.  If you create views and tables in your database to
match what vpopmail needs, then you're all set.  I don't think it
matters if vpopmail officially works off of views, stored procedures or
tables.  If you create a view named the same as a vpopmail required
table, that view would be the one used.  Done deal.  I think what's
missing here is clear definition of what database structure vpopmail

> Customizations would require a bit of database knowledge, but 
> I don't see that as a problem at all since only those with 
> database knowledge will be customizing things.
> The problem I see is that certain features (i.e. clearpasswd) 
> require extra columns in the database that are not present 
> otherwise.  One wouldn't want a stored procedure taking a 
> different number of arguments based on what features were 
> present - this would probably be best handled by adding 
> additional procedures to handle extra features, and add 
> columns for them to the default table definitions which allow 
> null values (but with a comment on the extra columns and 
> functions that say they can be dropped safely if you're not 
> using X feature).

You don't have to enable clear passwords in vpopmail.  That's a
configuration option.  This kind of goes with what you said above about
creating a table for vpopmail specific data and then linking it to the
rest of your database.

> However, a configuration file would still be needed to define 
> the database hostname(s), port, dbname, username, and 
> password.  How would you propose doing that efficiently 
> without a daemon?  On the bright side this could probably 
> done with the existing vpopmail.pgqsl file, even if it needs 
> to be fixed before working like the mysql one.

How often do you change hostname, database, username and password?  I
don't change mine very often.  If I do it would probably just be the
password.  The multiple host is where I see an issue coming in.  I don't
believe vpopmail has support for multiple postgresql database servers.
I know ... Write a script that monitors the main server and if it's
offline modify vpgsql.h and recompile and reinstall vpopmail.  ;)

> Please note the '(s)' above, I think it's pretty important 
> for reliability to have failover support so that another 
> database is queried in the event that the first one doesn't 
> answer within a reasonably short amount of time.
> According to the MySQL documentation, the file format is 
> supposed to look like this:
> read_server|read port|read_user|read_password|database_name
> update_server|update port|update_user|update_password|database_name
> I'm not sure I understand why the ability exists to use a 
> separate username for reading and writing since the passwords 
> are stored in the same file anyways, but perhaps this is for 
> people who want to read off their replication slave and write 
> to the master?
> One might add one more character to the beginning of each 
> line to define which are read accounts and which are write 
> accounts, perhaps a + for write or - for read to enable 
> multiple entries for either, for redundancy purposes.  
> However this would impact performance a tiny bit I'm sure as 
> it would require more examination than "read line 1 and cut 
> by |" in the code.
> I personally don't mind relying on external transparent forms 
> of redundancy (i.e. drbd + heartbeat which we use or load 
> balancers) rather than making the code more complex.  Users 
> without external transparent redundancy could use a cron job 
> that runs once a minute and if the primary is down, sed's 
> this file to update the connection information, but that 
> isn't quite as easy to set up.

See above ... Modify vpqsql.h and recompile and reinstall automagically.

> On the other hand on the busiest mail server this file is 
> probably going to just sit in buffer memory all the time 
> anyways, so does it really matter?


> > I think the problem is time. Those of us who have worked on the 
> > PostgreSQL support have done it in our spare time.
> I don't mind spending time on it, as long as the work is done 
> in the best manner possible and in such a way that it may be 
> accepted into the main distribution instead of used only by me.

The vpopmail developers are very open-minded.  If your changes make
sense, work and make things more efficient, I don't see why they
couldn't be included in the distribution.  Just keep in mind that the
vpopmail postgresql users that are out there probably don't want to go
changing their structure unless they're going to benefit.

> It would be faster for me to just write some scripts to call 
> v{{add,del}{user,domain},passwd,alias{,domain}} on a normal 
> CDB installation as required when database changes are made 
> if nobody else is going to benefit from the work.  I do 
> prefer to make things more DB-centric though.
> > The other problem is lack of interest for PostgreSQL support.
> Well, I think that's changing.  PostgreSQL has an 
> ever-expanding userbase, and at least now when I mention it 
> to technical people they don't all ask "what's that?" first 
> thing like they did 4 years ago.

Agreed.  But still not as popular as the dolphin! :)

> > As I've said, it does work.
> Good to know somebody is actually using it! :)  Makes me feel 
> a little less frustrated and hopeless. :P  Let me know your 
> thoughts on the above ideas and inquiries.
> 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