dovecot - Oct 2011 - 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 at 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