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.

> 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.

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.

> 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.

> 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.

> 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.

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).

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.

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.

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.

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 

> 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.

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