[DBMA Online Help | README]

Managing Users with DbMail Administrator

DBMA is a GUI written in PERL for Administering ALL VERSIONS of DbMail


Managing Users

DBMA V2.x uses SQL commands and queries to work exclusively on the database. That means that DBMA is LAN-distributable and can run on any intranet web server with network access to your DbMail database (PostgreSQL or MySQL). DO NOT ALLOW UNFETTERED WEB 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; a few configuration tweaks.

No Stone Tablets

DBMA provides multiple ways of doing things and every admin has their own style. Each user of DBMA must set their own policies and style for their own environment. 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 dislose the content of that message to any person nor may you interrupt or tamper with that message in any manner.

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.

Connecting

Once you are 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.

Useful Information

Make a note of how many users there are. In the example below there are 567 users. There are 1137 aliases. Lets say your current assignment is to add 22 new users each with an address at two domains for Department X which is comprised of persons recently installed in your building as a result of a company merger. When you are finished adding users, there will be 589 users and 1181 aliases. If there are only 587 users, you left out an account, etc. There is nothing trivial about any of the large volume of the data DBMA presents. It is very useful for quickly spotting problems and assisting you in performing your administration.

 

DBMA V2.0.4 (mysql) on library.mobrien.com Fri Aug 27 14:13:01 2004
Successfully connected to dbmail on demo.mobrien.net
User Group Group
Users Aliases Forwards Special Global
Add User Add Aliases  Add Forward Add Auto Notify List all users all groups
Delete User Delete Aliases Delete Forward Delete Auto Notify List All Aliases
Email a User List Aliases List All Forwards List Auto Notifications Logins Last hrs

Configurations Encrypt Tool Display: 100 500 Clear. Refresh stats
dbmail running on demo.mobrien.net:
Version: 4.0.20-log
User Table Details
Number of users:  567
Number of aliases =  1137
Configured Auto Replies =  0
Configured Auto Notifications =  11
Recent logins =  7
Mail Table Details
Mailboxes =  85
Messages =  0
Physical Messages =  0
Message Blocks =  0
Status and connections to dbmail.

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 etcetras. 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.

click the mailbox icon in the user account window to list all mail in that box


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_idnr's (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.

GroupID's Common Practice

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

You will find that DbMail developers are going to claim Group "0". 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".

Down the road, your own system team may have an internal-system special user to add to this group.

Can we set aside groups 1 and 2 for local admin use? That would include mandatory and useful network and systems administration addresses plus common, never accessed generic business accounts. 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 GroupID Structure:

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

The easiest way to do this with DBMA is to 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.

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...

Adding Users and Aliases  - Configuration Defaults

  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.

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. Just type the users name and DBMA will create the account. 

1 - You can set the default mailbox size 
2 - You can set the default domain 
3 - You can set your default Group ID (client_idnr). (Some Admins only ever use one main group.)
4 - DBMA can automatically create the first alias using the username and the default domain 
5 - You can ask DBMA to generate the password 
6 - DBMA will notify the user of the password, mailbox quota and username. 

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

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"


MD5 = $1$s0yfoqOn\$i6Nr5nPSAhuqxbk.h.EJn/ In this MD5 password string, the characters between the 2nd and 3rd "\$" are the 8-chars (maximum) of 'Salt' used to create the RSA password which follows the 3rd "\$".
md5sum = 9cd234f150c500d196fbad63d2877bb2 or md5 hash is a one-way hash algorithm defined by RFC1321. On the command line type "md5 -s encrypthelp" for identical 128 bit digital signature of the password string.
crypt = MBQF2l2BTiTbM or UNIX crypt is based on a DES algorithm with a standard 2-char "salt". The first two chars define the salt which perturbs the algorithm in one of 4096 different ways.

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.

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 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.

The password DBMA auto-gen feature is a good way of doing plain text passwords if 'clear text' is your preference because DBMA generates gibberish passwords which look like mush to a sniffer as compared to typical user passwords.

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. NET::SMTP 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.

You can send mail to a bare username in your local system if your MTA is configured to pass it to DbMail.

User Names and the MTA Unix-Style versus Alias addresses

This section considers some "best practices" and aims at getting you thinking about "future-proofing" your mail system setup. This may be controversial but as Bob Dylan said, "These times are a'changin'".

Some DbMail users using SQL features of their MTA (i.e.: Postfix) to point their MTA at authorized recipients in the dbmail_aliases table. In Postfix for example, "local_recipient_maps" would look something like this:

 "mysql:/etc/postfix/sql-recipients.cf"

where "sql-recipients.cf" would look something like this:

user = <username>
password = <password>
host = <dbhost>
dbname = <dbname>
table = aliases
select_field = alias
where_field = alias

Would this indicate that the choice of aliases over usernames for a recipient table is because not all aliases actually have an account?

In the "main.cf" notes of the current "official" release of Postfix, this is OK. It will work; but obsolete in the new development branch of Postfix. Spammers have caught on to this flaw and forward looking thinkers are making some changes.

"The local_recipient_maps parameter specifies optional lookup tables with all names or addresses of users that are local with respect to $mydestination, $inet_interfaces or $proxy_interfaces."

In future releases of Postfix, this is not OK.

In the new Postfix configuration notes for the V2.2 developmental branch, " The local_recipient_maps parameter specifies optional lookup tables with all names (not addresses) of users that are local with respect to $mydestination and $inet_interfaces. If this parameter is defined, then the SMTP server will reject mail for unknown local users." 

In fact, try it, it will not work and Postfix will reject all mail.

NOTE: On release of 2.2, this has changed. 

What you see here is "future proofing"

"sql-recipients.cf" Should be, in this instance:

user = <username>
password = <password>
host = <dbhost>
dbname = <dbname>
table = dbmail_users
select_field = userid
where_field = userid

Stay Future Proof and Create Accounts for All Users

This is a decison you may or may not face. The object here is to show you an alternative method of  recipient mapping -- do we have this user? 

The future of MTA's could point to authentication on the basis of first_part user names only. A number of factors prevail, nontheleast of which may be your desire to implement key pair sender-authenticion regime. 

Every user must have their own account. Itcan't be just an alias. 

That is by far, a more extensible approach but initially requires a little more work.

Where you may now be pointing your MTA at the aliases table, you find it necesary to point to the first_part (left of the @) in the users.userid column. What are the ramifications?

It's not that hard. 

If webmaster@ has an alias of web@, an account for web should exist. Web doesn't need to receive mail, but the account must exist. Aliases like info@, sales@ may be directed to a real person name, but when that real person leaves the company, gets promoted or goes on vacation, info@, sales@ need to be redirected to someone else. They can even have encrypted auto-generated passwords which no one knows; their mail being stored only in the mailboxes of real people.

Each of these 'system' accounts would need to have their own user accounts in order to do that correctly and easily. If for example, you needed to know "who's handling info@" unless "info" has its own account, you would theoretically need to search through every single account and GroupID until you found "info@". Well, DBMA provides a tool for accomplishing this quickly, because there are likely many users who do not follow the best practices of creating a user account for all users. 

It's a matter of what works best; or in the future, what will break completely.

 Several RFC's require that mail be accepted for certain accounts. Don't make these orphaned aliases. Create user accounts within their appropriate system account GroupID.

In short, create accounts for all users. If you have info@, sales@, marketing@, then create accounts for them. Have set their alias to real people; something you can quickly change as personnel change.

More about redirecting mail for pseudo accounts to real humans.

The easiest way to do this with DBMA is to 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.

The day Harry moves on to bigger and better things and you need to move "info" over to "Bob Jones", open the "info" account and you will see the alias pointed to Harry's userID. Delete that alias. Next, open the account for "Bob Jones" and create "[email protected]" in the "Bob Jones" account. Bob Jones will now get all the "info" mail.

It's easy. It's foolproof.