DBMA - HELP with Useage and Installation
For all DbMail versions
This 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://library.mobrien.com/dbmailadministrator/|
DBMA Configuration For Win32 and Unix Admins, PostgreSQL or MySQL
UpGrading to a more Recent Version?
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/*
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.
You need a Web Server (i.e.: Apache)
and PERL installed with the PERL MD5, DBI and appropriate DBD modules.
(See below how to use "perl dbi-test.pl" to learn what DBI capabilities are installed on your system.
The required PERL modules are:
Digest::MD5 (normally part of modern PERL)
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
Install modules with CPAN like this:
> perl -MCPAN -e shell
> install Digest::MD5
> install Bundle::libnet
> install DBI
> install DBD::Pg or DBD::mysql
1) Untar (tar xvf DBMA_SQL_V2.tar) the tarball
from HTTPD document root or from your cgi-bin, as you wish.
download tarball to /usr/local/www/
tar -xvf DBMA_SQL_V2.tar
2) chown files to your HTTPD user:group and chmod 755 *.cgi
i.e.: chown -R www:www ~/dbmailadministrator/*
3) With the sole exception of 'hard-coding' a "RESTRICTGroupID"
mode (see below), all configuration is done from the GUI.
4) Point your browser to /dbmailadministrator/
5) i) If the programme fails to start, check that the shebang
line (#!c:/perl/bin/perl.exe) in DBMA.cgi points to your PERL and that
you have the necesary modules installed. (See top of README.)
ii) chown files to your HTTPD user:group and chmod 755 *.cgi
(if you are very lucky the tarball's defaults may be fine.)
6) make certain all files and directories are readable and writeable
by the httpd user:group
8) This package includes a maillog search feature for trouble
shooting. dbma_logsearch.cgi doesn't need configuration
as it will look for your logs or you can simply enter the
location from the user interface. Make certain your maillog
is readable by the HTTPD's user:group. Remember that Logs
are rotated by crontab so permissions may need updating.
9) DO NOT make this tool available to Public web access!
|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 ideas.|
|DBMA configuration is now done from
the Configuration Window which should open the first time you point your
browser to /dbmailadministrator/DBMA.cgi |
When something doesn't work right off the bat, we know it can be frustrating.
Feel better now. Your problem is not serious, particularly if you already have DbMail.org's IMAP and POP3 daemons up and running well.
If you follow the installation procedure in the DBMA README
which came with your DBMA package, DBMA will work instantly unless:
- You are locked out. (Please see security notes for Help with Passwords.
Meanwhile the default username is dbmail and the password is dbmail. Generate your own new password NOW. Use the following command adjusted to the
location of htpasswd on your system:
> /usr/local/bin/htpasswd -cb .htpasswd user secret)
- You made an error in your typing of config information. (Proof read again please and check that your config is correct.)
- Your host cannot resolve the SQL server name you specified. (Change to an IP number or fix your hosts and/or DNS zone files)
- SQL User Name rejected. Perhaps the limited priviledges user name and password you are using for your database has only localhost authority on the database server. (Change the user privs on the database to include the host you are running DBMA from or try a different user.)
Check your PERL
If you do not know which PERL modules are installed on your
system, this package contains a console tool to answer that question for you if
you are unable to start DBMA:
Open a shell to your DBMA installation and enter
the command "perl dbi-test.pl"
This tool will list in the console display all the database types which DBI can connect to. Look for yours which will be either "MySQL" or "Pg".
If you don't have the one you are looking for open "perl -MCPAN -e shell" and install DBD::mysql or install DBD::Pg. If you do not have the CPAN module installed or if you need more help using it, visit http://www.cpan.org/modules/INSTALL.html
If DBMA starts but cannot connect, it
will tell you most everything you need to know to get you connected and working on Administration of your DbMail
DBMA Troubleshooting Summary
1) Are you using PERL 5.6 or better with DBI and DBD installed for your database?
CGI::Carp (should be in your PERL already)
NET::SMTP (part of libnet which should be in your PERL)
Digest::MD5 (should be in your PERL already)
2) Is your web space script-enabled (i.e.: ExecCGI) (read DBMA_security.htm)?
3) Have you correctly configured DBMA with your Database details?
4) Have you correctly set the correct database type?
5) Have you correctly set the versioning for DbMail?
6) Do you have a database client installed?
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.
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" (client_idnr).
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 "Group/Client" Administrator.
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 assigned group
In the (default) unrestricted configuration, DBMA enables full access to your entire DbMail system database for managing users, finding mail, etc. and is relatively unchanged.
- 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
- 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
- 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 000;
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 Delete Status".
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
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 "firstname.lastname@example.org". Bob must login with
account name "boblastname" but his email address is "email@example.com".
All mail for "firstname.lastname@example.org" 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 like.
- 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 very
- 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 email@example.com
forwarded to firstname.lastname@example.org,
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 (right link).
- Distributing your NewsLetter with forwarding.
If you are creating forward aliases like "email@example.com" 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).
Each user listing is a link to the user's data page.
- In the above User List Window screenshot you would click the user link on
the left side to open a user data window.
- Select "Configurations" for a data page setting out configration
and technical specifics about your DBMA installation and your system's, PERL as
well as your database.
- Restrict the number of listings by using the radio check buttons in addition
to your function selection.
More About Password Encryption
Please read this.
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,
Like Donald Trump says, "You're fired!".
Storing encrypted passwords is the right choice.
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 security.
All of this is fairly common practice. I understand that an SSL
version of DbMail is contemplated by some developers for later consideration and
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.
|cleartext || "NULL" || yourtest|
|crypt || "crypt" || sixG/7CU2FOtg|
|md5-hash || "md5" || $1$rjN6/GVE$6rPnLX388iJ1Dt7J/LRPf.|
|md5-digest || "md5sum" || b22766fada4a17d0f1a67c258a1d93d7|
|crypt-raw || "crypt" || whatever hash you enter |
|md5-hash-raw || "md5" || whatever hash you enter|
|md5-digest-raw || "md5sum" || whatever hash you enter|