Managing Users with DBMailAdministrator

Double Check Security and Versioning before We Start

DBMA V2.x (as opposed to V1.x) uses SQL commands and queries to work exclusively on the database connected via TCP. That means that DBMA is LAN-deployable and can run on any intranet web server with network access to your DBMail RDBMS (PostgreSQL or MySQL).

Do not allow unfettered access to this tool!

Before getting into the topic of User Management it would be wise to upgrade to the latest version. It's a reasonably painless process done with an upgrade script. You simply go to your "dbmailadministrator/" directory as root or as the user:group of your httpd and run "perl update.pl". Obviously if you have just downloaded and installed the programme you will not need to update at this time.

No Stone Tablets

1. DBMA provides multiple ways of doing things.
2. Every admin has their own style.
3. Each user of DBMA must set their own policies and style for their own environment. 
4. What is written here is for guiding purposes only without any warranty nor responsibility implied.

Privacy Warning

In the event you observe the mail contents addressed to a real person's (not referring to 'webmaster', 'abuse', 'sales', etc.), you may not disclose the content of that message to any person nor may you interrupt or tamper with that message in any manner.

My Mail System

Once connected to your database, DBMA will tell you all about it. In both PostgreSQL and MySQL configurations, the first window is all business. It tells you if and to what SQL server it is connected and then gives you the specifics about how your SQL server is running and what is happening with your DBMail database. As you make changes you will see these details change accordingly.

Searching and Reading Mail

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 :

• finding and undeleting a critical message a user inadvertently 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.

Checking Mailbox Content

Part of the job maintaining your DBMail system will be making certain that mail marked for deletion is getting deleted when your crontab dbmail-utils run. DBMA will help you do that as well as maintain a close eye on mail distribution. In each user account window are listed the users mail boxes. Clicking the mailbox icon brings up a technically detailed list of all mail in descending order displaying physmessage_id, message_id, internal_date, unique_id, rfcsize, blockSize etceteras. Below is what you will see. You can also access messages making certain they are being stored intact by clicking the email icon on the left side of each listing. This is a good method for checking headers as well.

Group ID's

The term GroupID is indigenous to DBMA and should not be confused with Unix-Group or other IMAP daemons use of the term "Mail Group"

The GroupID is a structural organization within the database. Users are organized into client_idnr's. You can have as many GroupID's as you wish. Thousands, if you like; whatever your database setup can handle.

The DBMail structure in this regard is unique. You will see that it offers excellent feature possibilities and is more "future-proof" than most alternatives.

You might notice that several terms of reference in DBMA (GUI names) differ slightly from the table and field names used in the DBMail database. 

The Mail Administrator is seldom a database engineer and likely wants to work in his/her own terms of reference.

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 as well as the 'machine room' mail team. 

"GroupID" refers to the 'dbmail_users' field 'client_idnr'. 

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. 

DBMail does not in any way discriminate client_idnrs (GroupID). This is for local use. There are things like a patch to enable group quotas with PostgreSQL and other ideas will come along. You may have your own database schema change ideas. That is beyond the scope of this article.

Some Common Practice for GroupID's 

You will find that DBMail developers are have claimed Group "0" for the system. That's that. In it will be internal use members like the "Delivery Agent or  Anybody for ACL "public" folders. 

DBMA restricts (not denies) access to GroupID "0" just in case a "newbie" accidentally deletes the delivery agent. That would make a mess, wouldn't it? You can still list users in Group "0" by selecting "List any and all" but there are limitations imposed against open access to GroupID "0".

Let's consider some common practice guidelines for "GroupID's' 

Let's set aside groups 1 and 2 for local admin use? That would include mandatory (abuse, privacy, postmaster etceteras) and useful network and systems administration addresses (webmaster, noc, dns etceteras.)  plus common, never accessed generic business accounts (info, brochures, sales, etceteras). They could be accounts that never actually receive mail (forwarding everything to real people) and which have horrid passwords known to no one, encrypted with an MD5 hash using a eight character random salt key.

Example structure sorts 'client_idnr' numbers into Administrative and User "Groups".

Your permanent pseudo accounts, organized by groups, can store mail in the accounts of real humans and can be easily changed from time to time to point to different persons as they come and go.

Create the account with a wacky encrypted password say for "info" WITHOUT an alias. If Harry Smith is responsible for answering queries to "info" open Harry's account and add an alias "[email protected]". All mail for "info" will go to Harry. If Harry gets a better job and is replaced by "Peter", move the alias to "Peter".

Example GroupID structure:

GroupID 0 is Reserved for The MAIL DELIVERY SYSTEM

• secret black magic users owned by the internal system

GroupID 1 Systems and RFC-required access points

• user: postmaster,  password: **encrypted-DBMAutogen-unknown**
  alias: James_elected_postmaster@your_domain.tld ==> user_idnr of James
• user: abuse,  password: **encrypted-DBMAautogen-unknown**
  alias: [email protected]_domain.tld ==> user_idnr of human_name
• user: dns,  password: **encrypted-DBMAautogen-unknown**
  alias: [email protected]_domain.tld ==> user_idnr of human_name
• user: privacy,  password: **encrypted-DBMAautogen-unknown**
  alias: [email protected]_domain.tld ==> user_idnr of human_name
• webmaster, hostmaster, noc, spf, etceteras....

GroupID 2 Business Generics

• user: info,  password: **encrypted-DBMAautogen-unknown**
  alias: Sue_PR_Mgr@your_domain.tld ==> user_idnr of human_name
•  user: sales,  password: **encrypted-DBMAautogen-unknown**
  alias: Bob_salesmanager@your_domain.tld ==> user_idnr of human_name
• user: contracts password: **encrypted-DBMAautogen-unknown**
  alias: Sam_the_Lawyer@your_domain.tld ==> user_idnr of human_name
• help, support, accounts, receivables, eteceteras....

GroupID 3

• Real People eteceteras...

Speed Up The Process of Adding Users and Aliases

1. A user name is the local part of [email protected].
2. The whole address, local part at domain part, is entered as an alias.
3. Some very small systems use the entire address as a username.

DBMA's options (select "configurations" and press "Go" button) configuration allows an automated method for cutting down the number of key presses when entering a large number of new users. 

With auto presets, you type the user's name and DBMA creates the entire account. 

These options can make adding new users a snap.

If you complete all of the optional configs, creating a new user is a matter of typing the name. You can populate your database with a large number of users in very short order. 

Auto-Password

DBMA will refuse to encrypt an auto-generated password until you have told the user what is their password. Well, that's isn't quite true. DBMA is not triggered to release a constraint once an email has been sent. DBMA will instead refuse to encrypt an auto-generated password from the initial create-new-user regime. Your next window will show the created password and give you a button to press to notify the user of the password. Remember that you may need to send this message to an alternate address like "[email protected]". If you don't have such an account from the user, and the user is local, use your browser's PRINT command, [Cntrl P] maybe, and print the mail message, stick it into an envelope and mail it or give it to the user. There isn't much point setting a new password and sending the message about that to a locked mailbox. 

(You can alter the encryption type and password of auto-gens in the user modify form. By forcing plaintext initially, DBMA saves you some annoyance if you forget to jot down the password because it's still visible. You can always change it to an encrypted form once the user is notified and has logged in at least once. You will know this from checking your recent logins with DBMA.)

Password Encryption: md5, md5sum or crypt

Yes. You really should, especially if yours is a corporate, ISP or enterprise system. DBMail uses "crypt", "md5" and "md5sum" in addition to clear text (plain). What follows is a set of encrypted passwords using the string example "encrypthelp"

DBMail uses "crypt", "md5" and "md5sum" in addition to clear text (plain).

 Encryption in the database makes no difference to the user's email client as it will be passing the 'secret' in plaintext. There is something ironic about that, but best practices say don't allow access points to your database through unencrypted user (data) accounts. Under most circumstances you are only putting at risk the user's mail, but don't be too sure that some messed-up-brained attacker couldn't plague your database server with a little bit of access. The choice is yours to encrypt or not to encrypt. You will find wide ranging views on the subject.

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.

Plain Passwords in the Open can Cause at Least Embarrassment

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 'secret' password (fifi~loves^me2) on your giant flat screen along with those of the VP Finance, VP Legal, VP Human Resources, Payroll etc. Like Donald Trump says, "You're fired!".

Storing encrypted passwords is the right choice unless you are using the database as a source of data for user authentication. An example would be cyrus-sasl2 with an sql plugin and authentication queries to the dbmail_users table. In that case plain text is usuallly a must.

Using Transport Layer Security is another good idea. That means that transmission between the user and the server is encrypted on secure ports as follows: SMTPS port 465, POP3S port  995 and IMAPS port  995. The favoured approach with DBMail to accomplish this is with "Stunnel".

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. Consider the number of users who might be connecting to the internet on a WiFi (wireless) connection, many of which offer no security at all.

Autogenerating Unique Passwords

DBMA will generate gibberish passwords. It's a good way of doing plain text passwords if 'clear text' is your preference because the passwords which look like mush to a sniffer as compared to typical user passwords using phone numbers, birthdays, middle names and so on. The administrator is able to see plain text passwords in DBMA at all times and can easily make changes as desired by the user. This is similar to the way Hotmail, Yahoo mail and others work.

Sending Mail with DBMA

It is a nice touch to send a quick note to a user who has requested a Mailbox quota increase or password change. PERL's NET::SMTP module is what DBMA uses to send mail to your users. 

Their contact address can be outside your operation (necessary in the case of a password change).  

The NET::SMTP module is part of Graham Barr's libnet. The configurable SMTP_ServerName in DBMA should point to your DBMail MTA and that is what NET::SMTP will use. You can send mail within the same system to a bare username "i.e.: mike" if your MTA is configured to pass it to DBMail.