SQL help please (Mysql/MariaDB).

Sun Sep 12 19:45:45 EEST 2021

That appears to have done the trick.

Thank you for your quick response.

I probably should have found that setting. Sorry.

On Sun, Sep 12, 2021 at 10:39 AM Aki Tuomi <aki.tuomi at open-xchange.com>
wrote:

> Try
>
> mail_location = maildir:/var/mail/vmail/%Ld/%Ln
>
> Aki
>
> > On 12/09/2021 19:38 C. Petro <petro at cpetro.us> wrote:
> >
> >
> > 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 (
> http://petro@republicofmean.com/dovecot.index.log)
> > > ./petro at republicofmean.com/dovecot-uidvalidity.6136d995 (
> http://petro@republicofmean.com/dovecot-uidvalidity.6136d995)
> > > ./petro at republicofmean.com/tmp (http://petro@republicofmean.com/tmp)
> > > <...>
> > > ./petro at republicofmean.com/new (http://petro@republicofmean.com/new)
> > > ./petro at republicofmean.com/subscriptions (
> http://petro@republicofmean.com/subscriptions)
> > > ./republicofmean.com (http://republicofmean.com)
> > > ./republicofmean.com/petro (http://republicofmean.com/petro)
> > > ./republicofmean.com/petro/tmp (http://republicofmean.com/petro/tmp)
> > > ./republicofmean.com/petro/cur (http://republicofmean.com/petro/cur)
> > > ./republicofmean.com/petro/new (http://republicofmean.com/petro/new)
> > > ./
> republicofmean.com/petro/new/1630897562.Vfd00Ib40009M307139.mailserver (
> http://republicofmean.com/petro/new/1630897562.Vfd00Ib40009M307139.mailserver
> )
> > > <....>
> > > ./
> republicofmean.com/petro/new/1630899062.Vfd00Ib40011M264001.mailserver (
> http://republicofmean.com/petro/new/1630899062.Vfd00Ib40011M264001.mailserver
> )
> > > ./republicofmean.com/cer (http://republicofmean.com/cer)
> > > ./republicofmean.com/cer/tmp (http://republicofmean.com/cer/tmp)
> > > ./republicofmean.com/cer/cur (http://republicofmean.com/cer/cur)
> > > ./republicofmean.com/cer/new (http://republicofmean.com/cer/new)
> > > ./republicofmean.com/cer/new/1630900562.Vfd00Ib40013M218855.mailserver
> (
> http://republicofmean.com/cer/new/1630900562.Vfd00Ib40013M218855.mailserver
> )
> > > ./republicofmean.com/cer/new/1630899062.Vfd00Ib40010M259724.mailserver
> (
> http://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 (
> http://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 (
> http://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 (
> http://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 (http://mysql_virtual_alias_maps.cf):
> > > query = SELECT goto FROM alias WHERE address='%s' AND active = '1'
> > > mysql_virtual_domains_maps.cf (http://mysql_virtual_domains_maps.cf):
> > > query = SELECT domain FROM domain WHERE domain='%s' AND active = '1'
> > > mysql_virtual_mailbox_limit_maps.cf (
> http://mysql_virtual_mailbox_limit_maps.cf):
> > > query = SELECT quota FROM mailbox WHERE username='%s' AND active = '1'
> > > mysql_virtual_mailbox_maps.cf (http://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/ac8152f3/attachment.html>