SQL help please (Mysql/MariaDB).

[C. Petro]

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 at 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 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/20210912/491a936e/attachment.html>