[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?
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
I always keep it seperate, the user table is used by dovecot only, and
the alias table is used by postfix.
And then for users, you just alias them to themselfs. Then everything
exists in the alias table, and postfix knows all valid users, cause
they all exist in the alias table.
I just think of it as, user accounts, and email addresses that map to
the user accounts.
Quoting jake0534@airpost.net:
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
Hey,
On Monday, October 10, 2011 7:46 PM, "Patrick Domack" <patrickdk@patrickdk.com> wrote:
I always keep it seperate, the user table is used by dovecot only, and
the alias table is used by postfix.And then for users, you just alias them to themselfs. Then everything
exists in the alias table, and postfix knows all valid users, cause
they all exist in the alias table.I just think of it as, user accounts, and email addresses that map to
the user accounts.
So you're not using LMTP authentication to verify that Postfix is trying to deliver to a valid user? I though that's what it was for.
In your setup, 'who' does Postfix try to deliver to, then? The 'real' user, after figuring out and remapping any aliases to it? Or to the alias, and then Dovecot does the remapping?
Jake
postfix delivers to the user, and I don't do lmtp authentication, I
just submit the email from postfix to lmtp.
I'm not even sure how you can use lmtp authentication to verify a
delivery address, normally when postfix uses lmtp the email was
accepted, and therefor would generate a bounce.
I can't find anything that wouldn't bounce using lmtp, in postfix or
dovecot documentation.
Quoting jake0534@airpost.net:
Hey,
On Monday, October 10, 2011 7:46 PM, "Patrick Domack" <patrickdk@patrickdk.com> wrote:
I always keep it seperate, the user table is used by dovecot only, and the alias table is used by postfix.
And then for users, you just alias them to themselfs. Then everything exists in the alias table, and postfix knows all valid users, cause they all exist in the alias table.
I just think of it as, user accounts, and email addresses that map to the user accounts.
So you're not using LMTP authentication to verify that Postfix is trying to deliver to a valid user? I though that's what it was for.
In your setup, 'who' does Postfix try to deliver to, then? The 'real' user, after figuring out and remapping any aliases to it? Or to the alias, and then Dovecot does the remapping?
Jake
On Tuesday, October 11, 2011 8:37 AM, "Patrick Domack" <patrickdk@patrickdk.com> wrote:
postfix delivers to the user, and I don't do lmtp authentication, I
just submit the email from postfix to lmtp.
Ok, then we have different approaches.
So you are using *only* Postfix to check if a received user is valid, right?
I'm not even sure how you can use lmtp authentication to verify a
delivery address, normally when postfix uses lmtp the email was
accepted, and therefor would generate a bounce.I can't find anything that wouldn't bounce using lmtp, in postfix or
dovecot documentation.
If you follow this,
http://wiki2.dovecot.org/HowTo/PostfixDovecotLMTP
it works OK. There is no bounce when Postfix submit mail to LMTP for delivery and the user is authenticated as valid.
Jake
I'm totally confused by your authenticated as valid part, lmtp doesn't
authenticate anything.
How do you let postfix know about valid recipients, I don't really
care about authenticated ones, just valid ones, and if you do that at
lmtp time, postfix has already received the email, and therefor it
would bounce if lmtp says it's not a valid recipient.
Quoting jake0534@airpost.net:
On Tuesday, October 11, 2011 8:37 AM, "Patrick Domack" <patrickdk@patrickdk.com> wrote:
postfix delivers to the user, and I don't do lmtp authentication, I just submit the email from postfix to lmtp.
Ok, then we have different approaches.
So you are using *only* Postfix to check if a received user is valid, right?
I'm not even sure how you can use lmtp authentication to verify a delivery address, normally when postfix uses lmtp the email was accepted, and therefor would generate a bounce.
I can't find anything that wouldn't bounce using lmtp, in postfix or dovecot documentation.
If you follow this,
http://wiki2.dovecot.org/HowTo/PostfixDovecotLMTP
it works OK. There is no bounce when Postfix submit mail to LMTP for delivery and the user is authenticated as valid.
Jake
participants (2)
-
jake0534＠airpost.net
-
Patrick Domack