Re: SQL help please (Mysql/MariaDB).

12 Sep 2021 · *almost*


      To be more clear about my problem.
This server will be hosting multiple email domains, and I would prefer to
have the filesystem structured like /var/mail/vmail/<domain>/<user> rather
than /var/mail/vmail/<user>@<domain>.
On Sat, Sep 11, 2021 at 3:06 PM C. Petro petro@cpetro.us wrote:
...
I'm trying to build a mail server for a friend using Postfix, Dovecot and
Postfixadmin, using Mariadb as the backend for user information and
authentication.
I've got it *almost* working.
(dovecot -n at the bottom because I don't think the problem is in the SQL)
I have things up to the point where mail comes in, and where you can get
Thunderbird to connect to Dovecot, but delivery is looking at a different
directory than IMAP:
root@mailserver:/var/mail/vmail# find ./
./
./petro@republicofmean.com
./petro@republicofmean.com/dovecot.index.log
./petro@republicofmean.com/dovecot-uidvalidity.6136d995
./petro@republicofmean.com/tmp
<...>
./petro@republicofmean.com/new
./petro@republicofmean.com/subscriptions
./republicofmean.com
./republicofmean.com/petro
./republicofmean.com/petro/tmp
./republicofmean.com/petro/cur
./republicofmean.com/petro/new
./republicofmean.com/petro/new/1630897562.Vfd00Ib40009M307139.mailserver
<....>
./republicofmean.com/petro/new/1630899062.Vfd00Ib40011M264001.mailserver
./republicofmean.com/cer
./republicofmean.com/cer/tmp
./republicofmean.com/cer/cur
./republicofmean.com/cer/new
./republicofmean.com/cer/new/1630900562.Vfd00Ib40013M218855.mailserver
./republicofmean.com/cer/new/1630899062.Vfd00Ib40010M259724.mailserver
The SQL I think Dovecot is using:
root@mailserver:/etc/dovecot# grep -i select dovecot-sql.conf.ext | grep
-v "^#"
password_query = SELECT username as user, domain, password FROM mailbox
WHERE username = '%u'
user_query = select  CONCAT("/var/mail/vmail/",maildir) as home, 500 as
uid, 500 as gid from mailbox where username="%u"
Here's what  Postfix is using:
mysql_virtual_alias_domain_catchall_maps.cf:
query  = SELECT goto FROM alias,alias_domain WHERE
alias_domain.alias_domain = '%d' and alias.address = CONCAT('@',
alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active='1'
mysql_virtual_alias_domain_mailbox_maps.cf:
query = SELECT maildir FROM mailbox,alias_domain WHERE
alias_domain.alias_domain = '%d' and mailbox.username = CONCAT('%u', '@',
alias_domain.target_domain) AND mailbox.active = 1 AND
alias_domain.active='1'
mysql_virtual_alias_domain_maps.cf:
query = SELECT goto FROM alias,alias_domain WHERE
alias_domain.alias_domain = '%d' and alias.address = CONCAT('%u', '@',
alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active='1'
mysql_virtual_alias_maps.cf:
query = SELECT goto FROM alias WHERE address='%s' AND active = '1'
mysql_virtual_domains_maps.cf:
query          = SELECT domain FROM domain WHERE domain='%s' AND active
= '1'
mysql_virtual_mailbox_limit_maps.cf:
query = SELECT quota FROM mailbox WHERE username='%s' AND active = '1'
mysql_virtual_mailbox_maps.cf:
query           = SELECT maildir FROM mailbox WHERE username='%s' AND
active = '1'
# dovecot -n
# 2.3.7.2 (3c910f64b): /etc/dovecot/dovecot.conf
# Pigeonhole version 0.5.7.2 ()
# OS: Linux 5.4.0-81-generic x86_64 Ubuntu 20.04.3 LTS ext4
# Hostname: <redacted>
auth_mechanisms = plain login
log_path = /var/log/dovecot.log
login_greeting = Not your mail server
mail_location = maildir:/var/mail/vmail/%u/
mail_max_userip_connections = 50
mail_privileged_group = vmail
namespace inbox {
inbox = yes
location =
mailbox Drafts {
special_use = \Drafts
}
mailbox Junk {
special_use = \Junk
}
mailbox Sent {
special_use = \Sent
}
mailbox "Sent Messages" {
special_use = \Sent
}
mailbox Trash {
special_use = \Trash
}
prefix =
}
passdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
protocols = " imap lmtp pop3 submission"
service auth-worker {
user = vmail
}
service auth {
unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0600
user = postfix
}
unix_listener auth-userdb {
mode = 0600
user = vmail
}
user = dovecot
}
service imap-login {
inet_listener imap {
port = 143
}
inet_listener imaps {
port = 993
ssl = yes
}
}
service lmtp {
unix_listener lmtp {
group = postfix
mode = 0600
user = postfix
}
}
service pop3-login {
inet_listener pop3 {
port = 110
}
inet_listener pop3s {
port = 995
ssl = yes
}
}
ssl_cert =