starred/modoboa-modoboa

Fork 0

mirror of https://github.com/modoboa/modoboa.git synced 2026-04-27 01:45:58 +03:00

[GH-ISSUE #462] Sqlite3 support #445

New issue

Closed

opened 2026-02-27 11:11:49 +03:00 by kerem · 1 comment

kerem commented

2026-02-27 11:11:49 +03:00

Owner

Originally created by @tonioo on GitHub (Dec 4, 2013).
Original GitHub issue: https://github.com/modoboa/modoboa/issues/462

Originally assigned to: @tonioo on GitHub.

Originally created by Ricardo Padilha on 2013-09-27T10:27:43Z

These are the steps required to have an sqlite3-based deployment using Modoboa+Postfix+Dovecot, based on virtual users.

Assumptions:

Postfix configuration is in /etc/postfix
Dovecot configuration is in /etc/dovecot

Global variables:

$NAME = name of the modoboa deployment
$FOLDER = folder where modoboa will be deployed
$SQLITE = complete path to the file of the sqlite3 database, e.g., /home/user/modoboa.sqlite
$MAILBOXES = mailboxes folder
$UID, $GID = uid/gid of the vmail user/group

Replace appropriately in the files below.

Modoboa configuration

Deploy modoboa using the standard command: "modoboa-admin.py deploy $NAME --syncdb"

Then provide the following parameters:

Database type: sqlite3
Database host: 
Database name: $SQLITE
Username: 
Password: 
Under which domain do you want to deploy modoboa: *

After this, you can start modoboa.

Postfix configuration

Make sure main.cf contains these lines:

virtual_mailbox_domains = sqlite:/etc/postfix/sql-domains.cf
virtual_mailbox_maps = sqlite:/etc/postfix/sql-mailboxes.cf
virtual_alias_domains = sqlite:/etc/postfix/sql-domain-aliases.cf
virtual_alias_maps = sqlite:/etc/postfix/sql-aliases.cf,
      sqlite:/etc/postfix/sql-domain-aliases-mailboxes.cf,
      sqlite:/etc/postfix/sql-catchall-aliases.cf

Content of /etc/postfix/sql-aliases.cf:

dbpath = $SQLITE
query = (SELECT (mb.address || '@' || dom.name) FROM admin_mailbox mb INNER JOIN admin_domain dom ON mb.domain_id=dom.id WHERE mb.id IN (SELECT al_mb.mailbox_id FROM admin_alias_mboxes al_mb INNER JOIN admin_alias al ON al_mb.alias_id=al.id INNER JOIN admin_domain dom ON al.domain_id=dom.id WHERE dom.name='%d' AND dom.enabled=1 AND al.address='%u' AND al.enabled=1)) UNION (SELECT (al.address || '@' || dom.name) FROM admin_alias al INNER JOIN admin_domain dom ON al.domain_id=dom.id WHERE al.id IN (SELECT al_al.to_alias_id FROM admin_alias_aliases al_al INNER JOIN admin_alias al ON al_al.from_alias_id=al.id INNER JOIN admin_domain dom ON al.domain_id=dom.id WHERE dom.name='%d' AND dom.enabled=1 AND al.address='%u' AND al.enabled=1)) UNION (SELECT al.extmboxes FROM admin_alias al INNER JOIN admin_domain dom ON al.domain_id=dom.id WHERE dom.name='%d' AND dom.enabled=1 AND al.address='%u' AND al.enabled=1 AND al.extmboxes<>'')

Content of /etc/postfix/sql-catchall-aliases.cf

dbpath = $SQLITE
query = (SELECT (mb.address || '@' || dom.name) FROM admin_mailbox mb INNER JOIN admin_domain dom ON mb.domain_id=dom.id WHERE mb.id IN (SELECT al_mb.mailbox_id FROM admin_alias al INNER JOIN admin_domain dom ON al.domain_id=dom.id INNER JOIN admin_alias_mboxes al_mb ON al.id=al_mb.alias_id WHERE al.enabled=1 AND al.address='*' AND dom.name='%d' AND dom.enabled=1)) UNION (SELECT al.extmboxes FROM admin_alias al INNER JOIN admin_domain dom ON al.domain_id=dom.id WHERE al.enabled='1' AND al.extmboxes<>'' AND al.address='*' AND dom.name='%d' AND dom.enabled=1)

Content of /etc/postfix/sql-domain-aliases-mailboxes.cf

dbpath = $SQLITE
query = SELECT DISTINCT (mb.address || '@' || dom.name) FROM admin_alias al INNER JOIN admin_domain dom ON dom.id=al.domain_id INNER JOIN admin_domainalias domal ON domal.target_id=dom.id INNER JOIN (admin_alias_mboxes almb, admin_mailbox mb) ON (almb.alias_id=al.id AND almb.mailbox_id=mb.id) WHERE domal.name='%d' AND domal.enabled=1 AND (al.address='%u' OR mb.address='%u')

Content of /etc/postfix/sql-domain-aliases.cf

dbpath = $SQLITE
query = (SELECT (mb.address || '@' || dom.name) FROM admin_domainalias domal INNER JOIN admin_domain dom ON domal.target_id=dom.id INNER JOIN admin_mailbox mb ON mb.domain_id=dom.id WHERE domal.name='%d' AND dom.enabled=1 AND mb.address='%u') UNION (SELECT (al.address || '@' || dom.name) FROM admin_domainalias domal INNER JOIN admin_domain dom ON domal.target_id=dom.id INNER JOIN admin_alias al ON al.domain_id=dom.id WHERE domal.name='%d' AND dom.enabled=1 AND al.address='%u')

Content of /etc/postfix/sql-domains.cf

dbpath = $SQLITE
query = SELECT name FROM admin_domain WHERE name='%s' AND enabled=1

Content of /etc/postfix/sql-mailboxes.cf

dbpath = $SQLITE
query = SELECT 1 FROM admin_mailbox mb INNER JOIN admin_domain dom ON mb.domain_id=dom.id INNER JOIN admin_user user ON mb.user_id=user.id WHERE dom.enabled=1 AND dom.name='%d' AND user.is_active=1 AND mb.address='%u'

Dovecot configuration

Make sure that /etc/dovecot/dovecot.conf contains this:

userdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}

passdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}

dict {
  quota = sqlite:/etc/dovecot/dovecot-dict-sql.conf.ext
  expire = sqlite:/etc/dovecot/dovecot-dict-sql.conf.ext
}

Content of /etc/dovecot/dovecot-sql.conf.ext

driver = sqlite
connect = $SQLITE
default_pass_scheme = CRYPT
password_query = SELECT email AS user, password FROM admin_user WHERE email='%u' and is_active=1
user_query = SELECT '$MAILBOXES/%u' AS home, $UID as uid, $GID as gid, ('*:bytes=' || mb.quota || 'M') AS quota_rule FROM admin_mailbox mb INNER JOIN admin_domain dom ON mb.domain_id=dom.id WHERE mb.address='%n' AND dom.name='%d'
iterate_query = SELECT email AS username FROM admin_user

Content of /etc/dovecot/dovecot-dict-sql.conf.ext

driver = sqlite
connect = $SQLITE
map {
  pattern = priv/quota/storage
  table = admin_quota
  username_field = username
  value_field = bytes
}
map {
  pattern = priv/quota/messages
  table = admin_quota
  username_field = username
  value_field = messages
}
map {
  pattern = shared/expire/$user/$mailbox
  table = expires
  value_field = expire_stamp

  fields {
    username = $user
    mailbox = $mailbox
  }
}

Originally created by @tonioo on GitHub (Dec 4, 2013). Original GitHub issue: https://github.com/modoboa/modoboa/issues/462 Originally assigned to: @tonioo on GitHub. **Originally created by Ricardo Padilha on 2013-09-27T10:27:43Z** These are the steps required to have an sqlite3-based deployment using Modoboa+Postfix+Dovecot, based on virtual users. _Assumptions:_ - Postfix configuration is in /etc/postfix - Dovecot configuration is in /etc/dovecot _Global variables:_ $NAME = name of the modoboa deployment $FOLDER = folder where modoboa will be deployed $SQLITE = complete path to the file of the sqlite3 database, e.g., /home/user/modoboa.sqlite $MAILBOXES = mailboxes folder $UID, $GID = uid/gid of the vmail user/group Replace appropriately in the files below. _Modoboa configuration_ Deploy modoboa using the standard command: "modoboa-admin.py deploy $NAME --syncdb" Then provide the following parameters: <pre> Database type: sqlite3 Database host: Database name: $SQLITE Username: Password: Under which domain do you want to deploy modoboa: * </pre> After this, you can start modoboa. _Postfix configuration_ Make sure main.cf contains these lines: <pre> virtual_mailbox_domains = sqlite:/etc/postfix/sql-domains.cf virtual_mailbox_maps = sqlite:/etc/postfix/sql-mailboxes.cf virtual_alias_domains = sqlite:/etc/postfix/sql-domain-aliases.cf virtual_alias_maps = sqlite:/etc/postfix/sql-aliases.cf, sqlite:/etc/postfix/sql-domain-aliases-mailboxes.cf, sqlite:/etc/postfix/sql-catchall-aliases.cf </pre> Content of /etc/postfix/sql-aliases.cf: <pre> dbpath = $SQLITE query = (SELECT (mb.address || '@' || dom.name) FROM admin_mailbox mb INNER JOIN admin_domain dom ON mb.domain_id=dom.id WHERE mb.id IN (SELECT al_mb.mailbox_id FROM admin_alias_mboxes al_mb INNER JOIN admin_alias al ON al_mb.alias_id=al.id INNER JOIN admin_domain dom ON al.domain_id=dom.id WHERE dom.name='%d' AND dom.enabled=1 AND al.address='%u' AND al.enabled=1)) UNION (SELECT (al.address || '@' || dom.name) FROM admin_alias al INNER JOIN admin_domain dom ON al.domain_id=dom.id WHERE al.id IN (SELECT al_al.to_alias_id FROM admin_alias_aliases al_al INNER JOIN admin_alias al ON al_al.from_alias_id=al.id INNER JOIN admin_domain dom ON al.domain_id=dom.id WHERE dom.name='%d' AND dom.enabled=1 AND al.address='%u' AND al.enabled=1)) UNION (SELECT al.extmboxes FROM admin_alias al INNER JOIN admin_domain dom ON al.domain_id=dom.id WHERE dom.name='%d' AND dom.enabled=1 AND al.address='%u' AND al.enabled=1 AND al.extmboxes<>'') </pre> Content of /etc/postfix/sql-catchall-aliases.cf <pre> dbpath = $SQLITE query = (SELECT (mb.address || '@' || dom.name) FROM admin_mailbox mb INNER JOIN admin_domain dom ON mb.domain_id=dom.id WHERE mb.id IN (SELECT al_mb.mailbox_id FROM admin_alias al INNER JOIN admin_domain dom ON al.domain_id=dom.id INNER JOIN admin_alias_mboxes al_mb ON al.id=al_mb.alias_id WHERE al.enabled=1 AND al.address='*' AND dom.name='%d' AND dom.enabled=1)) UNION (SELECT al.extmboxes FROM admin_alias al INNER JOIN admin_domain dom ON al.domain_id=dom.id WHERE al.enabled='1' AND al.extmboxes<>'' AND al.address='*' AND dom.name='%d' AND dom.enabled=1) </pre> Content of /etc/postfix/sql-domain-aliases-mailboxes.cf <pre> dbpath = $SQLITE query = SELECT DISTINCT (mb.address || '@' || dom.name) FROM admin_alias al INNER JOIN admin_domain dom ON dom.id=al.domain_id INNER JOIN admin_domainalias domal ON domal.target_id=dom.id INNER JOIN (admin_alias_mboxes almb, admin_mailbox mb) ON (almb.alias_id=al.id AND almb.mailbox_id=mb.id) WHERE domal.name='%d' AND domal.enabled=1 AND (al.address='%u' OR mb.address='%u') </pre> Content of /etc/postfix/sql-domain-aliases.cf <pre> dbpath = $SQLITE query = (SELECT (mb.address || '@' || dom.name) FROM admin_domainalias domal INNER JOIN admin_domain dom ON domal.target_id=dom.id INNER JOIN admin_mailbox mb ON mb.domain_id=dom.id WHERE domal.name='%d' AND dom.enabled=1 AND mb.address='%u') UNION (SELECT (al.address || '@' || dom.name) FROM admin_domainalias domal INNER JOIN admin_domain dom ON domal.target_id=dom.id INNER JOIN admin_alias al ON al.domain_id=dom.id WHERE domal.name='%d' AND dom.enabled=1 AND al.address='%u') </pre> Content of /etc/postfix/sql-domains.cf <pre> dbpath = $SQLITE query = SELECT name FROM admin_domain WHERE name='%s' AND enabled=1 </pre> Content of /etc/postfix/sql-mailboxes.cf <pre> dbpath = $SQLITE query = SELECT 1 FROM admin_mailbox mb INNER JOIN admin_domain dom ON mb.domain_id=dom.id INNER JOIN admin_user user ON mb.user_id=user.id WHERE dom.enabled=1 AND dom.name='%d' AND user.is_active=1 AND mb.address='%u' </pre> _Dovecot configuration_ Make sure that /etc/dovecot/dovecot.conf contains this: <pre> userdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } passdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } dict { quota = sqlite:/etc/dovecot/dovecot-dict-sql.conf.ext expire = sqlite:/etc/dovecot/dovecot-dict-sql.conf.ext } </pre> Content of /etc/dovecot/dovecot-sql.conf.ext <pre> driver = sqlite connect = $SQLITE default_pass_scheme = CRYPT password_query = SELECT email AS user, password FROM admin_user WHERE email='%u' and is_active=1 user_query = SELECT '$MAILBOXES/%u' AS home, $UID as uid, $GID as gid, ('*:bytes=' || mb.quota || 'M') AS quota_rule FROM admin_mailbox mb INNER JOIN admin_domain dom ON mb.domain_id=dom.id WHERE mb.address='%n' AND dom.name='%d' iterate_query = SELECT email AS username FROM admin_user </pre> Content of /etc/dovecot/dovecot-dict-sql.conf.ext <pre> driver = sqlite connect = $SQLITE map { pattern = priv/quota/storage table = admin_quota username_field = username value_field = bytes } map { pattern = priv/quota/messages table = admin_quota username_field = username value_field = messages } map { pattern = shared/expire/$user/$mailbox table = expires value_field = expire_stamp fields { username = $user mailbox = $mailbox } } </pre>

kerem

2026-02-27 11:11:49 +03:00

closed this issue
added the
enhancement
label

kerem commented

2026-02-27 11:11:50 +03:00

Author

Owner

@tonioo commented on GitHub (Dec 4, 2013):

Posted by Antoine Nguyen on 2013-09-27T18:12:07Z

Applied in changeset commit:8bb753e89003309a67370419a9c5cb6c0f9bf28b.

@tonioo commented on GitHub (Dec 4, 2013): **Posted by Antoine Nguyen on 2013-09-27T18:12:07Z** Applied in changeset commit:8bb753e89003309a67370419a9c5cb6c0f9bf28b.