SQL iterate_query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL iterate_query

Dovecot mailing list
I've been hunting some ghost mailboxes - and I *think* I found the source.

I use the complete email address as the username, and store such in a
database. The storage structure is location=/var/mail/%d/%n. Not unusual
I think.

So all I *should* see from "ls /var/mail" would be a list of domains.
But I keep seeing empty mailboxes being created at this level. Having
corrected a few other errors I *hope* I've found the last one - but if
I'm right I believe the docs need updating:

The examples given for SQL userdb's include:
iterate_query = SELECT userid AS username, domain FROM users

So this means the username is returned for *both* the username and
domain. Even if I'm wrong as to the cause of my own troubles this can't
be right. It just can't. Or am I mistaken?

So, given that the complete address is used as the username I now use:
iterate_query = SELECT username FROM mailbox
(I'm using postfixadmin to administer this - and "mailbox" is the
default user table name)

I believe the alternative would be an explicit:
iterate_query = SELECT username, domain AS username, domain FROM users

I don't *think* that would make any security difference for my use case
so why add the extra processing?

I believe the documentation should be updated, or at least clarified, on
this issue.
--
Daniel

Reply | Threaded
Open this post in threaded view
|

Re: SQL iterate_query

Dovecot mailing list
On 25/10/2019 00:00, Daniel Miller via dovecot wrote:

> So, given that the complete address is used as the username I now use:
> iterate_query = SELECT username FROM mailbox

https://doc.dovecot.org/configuration_manual/authentication/sql/#user-iteration

...says that iterate_query has two variants.  You have complete
addresses in a database column "username" so you need the first.  The
addresses must be return in a column named "user", hence add "AS user"
to label your column.  Try:

iterate_query = SELECT username AS user FROM mailbox