SQL help please (Mysql/MariaDB).

[C. Petro]

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 at mailserver:/var/mail/vmail# find ./
./
./petro at republicofmean.com
./petro at republicofmean.com/dovecot.index.log
./petro at republicofmean.com/dovecot-uidvalidity.6136d995
./petro at republicofmean.com/tmp
<...>
./petro at republicofmean.com/new
./petro at 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 at 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 = </etc/letsencrypt/live/<redacted>/fullchain.pem
ssl_client_ca_dir = /etc/ssl/certs
ssl_dh = # hidden, use -P to show it
ssl_key = # hidden, use -P to show it
userdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver =
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://dovecot.org/pipermail/dovecot/attachments/20210911/21f845b4/attachment.html>