[Dovecot] To query two SQL tables for user verification over LMTP, should I use (a) two separate lookups? or (b) just one lookup with a boolean query?

jake0534 at airpost.net jake0534 at airpost.net
Tue Oct 11 01:17:17 EEST 2011


Hey all

I've been running a Dovecot 1X server for awhile.  Most of the config
was pretty simple using flat files.

I'm switching to Dovecot 2X now, and want to switch to using SQL for
lookups so I can share info with the Postfix front end.

For starters I setup two SIMPLE SQL tables -- a 'users' table and a
'aliases' table, where each user can have many aliases.

I want to have Postfix check Dovecot's SASL/LMTP passdb to see if a
<user>@<domain> exists.  If yes, deliver through LMTP.  If NO, reject
it.

Here are the table definitions so far,

CREATE TABLE user (
 userid TINYINT,
 user VARCHAR(64),
 domain VARCHAR(128),
 password VARCHAR(64),
 PRIMARY KEY (userid),
 UNIQUE (user,domain)
);

CREATE TABLE alias (
 aliasid TINYINT,
 alias VARCHAR(64),
 user VARCHAR(64),
 domain VARCHAR(128),
 PRIMARY KEY (aliasid),
 UNIQUE (domain,user,alias),
 CONSTRAINT fk_alias1 FOREIGN KEY (user) REFERENCES user (user) ON
 DELETE NO ACTION  ON UPDATE NO ACTION,
 CONSTRAINT fk_alias2 FOREIGN KEY (domain) REFERENCES user (domain) ON
 DELETE NO ACTION  ON UPDATE NO ACTION
);

With flat file and just one table, 'users', I get how to setup LMTP. I
can get Postfix delivering/rejecting over LMTP depending on if the user
exists in the Dovecot passdb or not.

I don't get how to do the SQL query in Dovecot if I have the TWO tables.
 An inbound <user>@<domain> needs to be auth'd as valid if it exists in
EITHER 'user' OR 'alias' table.

Do I need to use a single lookup for passdb with some sort of "IF EXISTS
IN TABLE 'user' OR TABLE 'alias'"  query?

Or do I need to have TWO lookups, the main one for 'user' and a fallback
one for 'alias' (like talked about here
http://wiki2.dovecot.org/Authentication/MultipleDatabases)?

Cheers!

Jake


More information about the dovecot mailing list