Change a Postfix User Password in MySQL

Submitted by Tom Thorp on Monday, September 9, 2019 - 17:21
Modified on Tuesday, September 10, 2019 - 15:52
Postfix logo
To update user accounts on Postfix that are connected via MySQL, log into your MySQL database using an account with at least Select,Insert,Update,Delete privileges to the Postfix database. From there, find the username and password by searching the mailbox table. 
 
Below is an example :
MariaDB [postfix]> select username,password from mailbox limit 1;                                                                                                                   
+--------------------------------+------------------------------------+
| username                       | password                           |
+--------------------------------+------------------------------------+
| contact@example.com            | $1$1.sCG/pH$u9EMA7EHZbfB4WlSvfyO01 |
+--------------------------------+------------------------------------+
1 row in set (0.001 sec)
If the password is in hashed format and it begins with '$1$', then the password is stored in MD5 format. This is preferable from a security standpoint, as passwords will not be transmitted in plaintext across the internet. If the passwords are in plaintext format, then I would highly suggest changing the password format accordingly.
 
To change an account's password, you first have to generate a new MD5 hash. From the command line, do the following :
[fedora@ns ~]$ echo "newpassword" | openssl passwd -1 -stdin     
$1$ZazQAMtb$Z8/Yo/TAc40TDJ6mzvpd7/
[fedora@ns ~]$
where '-1' generates "newpassword" in MD5 format, and '-stdin' reads passwords from stdin.
 
Copy the MD5 hash, then execute the following command with MySQL. 
USE postfix
UPDATE mailbox 
  SET password="$1$ZazQAMtb$Z8/Yo/TAc40TDJ6mzvpd7/" 
  WHERE username="contact@example.com";
Once executed, the end-user will notice the change immediately upon the next access to their mailbox. 
 
 

About the author

Tom Thorp
Tom Thorp is an IT Consultant living in Miami on Queensland's Gold Coast. With over 30+ years working in the IT industry, Tom's experience is a broad canvas. The IT services Tom provides to his clients, includes :
 
Website development and hosting
Database Administration
Server Administration (Windows, Linux, Apple)
PABX Hosting and Administration
Helpdesk Support (end-user & technical).
 
 
Categories
PostfixDovecotMySQLEmailOpenSSL

Leave a Comment