DBMA - Installation and Configuration
For all DbMail versions
program is distributed in the hope it will be useful, but WITHOUT
ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. This package
and updates are available at http://www.dbma.ca/|
FastTrack Installation and Configuration Help for Unix
Three Quick Steps for the Experienced
(FreeBSD model, change namespaces to suit your OS)
1) Apply Package
Unpack and install DBMA
# cd /usr/local/www
# tar xvf DBMA_SQL_V2.tar
# chown -R www:www /usr/local/www/dbmailadministrator
# chmod 777 /usr/local/www/dbmailadministrator
# chmod 755 /usr/local/www/dbmailadministrator/*.cgi
2) Update your Perl Modules
# perl -MCPAN -e shell
CPAN> install Digest::MD5
CPAN> install Bundle::libnet
CPAN> install DBI
CPAN> install DBD::Pg or DBD::mysql # whichever you are using, MySQL or PostGreSQL
3) Configure your Apache Server
<Directory "/usr/local/www/dbmailadministrator"> # change for your system
Options ExecCGI MultiViews
Allow from all
AuthUserFile /usr/local/www/dbmailadministrator/.htpasswd # change for your system
AuthName "Administration Only. Enter username and password."
# apachectl graceful
Configure to your database setup
Set options to your liking
UpGrading to a more Recent Version of DBMA?
Update Script Preserves all Configs
The "update.pl" script included in the tarball will fetch and install
an upgrade of DBMA while preserving all existing configurations.
This script uses "wget" so you must have "wget" installed.
simply type perl update.pl in the dbmailadministrator/ directory.
Untar the tarball (tar -xvf) from one level above your dbmailadministrator folder, overwriting everything; or to
preserve your configuration:
from inside "../dbmailadministrator" do this:
> cp *.DB ../
> cd ../
> tar -xvf DBMA_SQL_V2.tar
> mv *.DB dbmailadministrator/
> chown -R HTTPD_user:HTTPD_Group ~/dbmailadministrator/*
New at this? Just Starting?
Platform notes. DBMA was built on Unix servers and tested
and tuned on several Windows variants. You can fully administer
your DbMail IMAP / POP3 system using DBMA on a Win32 host
(NT4Server, W2kServer, WinServer-2003).
On either Unix or Windows, DBMA will start up instantly and
fly up a configuration window or just login into the database if
the default configurations match yours.
If you are running Windows Server 2003 with IIS6, make sure
that your CGI is turned on for the dbmailadministrator directory.
Use PerlIS.dll instead of the executeable (Perl.exe) and you
won't even need to alter the shebang line.
Make certain that the *.DB files are writeable by the account
the server runs as (IUSR_hostname). The same applies to Unix
systems and their corresponding HTTPD user and group.
Be certain your PERL is up to speed by installing the needed
modules. Configure everything correctly and there's nothing you
can't do from a Windows box that can be done from a Unix host.
DBMA has been tested on Solaris 8/9, Linux, FreeBSD,
NT4-IIS4/Apache, NT5-Apache, NT5.2-IIS6/Apache and then some.
Web Server and Perl Modules
You need a Web Server (i.e.: Apache) and PERL installed with the DBI and appropriate DBD modules. (See below how to use
"perl dbi-test.pl" to learn what DBI capabilities are installed on your system. Also some tutorial is available on how to modify your Apache configuration to run a secure DBMailAdministrator at DBMA Security
The required PERL modules are:
• DBI and the DBD module for your database (i.e.:
DBD::mysql or DBD::Pg)
• NET::SMTP (part of CPAN's Bundle::libnet)
For the best help available on the subject of installing PERL
modules, visit CPAN
Installing PERL Modules
Install modules with CPAN like this:
> perl -MCPAN -e shell
> install Bundle::libnet
> install DBI
> install DBD::Pg or DBD::mysql
Mechanics of Installing DBMailAdministrator (DBMA)
1) Untar (tar xvf DBMA_SQL_V2.tar) the tarball
from HTTPD document root or from your cgi-bin, as you
download tarball to /usr/local/www/
tar -xvf DBMA_SQL_V2.tar
2) chown files to your HTTPD user:group and chmod 755
i.e.: chown -R www:www ~/dbmailadministrator/*
3) With the sole exception of 'hard-coding' a
mode (see below), all configuration is done from the
4) Point your browser to /dbmailadministrator/
5) i) If the programme fails to start, check that the
line (#!/usr/bin/perl) in DBMA.cgi points to your PERL and
you have the necesary modules installed. (See top of
ii) chown files to your HTTPD user:group and chmod 755
(if you are very lucky the tarball's defaults may be
6) make certain all files and directories are readable and
by the httpd user:group
8) This package includes a maillog search feature for
shooting. dbma_logsearch.cgi doesn't need configuration
as it will look for your logs or you can simply enter
location from the user interface. Make certain your
is readable by the HTTPD's user:group. Remember that
are rotated by crontab so permissions may need
9) DO NOT make this tool available to Public web access!
- DBMA v2.5.x + is built within the rigid standards of Mod_Perl 2.02+
and runs fastest in ModPerl (any), but is entirely suited to run as a
Common Gateway Interface (CGI) on any platform.
- Cache your SQL queries for improved performance of your mail
system. The example below assumes you have only a modest amount
of memory available. Please read your SQL RDBMS manual for
- MySQL my.cnf (my.ini on win32) example for query cache.
DBMA MTA Administration
DBMA provides a number of tools for configuring and managing
your Mail Transfer Agent from the GUI. This is accomplished using
special tables added to your mail database which, once your MTA
is correctly configured, replaces flat text files and provides a
range of advantages nonetheleast of which is speed and
- Destination Domains: are the domains for which your MTA will
- Mail Transports, per-domain: configures unique routings to
spamD, AV, DbMail SMTP or other filtering and policy
- Access control provides a method of rapidly creating primary
white and black lists or for fine-tuning what specialized
configuration you may already have.
- Read MTA HELP for more information.
|Consider with care the
location of your DBMA installation. It must have only
authenticated use by only the assigned administrators. Read the
security notes for some
|DBMA configuration is
now done from the Configuration Window which should open the
first time you point your browser to
In the event you observe the mail contents addressed to a real
person's (not referring to 'webmaster', 'abuse', 'sales', etc.),
you may not dislose the content of that message to any person.
Period. Throughout the world it is most often a criminal offence
to do the contrary. DbMailAdministrator (DBMA) provides methods
for searching mail headers and message blocks for administrative
troubleshooting only. DBMA's message block displays are not
content-friendly but ASCII-forced with emphasis on routing and
embedded header-fields tracing the internet 'hops' the message
travels. An example of appropriate use would be finding and
UNdeleting a critical message a user inadvertantly deleted;
troubleshooting headers when a delivery breaks; evaluating
anti-spam/virus software deployments; providing help-phone
assistance to users in the identification or removal of SPAM,
'message-jams', viruses; and so on. Use the search headers option
wherever possible for speed, eficiency and privacy. Anything you
read, keep it to yourself. If something you feel is alarming
comes into view, refer the message ID to an immediate supervisor
or consult with your employer's privacy/security authority.
Some Pointers for Using DBMA
Please change your default password (dbmail:dbmail and
dbma:dbma for Win32) as soon as possible. See security for more details. Password
protection should be enabled in your HTTP Daemon configuration
file (i.e.: httpd.conf).
DbMail Administrator (DBMA) GUI interface tunes into the
amazing flexibility of the DbMail.org mail system concept. You'll
love this, as managing large mail account groups becomes a treat.
DbMail is so flexible it might be possible to write a book about
all the permeatations one can create. The DBMA administrative GUI
opens the door. What follows is no book. If you have any
questions about what is or is not covered below, please ask them.
DBMA enables an Administrator to 'hard-code' DBMA to
restrict administrative access to a single DbMail "Group"
This is ideal for Help Desk or Level One Support situations
where you want personnel to perform mail user management on a
select group of DbMail accounts without providing full DbMail
database view or access.
You can also use DBMA (RESTRICTGroupID mode) on a end-to-end
secure and authenticated Web resource (authenticated SSL) with
which your external customers' Administrator or internal/remote
departments' Administrator could manage their own DbMail
accounts. In the case where there are only single known users of
the resource you could easily use a self-issued High Grade
Encryption (AES-256 256-bit) server certificate if you don't have
nor want the cost of a 'store-bought' Server Cert (i.e. Thawte,
VeriSign, SSL, etc.). (http://www.ssl.com/ now offers an
SSL128SCG2.5 single-domain Cert for under $100. USD).
Multiple instances of DBMA can be used to Administer multiple
"DbMail Groups/Clients" by designated personnel without any
overlap and with only a restricted view or access to a single
group (client_idnr) of mail accounts for each designated
To implement this mode, DBMA is first configured like normal
in the configurations GUI. Then "root" opens the main script
(DBMA.cgi) in a text editor; a comment hash mark is removed; and
the client_idnr number for the group to which DBMA will be
restricted is entered. (Detailed instructions are in the top of
the script.) At this point no further GUI access is available to
the configurations window nor global list/add/delete/etc.
functions. Administration rights exist only for the single group
you enable. The README in the tarball has further info.
- RESTRICTGroupID mode (see
http://dbma.mobrien.com/DBMA-FAQ.htm for full details)
- - removes access to all Global Functions
- - removes access to DBMA Configuration GUI
- - limits administration access to a single group
- - prevents duplicate user accounts across mail Groups
- - returns a notice and denies access when the group
- administrator's search returns a user outside his/her
In the (default) unrestricted configuration, DBMA
enables full access to your entire DbMail system database for
managing users, finding mail, etc. and is relatively
- Scalability. Like DbMail, DBMA is vastly scalable. Millions of users
can be accomodated depending on the scope of your database
servers (or cluster). You might notice some terms of reference in
DBMA (GUI names) differ slightly from the table and field names
used in the DbMail database. DBMA is a management tool as well as
a customer support tool. DBMA favours the use of 'friendly'
terminology which fits the most likely usage by front line Level
One Support people and by the 'machine room' mail team. "Group"
refers to the 'dbmail_users' field 'clientid', for example. In
the case of an ISP, each user is a client. You might consider
organizing your clients/users into geographic groups or net
segment groups or whatever you like, to keep the total number of
users broken up into manageable lots of up to 1000-1500 accounts
per group. With just 999 groups you can manage as many email
accounts as some countries have internet users. Both DbMail and
the DBMA GUI are highly scalable.
- By using simple "radio check buttons" and pressing
"Go" DBMA lets you add or delete users, aliases, forwards,
notifications, check mail limits, monitor IMAP mailboxes, and
more as well as show and modify single users or display a list of
all existing users in any group or all groups displaying all
information including current mail stats.
- Deleting the user removes the account as well as all
aliases and auto notifications for that user.Heed the
'Alert' popup asking "Are you sure you want to do this?" Once
deleted, all aliases, forwards, mailboxes and mail associated
with the deleted user are GONE for good.
- Message Management Problems with undeleted messages in
DbMail? With the preponderance of email clients and perhaps after
numerous rejigging of the DbMail database through various
upgrades, you may find that messages marked by a user for
deletion are not having their status reset. Look for changes in
the MAIN MENU statistics column: "Number of deletes pending" DBMA
(since V2.1.2) will accomplish this with a single check "Update
delete status" box and "Go".
- Find the orphaned messages DBMA is a good fixer.
This 'Update Delete Status function' runs a scan of the
database looking for three basic problems.
1. messages marked delete by the client but status is still
2. messages having no 'owner mailbox' and
3. messages unattached to a user.
These are three issues which DBMA users have emailed me about.
From DBMA user feedback I deduce this is sometimes caused by
mucking around in the datatbase with an editor like MySQLCC,
PHPMyAdmin or phpPgAdmin. There may also be issues with some
patched/migrated database schemas; and surely there's the
gremlins and quirks.
If DBMA finds some orphaned messages DBMA will, with gentle
grace, set their dbmail_messages.status to a mere 001 and their
deleted_flag to a certain "1" preparing them for the ultimacy of
the maintenance cleanup.
Look at the "Number of deletes pending:" column of the DBMA
statistics (Main menu) to observe any changes after doing "Update
Why status 001?
You are the real 'administrator' and must be afforded a chance
to review what you have done.
If you don't like '001', go nuts, repeat the action and they
will all be set to '003', the final bell. The next crontab
'dbmail-util -d' removes them from the database. (You may someday
benefit from this two-step flexibility.)
NOTE: The Update delete status function is not available to
"Group Administrators" under the RESTRICTGroupID constrict.
- Mail search, delete or undelete. Mail search is
available from any users mail box and the search will be
conducted within that Mailbox. Delete sets the status flag to 003
so it is wiped out on the next maintenance pass. All flags are
visible in any mail box so an erroneously marked (for delete) can
be spotted quickly. Individual mail can be undeleted or deleted,
all mail in any mail box can be deleted or undeleted.
- Remove separate aliases by User ID (even if the user
no longer exists) or by complete alias. Some orphaned rows may
exist in your database between dbmail-utils / dbmail-maintenance
crontab runs. More often than not this is an admin-user error.
Hush. DBMA can help you monitor and oust straggler aliases. Just
type the full alias and press "Delete Alias" and presto it's
gone. In the alternative you can type the UserID and press
"Delete All Aliases" and zap, they are all gone.
- Forwards including external address forwards like
email@example.com ==> firstname.lastname@example.org are
accomplished by checking 'Add Forwards' and pressing the "SQL
Tool" button from the Admin page. You will get another GUI window
which is self-explanatory on adding email internal and external
forwards. You can also list all the forwards and delete any which
need to go.
- I already have a "Bob" as user. Let's face it, public
email addresses uniquely identify each person on planet earth. As
a mail administrator or postmaster you already realize that folks
take their email address and their email seriously and
personally. Very much so. Keeping the mail running and folks
happy is your aim but you will encounter many heretofore
inconceivable complaints or requests from users. It's nice to
know you are ready for most anything.
So, the new user wants the name "bob" in his email address,
empatically. You already have a "bob" (or whatever the request
may be) at another virtual domain and this new user who wants to
be "bob" won't take no for an answer. No problem. Use DBMA with
"Add User" checked to open an "(Add) User" window. Type a uniqe
name like "boblastname" for the "User Name", enter the new Bob's
password and "Client (Group) ID" then enter the email address as
"email@example.com". Bob must login with account name
"boblastname" but his email address is
"firstname.lastname@example.org". All mail for
"email@example.com" will go to the mailbox of
"boblastname" and the other "Bob" is unscathed by all this.. You
can have as many "bob's @ different_domains.tld" as you
- Encryption. Many people will find that plain text
passwords are sufficient for email user accounts. If that is not
true for you, DBMA will encrypt your passwords compatible with
dbmail. Choose from md5-hash, md5sum or crypt.
- Auto Notify is accomplished by knowing the UserID
number of the user you want to cause a notification. Enter the
UserID number and the full email address of the mail box you want
a notification sent to. It will work like a charm.
- A simple way to add an alias is to call up the user in
a manner of your choosing and then use the "Modify..." button. In
the window which appears, without changing any of the main
entries, add an alias to the "alias" text window at the bottom
and press "Update". A similar window will popup again showing the
change. You can repeat the above over again to add more aliases
- Search for users can be accomplished using the email
address, the user name or the user ID number. Your most
unequivocal method uses the ID number.
- You can view all users in all groups by selecting
"List all users all group" and pressing the "Go!" button, but if
you have 100 groups each with a thousand users, you won't be
seeing the list for quite some time. To be more selective, in
the top row of functions, enter the group (ClientID) number and
press either the "Users" or "Aliases" buttons. An alphabetical
list will appear. Click the name of the user or alias you wish to
modify and a data page for that user will open. If it proves true
that this is the user you seek, press the "Modify User" button
and make your changes, updates or whatever.
- List Aliases GUI helps you manage scores of
information and functions; forwards or aliased accounts and
more. You can list every alias but if the numbers are high you
should limit the display to under 1500 users. The ideal method is
to list aliases by groups. Mail forwarders can also be listed
separately. Each alias listing has a link to a data window or a
search function. In the case of an external forward you obviously
will not find the alias as a user account but in the case where
you have firstname.lastname@example.org forwarded to
email@example.com, the two links will take you to
the User Data Window for the two corresponding user accounts. Did
you follow that? Each line has two links. The mail for x@x goes
to xx@xx. It is possible that the link on the right is an
external address. Clicking it will fetch an "account doesn't
exist" message. On the other hand, accounts like "noc", "dns",
"abuse", "privacy" etc are likely aliased to the addresses of
persons or titles. The DBMA Alias List GUI allows you to quickly
select which Account window you wish to open, the account with
mail forwarded (left link), or the account where the mail goes to
- Distributing your NewsLetter with forwarding. If you
are creating forward aliases like
"firstname.lastname@example.org" to be forwarded to each and
every user at "@ourdomain.tld" you might forget to actually
create the account "newsletterfromtheprez", but if you thought
"Flubber" had bouncing problems, watch what happens with this
scenario when things go wrong. In other words, if you are going
to create a "newsletter" alias for mail distribution to various
mail account groups, for each "newsletter" forwarder, create a
full user account for your "newsletter".
- To view the data for a single user, type the user's
name or ID number beside the "User Search " button and press that
button. From there you can modify or even delete the user.
Don't Allow Public Access To This Tool
DbMail is a very good package of programmes for storing and
retrieving mail in a MySQL or PostgreSQL database. DbMail
Administrator (DBMA) can help you administer your DbMail user
accounts with an HTML GUI you can work with from across the LAN
or around the globe (you should VPN or similarly secure connect
for the latter). Whatever secure environment you choose to do
your enterprise or network management within, using your own
privileged user and permission sets, would be a good place to try
DbMail Administrator (DBMA).
More About Password Encryption
Please read this.
(Note: Passwords must not be encrypted if you are using SASL/SQL for SMTP authentication.
With SASL/RIMAP, encrypted passwords are fine because they are decrypted for DBMail IMAP LOGIN. )
When you are creating or modifying a user account in DBMA you are
given the option of encrypting passwords. For obvious security
reasons, DBMA will not decrypt them. If a user forgets their
password and if you have elected to encrypt passwords in your
database, your only option is to issue a new password. This is a
'best practice' in enterprise, corporate or ISP scenarios.
Keep in mind that this discussion of encryption applies only to
the password communication between DbMail and its database and
how the password is actually stored in the database. The user's
handling of their password is in plain text.
Let's put this in perspective with a hypothetical anecdote.
You are sitting at your workstation as a Level One Support
person in a corporate environment. Open on your workstation
screen is DBMA displaying the Corporate Management group of
users: rows of executive mail accounts all with plain text
passwords in full view. The CEO walks by and sees his name and
password on your screen along with those of the VP Finance, VP
Legal, VP Human Resources, Payroll etc.
Like Donald Trump says, "You're fired!".
Storing passwords with encryption is the right choice for most
commercial and corporate environments.
Unless you are using one of the many options available for
authenticating encrypted user passwords across the internet; on
143 and 110, plain text login passwords are passed across the
internet. So also are email messages passed in plain text across
the internet. If someone wants to read your emails they certainly
don't need your POP3/IMAP login password. But any level of
unauthorized access to your database management system's
passwords is a potentially serious failure of your system's
All of this is fairly common practice. I understand that an SSL
version of DbMail is contemplated by some developers for later
consideration and possible release. Meanwhile, with great
success, many DbMail Admins are using "stunnel" (http://www.stunnel.org/) as an SSL
wrapper for DbMail..
The table which follows defines the four options you have in
DbMail for storing passwords: cleartext, an 'md5' (hash),
'md5sum', and the Unix 'crypt'. Remember these defining terms
should be entered in lower case. DBMA will encrypt your passwords
for you. Select from crypt, md5-hash or md5sum in DBMA. These are
stored in a manner understood by dbmail's various programmes and
the effect is seamless.
||whatever hash you enter|
||whatever hash you enter|
||whatever hash you enter|
dbmail_users.encryption_type = ''
dbmail_users.passwd = 'yourtest'
dbmail_users.encryption_type = 'crypt'
dbmail_users.passwd = 'sixG/7CU2FOtg'
dbmail_users.encryption_type = 'md5'
dbmail_users.passwd = '$1$rjN6/GVE$6rPnLX388iJ1Dt7J/LRPf.'
dbmail_users.encryption_type = 'md5sum'
dbmail_users.passwd = 'b22766fada4a17d0f1a67c258a1d93d7'
dbmail_users.encryption_type = 'crypt'
dbmail_users.passwd = 'whatever hash you enter'
dbmail_users.encryption_type = 'md5'
dbmail_users.passwd = 'whatever hash you enter'
dbmail_users.encryption_type = 'md5sum'
dbmail_users.passwd = 'whatever hash you enter.