Managing users with SQL
Managing users is easily achieved by directly updating the DBMail tables. The following are the tables and columns that can be managed in SQL:
Admins can create front-ends so users can manage their own email aliases and change their own passwords. Managing accounts isn't covered by IMAP or POP standards, nevertheless it's preferable they manage that information themselves by changing their information in SQL or LDAP/Active Directory.
User input should be checked for example ensure a user's email alias is for their own domain and an alias is not already going to someone else.
It's straightforward to create a user frontend and although not here yet, creating one is on the DBMail roadmap.
Managing users in SQL
There are two sql tables relevant to user accounts, dbmail_users for authentication and authorisation and dbmail_aliases for email addresses aliases and forwards. Although it's possible to edit sieve scripts in sql, the nature of those scripts means it's preferable for a specialist app to help users create and then check before submission.
Managing sql users is through the table dbmail_users. The following data types are for PostgreSQL but MySQL and Oracle are the same. Some columns shouldn't normally be edited by users such as their login id and password type should be set according to your security policy.
| Column name | Data type | Editable | Description |
|---|---|---|---|
| user_idnr | INT8 default nextval('dbmail_user_idnr_seq') | No | Identity column |
| userid | VARCHAR(100) NOT NULL | Care | User's login name. Shouldn't normally be changed |
| passwd | VARCHAR(130) NOT NULL | Yes | Password |
| encryption_type | VARCHAR(20) DEFAULT '' NOT NULL | Care |
Type of encryption. Current values are: plaintext, plaintext-raw, crypt, crypt-raw, md5, md5-raw, md5sum, md5sum-raw, md5-hash, md5-hash-raw, md5-digest, md5-digest-raw, md5-base64, md5-base64-raw, md5base64, md5base64-raw, shadow, whirlpool, sha512, sha256, sha1, tiger. |
| client_idnr | INT8 DEFAULT '0' NOT NULL | Yes |
Optional client identifier used to group users Not used by DBMail |
| maxmail_size | INT8 DEFAULT '0' NOT NULL | Yes | Used to limit emails for a user, not enforced by DBMail |
| maxsieve_size | INT8 DEFAULT '0' NOT NULL | Yes | Used to limit sieve scripts for a user, not enforced by DBMail |
| curmail_size | INT8 DEFAULT '0' NOT NULL | No | Current email size |
| cursieve_size | INT8 DEFAULT '0' NOT NULL | No | Current sieve script size |
| spasswd | VARCHAR(130) DEFAULT '' NOT NULL | Yes |
Separate security fall-back password Not used by DBMail |
| saction | SMALLINT DEFAULT '0' NOT NULL | Yes |
Security action Not used by DBMail |
| active | SMALLINT DEFAULT '1' NOT NULL | Yes | Used to control if a user is allowed to login |
| last_login | TIMESTAMP DEFAULT '1979-11-03 22:05:58' not null | No | Automatically updated when a user logs in |
Email Aliases
DBMail looks up an email in dbmail_aliases and delivers it to one or more accounts. Those accounts can be within DBMail and will be the user_idnr or forwarded to an external email address.
| Column name | Data type | Editable | Description |
|---|---|---|---|
| alias_idnr | INT8 DEFAULT nextval('dbmail_alias_idnr_seq') | No | Identity column |
| alias | VARCHAR(100) NOT NULL | Yes | Email address |
| deliver_to | VARCHAR(250) NOT NULL | Yes | Recipient. DBMail users use the user_idnr or an email address for forwarding. |