Can I create and use my own Web Interface on provided database to setup Mailing application? about dbmail

[CozC]

Salve, I'm sorry for late reply. The following notes are meant to be used in Centos 7 environment, but, with minor modification (using apt-get instead of yum and some configuration files on ubuntu may be in diffrent location) you should be fine. Also the notes are related to mariadb not mysql (also you should be fine, most of the changes are related to defaults of mariadb installation) Be careful with compilation parameters. The notes assumes you are using a 64bit os, and on Centos 7 those libs are stored in lib64 directories. I do not know what are conditions on ubuntu Have a nice day.

…

On Sun, 08 Dec 2019 03:26:46 +0200, RebootCode ***@***.***> wrote: > > salve rebootcode I can give a document I use it to install dbmail. It > may help you but you should know a little bit of linux to use it. Please share docs that can help in Installation in today's Ubuntu 18.04 — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

-- Cosmin Cioranu url:http://www.iredmail.org/docs/dbmail.mysql.centos.html#install-dbmai %prerequisites #yum install gcc glibc-headers gcc-c++ openssl-devel postfix libevent-devel %if mariadb installed #yum install MariaDB-devel %install libsieve #yum install flex %install libsieve # wget https://sourceforge.net/projects/libsieve/files/libsieve/2.2.7/libsieve-2.2.7.tar.gz/download % unarchive goto to src folder # ./configure --prefix=/usr --libdir=/usr/lib64 # make # make install %install libzdb # wget http://www.tildeslash.com/libzdb/dist/libzdb-3.1.tar.gz # tar xvf libzdb-3.1.tar.gz # cd libzdb-3.1 # for mysql #./configure --prefix=/usr --with-mysql=/usr/bin/mysql_config --libdir=/usr/lib64 --enable-protected # for mariadb #./configure --prefix=/usr --with-mysql=/usr/bin/mariadb_config --libdir=/usr/lib64 --enable-protected --enable-optimized % if there is an error such as "setjpm error", it means mariadb library is not linked corectly, % fix # ln -s /usr/lib64/libmariadbclient.a /usr/lib64/libmariadb.a # make # make install % GMime is part of epel-release # yum install gmime-devel % mhash is part of epel-release # yum install mhash* % install dbmail # wget http://dbmail.org/download/3.2/dbmail-3.2.3.tar.gz # ./configure --with-sieve=/usr --with-zdb=/usr --with-logdir --prefix=/usr --sysconfdir=/etc --enable-systemd --enable-shared --exec-prefix=/usr --libdir=/usr/lib64 # make all # make install % enable services # systemctl enable dbmail-lmtpd.service # systemctl enable dbmail-imapd.service # systemctl enable dbmail-pop3d.service % create db # mysql -u root -p DBMail < mysql/create_tables.mysql # mysql -u root -p DBMail < mysql/3_0_2-3_1_0.mysql # mysql -u root -p DBMail < mysql/upgrades/32001.mysql # mysql -u root -p DBMail < mysql/upgrades/32002.mysql # mysql -u root -p DBMail < mysql/upgrades/32003.mysql # mysql -u root -p DBMail < mysql/upgrades/32004.mysql #cd /dbmail-src %check installation text %dbmail Database configuration #mysql /> create database DBMail; #mysql /> create user dbmail; #mysql /> grant all privileges on DBMail.* to dbmail@'x.x.x.x identified by '??????'; #mysql -u root -p DBMail < ./sql/mysql/create_tables.mysql %config dbmail app #useradd -s /usr/sbin/nologin dbmail #mkdir /var/run/dbmail #chown dbmail:dbmail /var/run/dbmail %configure DBMail % Edit /etc/dbmail.conf <file> # (c) 2000-2006 IC&S, The Netherlands # # Configuration file for DBMAIL [DBMAIL] # # Database settings # # database connection URI #dburi = sqlite:///var/tmp/dbmail.db dburi = mysql://[user]:[password]@[ip]:3306/DBMail # # Supported drivers are sql, ldap. # authdriver = sql # # # following fields are now DEPRECATED! #driver = #host = #sqlport = #sqlsocket = #user = #pass = #db = # # Number of database connections per threaded daemon # This also determines the size of the worker threadpool # # Do NOT increase this without proper consideration. A # very large database/worker pool will not only increase # the connection pressure on the database, but will more # significantly cause unnecessary context-switching in # your CPUs. # max_db_connections = 1024 # # Table prefix. Defaults to "dbmail_" if not specified. # table_prefix = dbmail_ # # encoding must match the database/table encoding. # i.e. latin1, utf8 encoding = utf8 # # messages with unknown encoding will be assumed to have # default_msg_encoding # i.e. iso8859-1, utf8 default_msg_encoding = utf8 # # Postmaster's email address for use in bounce messages. # #postmaster = DBMAIL-MAILER # # Sendmail executable for forwards, replies, notifies, vacations. # You may use pipes (|) in this command, for example: # dos2unix|/usr/sbin/sendmail works well with Qmail. # You may use quotes (") for executables with unusual names. # sendmail = /usr/sbin/sendmail # # # The following items can be overridden in the service-specific sections. # # # # Logging via stderr/log file and syslog # # Logging is broken up into 8 logging levels and each level can be indivually turned on or off. # The Stderr/log file logs all entries to stderr or the log file. # Syslog logging uses the facility mail and the logging level of the event for logging. # Syslog can then be configured to log data according to the levels. # # Set the log level to the sum of the values next to the levels you want to record. # 1 = Emergency # 2 = Alert # 4 = Critical # 8 = Error # 16 = Warning # 32 = Notice # 64 = Info # 128 = Debug # 256 = Database -> Logs at debug level # # Examples: 0 = Nothing # 31 = Emergency + Alert + Critical + Error + Warning # 511 = Everything # file_logging_levels = 31 # syslog_logging_levels = 31 # # Generate a log entry for database queries for the log level at number of seconds of query execution time. # query_time_info = 10 query_time_notice = 20 query_time_warning = 30 # # Throw an exception is the query takes longer than query_timeout seconds query_timeout = 300 # # Root privs are used to open a port, then privs # are dropped down to the user/group specified here. # effective_user = nobody effective_group = nogroup # # The IPv4 and/or IPv6 addresses the services will bind to. # Use * for all local interfaces. # Use 127.0.0.1 for localhost only. # Separate multiple entries with spaces ( ) or commas (,). # #bindip = 127.0.0.1 # IPv4 only - all IP's #bindip = :: # IPv4 and IPv6 - all IP's (linux) #bindip = :: # IPv6 only - all IP's (BSD) bindip = 0.0.0.0,:: # IPv4 and IPv6 - all IP's (BSD) # # The maximum length of the queue of pending connections. See # listen(2) for more information # backlog = 128 # # Idle time allowed before a connection is shut off. # timeout = 300 # # Idle time allowed before a connection is shut off if you have not logged in yet. # login_timeout = 60 # # If yes, resolves IP addresses to DNS names when logging. # resolve_ip = no # # If yes, keep statistics in the authlog table for connecting users # authlog = yes # # logfile for stdout messages # logfile = /var/log/dbmail/dbmail.log # # logfile for stderr messages # errorlog = /var/log/dbmail/dbmail.err # # directory for storing PID files # pid_directory = /var/run # # directory for locating libraries (normally has a sane default compiled-in) # #library_directory = /usr/lib/dbmail # # SSL/TLS certificates # # A file containing a list of CAs in PEM format tls_cafile = # A file containing a PEM format certificate tls_cert = # A file containing a PEM format RSA or DSA key tls_key = # A cipher list string in the format given in ciphers(1) tls_ciphers = # hashing algorithm. You can select your favorite hash type # for generating unique ids for message parts. # # for valid values check mhash(3) but minus the MHASH_ prefix. # # if you ever change this value run 'dbmail-util --rehash' to # update the hash for all mimeparts. # # examples: MD5, SHA1, SHA256, SHA512, TIGER, WHIRLPOOL # hash_algorithm = MD5 # header_cache tuning # # set header_cache_readonly to 'yes' to prevent new # unknown header-names from being cached. # header_cache_readonly = no [LMTP] port = 24 #tls_port = [POP] port = 110 #tls_port = 995 # You can set an alternate banner to display when connecting to the service # banner = DBMAIL pop3 server ready to rock # # If yes, allows SMTP access from the host IP connecting by POP3. # This requires addition configuration of your MTA # pop_before_smtp = no [HTTP] port = 41380 # # the httpd daemon provides full access to all users, mailboxes # and messages. Be very careful with this one! bindip = 127.0.0.1 admin = admin:secret [IMAP] # You can set an alternate banner to display when connecting to the service # banner = imap 4r1 server (dbmail 2.3.x) # # Port to bind to. # port = 143 #tls_port = 993 # # IMAP prefers a longer timeout than other services. # timeout = 4000 # # If yes, allows SMTP access from the host IP connecting by IMAP. # This requires addition configuration of your MTA # imap_before_smtp = no # # during IDLE, how many seconds between checking the mailbox # status (default: 30) # # idle_timeout = 30 # during IDLE, how often should the server send an '* OK' still # here message (default: 10) # # the time between such a message is idle_timeout * idle_interval # seconds # # idle_interval = 10 # # If TLS is enabled, login before starttls is normally # not allowed. Use login_disabled=no to change this # login_disabled = yes # # Provide a CAPABILITY to override the default # #capability = IMAP4 IMAP4rev1 AUTH=LOGIN ACL RIGHTS=texk NAMESPACE CHILDREN SORT QUOTA THREAD=ORDEREDSUBJECT UNSELECT IDLE capability = IMAP4rev1 AUTH=LOGIN AUTH=CRAM-MD5 ACL RIGHTS=texk NAMESPACE CHILDREN SORT QUOTA THREAD=ORDEREDSUBJECT UNSELECT IDLE ID UIDPLUS WITHIN LOGINDISABLED CONDSTORE LITERAL+ ENABLE QRESYNC # max message size. You can specify the maximum message size # accepted by the IMAP daemon during APPEND commands. # # Supported formats: # decimal: 1000000 # octal: 03777777 # hex: 0xfffff # # max_message_size = [SIEVE] # # Port to bind to. # port = 2000 tls_port = [LDAP] port = 389 version = 3 hostname = ldap base_dn = ou=People,dc=mydomain,dc=com # # If your LDAP library supports ldap_initialize(), then you can use the # alternative LDAP server DSN like following. # # URI = ldap://127.0.0.1:389 # URI = ldapi://%2fvar%2frun%2fopenldap%2fldapi/ # # Leave blank for anonymous bind. # example: cn=admin,dc=mydomain,dc=com # bind_dn = # # Leave blank for anonymous bind. # bind_pw = scope = SubTree # AD users may want to set this to 'no' to disable # ldap referrals if you are seeing 'Operations errors' # in your logs # referrals = yes user_objectclass = top,account,dbmailUser forw_objectclass = top,account,dbmailForwardingAddress cn_string = uid field_passwd = userPassword field_uid = uid field_nid = uidNumber min_nid = 10000 max_nid = 15000 field_cid = gidNumber min_cid = 10000 max_cid = 15000 # a comma-separated list of attributes to match when searching # for users or forwards that match a delivery address. A match # on any of them is a hit. field_mail = mail # field that holds the mail-quota size for a user. field_quota = mailQuota # field that holds the forwarding address. field_fwdtarget = mailForwardingAddress # override the query string used to search for users # or forwards with a delivery address. # query_string = (mail=%s) [DELIVERY] # # Run Sieve scripts as messages are delivered. # SIEVE = yes # # Use 'user+mailbox@domain' format to deliver to a mailbox. # SUBADDRESS = yes # # Turn on/off the Sieve Vacation extension. # SIEVE_VACATION = yes # # Turn on/off the Sieve Notify extension # SIEVE_NOTIFY = yes # # Turn on/off additional Sieve debugging. # SIEVE_DEBUG = no # Use the auto_notify table to send email notifications. # AUTO_NOTIFY = no # # Use the auto_reply table to send away messages. # AUTO_REPLY = no # # Defaults to "NEW MAIL NOTIFICATION" # #AUTO_NOTIFY_SUBJECT = # # Defaults to POSTMASTER from the DBMAIL section. # #AUTO_NOTIFY_SENDER = # If you set this to 'yes' dbmail will check for duplicate # messages in the relevant mailbox during delivery using # the Message-ID header # suppress_duplicates = no # # Soft or hard bounce on over-quota delivery # quota_failure = hard # end of configuration file </file> % % Integrate DBMail in Postfix % # cp /etc/postfix/main.cf /etc/postfix/main.cf.bak # cp /etc/postfix/master.cf /etc/postfix/master.cf.bak % postfix minimal config myhostname = mail.example.tld mydomain = example.tld mynetworks = 127.0.0.1, 192.168.x.0/24 relay_domains = $mydestination %add /etc/postfix/master.cf if you want to use unix ports dbmail-lmtp unix - - n - - lmtp %Since DBMail uses different SQL structure from iRedMail, we have to disable some iRedMail special features in Postfix first. # postconf -e recipient_bcc_maps='' relay_domains='$mydestination' sender_bcc_maps='' transport_maps='' virtual_alias_maps='' %Update postfix setting to use DBMail transport (if set via unix port): %via unix# postconf -e virtual_transport='dbmail-lmtp:127.0.0.1:24' %via tcp # postconf -e virtual_transport='lmtp:127.0.0.1:24' % Update postfix setting in /etc/postfix/main.cf, remove reject_unknown_sender_domain in smtpd_recipient_restrictions setting. % Update postfix setting to lookup mail users and aliases from DBMail MySQL database: # postconf -e local_recipient_maps='' # postconf -e smtpd_sender_login_maps='proxy:mysql:/etc/postfix/dbmail_recipients.cf' # postconf -e virtual_mailbox_domains='proxy:mysql:/etc/postfix/dbmail_domains.cf' # postconf -e virtual_mailbox_maps='proxy:mysql:/etc/postfix/dbmail_mailboxes.cf' # postconf -e proxy_read_maps='$smtpd_sender_login_maps $virtual_mailbox_domains $virtual_mailbox_maps $local_recipient_maps' #nano /etc/postfix/dbmail_recipients.cf <file> hosts = 127.0.0.1 dbname = dbmail user = dbmail password = password_of_dbmail query = SELECT alias FROM dbmail_aliases WHERE alias='%s' LIMIT 1 </file> #nano /etc/postfix/dbmail_domains.cf <file> hosts = 127.0.0.1 dbname = dbmail user = dbmail password = password_of_dbmail query = SELECT DISTINCT 1 FROM dbmail_aliases WHERE SUBSTRING_INDEX(alias, '@', -1) = '%s' </file> #nano /etc/postfix/dbmail_mailboxes.cf <file> hosts = 127.0.0.1 dbname = dbmail user = dbmail password = password_of_dbmail query = SELECT 1 FROM dbmail_aliases WHERE alias='%s' LIMIT 1 </file> %//----------------// % see postfix configuration, for more %//----------------// % if you wish to configure authenticator via sql (sasl) you should install this cyrus-sasl-sql if not let it be and jump to sasl % sasl via sql #yum install cyrus-sasl cyrus-sasl-lib cyrus-sasl-sql cyrus-sasl-plain cyrus-sasl-md5 #nano /etc/sasl2/smtpd.conf <file> #if you want auth via db pwcheck_method: saslauthd mech_list: plain login auxprop_plugin: sql sql_engine: mysql sql_hostnames: [mysql ip] sql_user: dbmail sql_passwd: password_of_dbmail sql_database: DBMail sql_verbose: no sql_select: SELECT passwd FROM dbmail_users WHERE userid = '%u' </file> <file> #if you ant auth via saslauthd pwcheck_method: saslauthd mech_list: plain login </file> % sasl configuration %Update /etc/sysconfig/saslauthd (refer to manual page saslauthd(8) for more information): #nano /etc/sysconfig/saslauthd <add/mod> SOCKETDIR=/var/spool/postfix/var/run/saslauthd MECH=rimap #if your users have [email protected] FLAGS='-O 127.0.0.1 -r' #if your users have tt #FLAGS='-O 127.0.0.1 ' </add/mod> or (as nbeta) SOCKETDIR=/var/spool/postfix/var/run/saslauthd MECH=rimap FLAGS='-O 127.0.0.1' </add/mod> #copy init scripts to /etc/rc.d/init.d operations %create user %dbmail-users -p md5 -a [email protected] -w mypass -s [email protected] -f /etc/dbmail.conf % test sasl auth # testsaslauthd -u [email protected] -p test -f /var/run/saslauthd/mux

[CozC]

@rebootcode there is no clear documentation but analyzing the code I have came out with some results. Another user have asked, same how, the same question and I gave there some answers.

Please refer to Issue 17
Also you should take a look at a plugin developed for roundcube roundcube-dbmail

[langemeijer]

It is possible, but I wouldn't recommend it because:

• You would lock yourself into using dbmail as a mail store solution.
• You would have to re-implement the way dbmail stores mail into a database in your webmail client, possibly making mistakes that will affect dbmail's operation. Mind you that you not only will have to read from the database but also modify it. Marking mails as read, but possibly also store draft and sent mails into it, modify folders, delete and move mails etc.
• Writing a fully featured mail client is a lot of work. There is a lot of message parsing, address parsing and lots-and-lots of RFC reading involved. The storage backend of your mail application (being either imap or direct dbmail database connection) will not really be a big part of your endeavor.

Also, to be blunt, if you really need to ask 'Can I create and use my own Web Interface on the provided database to set up a Mailing application?' here, I wonder if you can. At least understand that there is probably not going to be someone here that will hold your hand while you shoot yourself in the foot.

That said: I know it has been done before, probably multiple times. I have written and modified PHP code that accesses the dbmail database directly myself, but I cannot share this with you for multiple reasons. Primarily because it only implements the basic stuff that this application needed, and the code is not designed to be expanded into a full email client storage backend. Secondary because it's not my own code.

Why do you prefer to create your own web interface? And if you do, why wouldn't you just use imap?

[rebootcode]

It is possible, but I wouldn't recommend it because:

• You would lock yourself into using dbmail as a mail store solution.

=> hmm, not sure, what does this mean, "mail store solution".

• You would have to re-implement the way dbmail stores mail into a database in your webmail client, possibly making mistakes that will affect dbmail's operation. Mind you that you not only will have to read from the database but also modify it. Marking mails as read, but possibly also store draft and sent mails into it, modify folders, delete and move mails etc.

=> Modify folders? is not dbmail, database only mail system? unlike system account mail thing?

• Writing a fully featured mail client is a lot of work. There is a lot of message parsing, address parsing and lots-and-lots of RFC reading involved. The storage backend of your mail application (being either imap or direct dbmail database connection) will not really be a big part of your endeavor.

=> Yes, I do understand and I am quite confident, these are not major task for me.

Also, to be blunt, if you really need to ask 'Can I create and use my own Web Interface on the provided database to set up a Mailing application?' here, I wonder if you can. At least understand that there is probably not going to be someone here that will hold your hand while you shoot yourself in the foot.

=> Yes, I understand - the only thing I need from dbmail is when the server receives mail, it stores them in the database and not in folder, plus, I don't have to create multiple system accounts or folder-based email account to create MAIL. I just want "user's email" created as database row and done.

Why do you prefer to create your own web interface? And if you do, why wouldn't you just use imap?

=> My Requirement is not in IMAP, I need a server to store mail-in database as soon as it receives and email accounts are created in the database and not as FOLDER or system user.

I need to create approx 300-1200 daily usable email accounts and this is only possible and scalable if we have DB based solution and not system user or email account.

Something like virtual mail that dbmail does.

[langemeijer]

Good. Build a database cluster to store the mail, setup at least two mail servers with dbmail and you'll have a very reliable mail server setup. dbmail is a very good solution for this. There is no good reason to access the mail database directly, as you can get mails through imap very efficiently. I'd slap a webserver cluster with roundcube on it and you're done.

Roundcube has 150 contributors, 11.500 commits to it's code and a very large user base. It would probably take you years to build something similar, and at least many months to build something that is barely usable.

Modify folders? is not dbmail, database only mail system? unlike system account mail thing?

Any modern email client supports mail folders. This is how you allow your users to organize their mail. All except gmail, they call it 'tags', but it essentially is the same.

Yes, I do understand and I am quite confident, these are not major task for me.

I just want "user's email" created as database row and done.

You have a very simplistic view on how mail should be stored in a database but your confidence is inspiring.

You haven't really answered my question though.

Why do you prefer to create your own web interface? And if you do decide to write your own, why wouldn't you just use imap to access the mail on the mail server?

[rebootcode]

Why do you prefer to create your own web interface? And if you do decide to write your own, why wouldn't you just use IMAP to access the mail on the mail server?

• It's my requirement to create my own mail interface for limited people around me.
• accessing mail is not the issue, I can do via IMAP, the problem is storing "incoming mail" on the server and creating a 300-1200 email account which is virtual and will work just like the user system in the database.

=> Do you have any better solution than "dbmail" for "storing incoming mail into database" and create virtual configurable email account stored in database?

[KenjiBrown]

I need to create approx 300-1200 daily usable email accounts and this is only
possible and scalable if we have DB based solution and not system user or email account.

Only three tables involved in creating a new user:
dbmail-users <--- The user account
dbmail-aliases <--- e-mail addresses that receive the messages and deliver them to the user
dbmail-mailboxes <--- At least INBOX should be created for a user to receive e-mail

[rebootcode]

Only three tables involved in creating a new user:
dbmail-users <--- The user account
dbmail-aliases <--- e-mail addresses that receive the messages and deliver them to the user
dbmail-mailboxes <--- At least INBOX should be created for a user to receive e-mail

Is there any video tutorial available? as I recently installed everything and created a user account, but none of them showed up on database - It was frustrating and I started installing postfix, dovecot, and binding with basis PHP script on mail received.

Is there any video tutorial to configure & install dbmail

[CozC]

salve rebootcode I can give a document I use it to install dbmail. It may help you but you should know a little bit of linux to use it.

[CozC]

I need to create approx 300-1200 daily usable email accounts and this is only
possible and scalable if we have DB based solution and not system user or email account.

Only three tables involved in creating a new user:
dbmail-users <--- The user account
dbmail-aliases <--- e-mail addresses that receive the messages and deliver them to the user
dbmail-mailboxes <--- At least INBOX should be created for a user to receive e-mail

After you have configured the postfix and dbmail and made them working together you can use the following sql script to create an user
`set @L_USER='[user account]';
set @L_PASSWD='[password]';
set @L_ALIAS='[[email protected]]';

insert into dbmail_users set userid=@L_USER, passwd=@L_PASSWD, encryption_type='';
insert into dbmail_aliases set alias=@L_ALIAS, deliver_to=(select user_idnr from dbmail_users where userid=@L_USER);
insert into dbmail_mailboxes set name='INBOX', owner_idnr=(select user_idnr from dbmail_users where userid=@L_USER);
insert into dbmail_mailboxes set name='Sent', owner_idnr=(select user_idnr from dbmail_users where userid=@L_USER);
insert into dbmail_mailboxes set name='Trash', owner_idnr=(select user_idnr from dbmail_users where userid=@L_USER);
insert into dbmail_mailboxes set name='Spam', owner_idnr=(select user_idnr from dbmail_users where userid=@L_USER);`

[rebootcode]

@CozC , Please share document - that will be helpful.

Thanks

[rebootcode]

salve rebootcode I can give a document I use it to install dbmail. It may help you but you should know a little bit of linux to use it.

Please share docs that can help in Installation in today's Ubuntu 18.04

[rebootcode]

@CozC - Thank you so much, this was very very helpful.

Is there any documentation for MySQL/MariaDB tables? cause tables are very confusing to find and search data, like, let's say, "If I want to search and list" all mail for "[email protected]", I have no idea, where these data are coming from.

Is there any documentation for database table, so that we understand how to fetch and process data?

[rebootcode]

if people are asking, that probably means - it is need of an hour. But Thanks for sharing the roundcube, I will have a look there.

Thank you so much for all this great help :)

[CozC]

It is not about explaining them it is about explaining the relation between them.
Please find attached the schema. The issue 17 does give you some SQL queries to extract information from the database.
Given your request I will do some short doc to create such doc. In the meantime please see attached the schema.