Dovecot User Password Expiration Notifications - UPDATED (4/12/2015)

Sep 29, 2014 21:00 dovecot

I recently found myself wishing for a way to implement password expiration for my IMAP users. I also wanted to preempt password expiration by notifying users to change their passwords before their expiration. To date, it doesn’t appear that there’s any feature which allows Dovecot to handle this type situation out of the box; at least not when using a DB backend for authentication. I figured it would be fun to create my own solution. What follows is a walk-through of my implementation.

This post assumes that you’re using PostgreSQL as your DB backend for user authentication. The same logic applies whether you’re using MySQL, MariaDB or something else. I cannot speak however, to the portability of the SQL statements among other database backends.

Overview

We’re pretty much just adding a few columns to the database so that we can use them in a SQL query that will return users with passwords that are near expiration. For starters, we need a column to hold the expiration date (and time) of the password. Secondly, we’ll need columns, one for each time interval for which we want to notify our users, to keep track of which users have been notified.

Items to Address

  • We need a cron job to run at some set interval to check for users with passwords near expiration. Cron will also be in charge sending out the emails to the users it returns from the database.

  • Unless our “user_query” accounts for expired passwords, users with “expired” passwords will be able to login without issue. Therefore, we’ll need to modify our “user_query” to account for an expired password parameter.

  • Users tend to ignore password expiration reminders so we’ll probably want to remind our users at several intervals that they need to change their password. However, we want to make sure that we’re not spamming the users with these notifications. This means that we’ll need a way to keep track of whether or not we’ve sent the user a notification.

Implementation

DB Changes

Before making any modifications to the database, please, please, PLEASE do a full backup. Or better still, do some testing on a non-production DB. One incorrectly typed ALTER or UPDATE statement on a production DB could leave you in a lot of pain. YOU’VE BEEN WARNED…

  • Let’s add a column to the table where our users exist. Let’s assume it’s called “users”. You can call your new column whatever you want but it’s probably best to choose something like “pw_expires_on”:
ALTER TABLE users ADD COLUMN pw_expires_on TIMESTAMP WITH TIME ZONE; # Add column, set data type
ALTER TABLE users ALTER COLUMN pw_expires_on SET DEFAULT now(); # We need a default here
UPDATE users SET pw_expires_on = now() + interval '90 days'; # Expire everyone 90 days from now
ALTER TABLE users ALTER COLUMN pw_expires_on SET NOT NULL; # No null values here
  • Now, let’s add some columns to the users table to keep track of the notices we’re sending out. I want to keep track of notices at 30, 14 and 7 days respectively. You don’t have to go with these intervals, they can be whatever you’d like. Feel free to add more points on the scale such 30, 14, 7, 2 and 1 day(s). Feel free to call your new columns whatever you want. I’ll name mine according to the day-interval that the notice represents:
ALTER TABLE users ADD COLUMN thirty boolean; # Add column, set data type
ALTER TABLE users ALTER COLUMN thirty SET DEFAULT false; # Assume 30 day notice not sent
UPDATE users SET thirty = false; # Give all current users a value
ALTER TABLE users ALTER COLUMN thirty SET NOT NULL; # No null values here either

ALTER TABLE users ADD COLUMN fourteen boolean; # Add column, set data type
ALTER TABLE users ALTER COLUMN fourteen SET DEFAULT false; # Assume 14 day notice not sent
UPDATE users SET fourteen = false; # Give all current users a value
ALTER TABLE users ALTER COLUMN fourteen SET NOT NULL; # No null values here either

ALTER TABLE users ADD COLUMN seven boolean; # Add column, set data type
ALTER TABLE users ALTER COLUMN seven SET DEFAULT false; # Assume 7 day notice not sent
UPDATE users SET seven = false; # Give all current users a value
ALTER TABLE users ALTER COLUMN seven SET NOT NULL; # No null values here either

PSQL

PSQL is the command line client for connecting to the PostgreSQL database. If you were able to make the necessary modifications in the previous section, chances are, you already know how to use it. On the off chance that you used some other utility such as pgadmin all you really need to know is the following command:

$ psql -U <db-user> -h <server-ip> -d <database-name>

When you run this command, you’ll be asked to enter the user’s password for connecting to the database. Since we want be able to use this command in a script, we’ll need a way to run the command without being prompted for a password. Using a .pgpass file to store our connection information will enable us to do just that:

$ cat << EOF > ~/.pgpass
# hostname:port:database:username:password
*:*:*:*:your-password-here
EOF

I’ve used wildcards for all the fields except for password. Feel free to fill in the other info you’d like.

This file has to be 0600 or else psql will ignore it:

$ chmod 0600 ~/.pgpass

Test that psql is picking up your settings:

$ psql -U <db-user> --no-password -h <server-ip> -d <database-name> -c "\\d+"

This command should return a description of the selected database without prompting for a password.

The Script

I have to warn you: things could get ugly in your shell script. This is definitely one of those occasions where you should be leveraging the power of Python or Ruby.

So what should our script do exactly?

  1. Look for users whose passwords expire in 30 days
  2. Look for users whose passwords expire in 14 days
  3. Look for users whose passwords expire in 7 days
  4. Update the corresponding column in the DB indicating that a notification has been sent

We’ll actually want to check for passwords that expire between our interval minus one day to account for the time of day that a password expires. Our queries should something like the following:

SELECT email,pw_expires_on::date FROM <database-name> WHERE pw_expires_on > now() + interval '29 days' AND pw_expires_on < interval '30 days' AND thirty = false;
  • Returns email and expiration date for all users whose passwords expired between 29 and 30 days from now and who’ve not yet received a 30 day notice.
SELECT email,pw_expires_on::date FROM <database-name> WHERE pw_expires_on > now() + interval '13 days' AND pw_expires_on < interval '14 days' AND fourteen = false;
  • Returns email and expiration date for all users whose passwords expired between 13 and 14 days from now and who’ve not yet received a 14 day notice.
SELECT email,pw_expires_on::date FROM <database-name> WHERE pw_expires_on > now() + interval '6 days' AND pw_expires_on < interval '7 days' AND seven = false;
  • Returns email and expiration date for all users whose passwords expired between 6 and 7 days and who’ve not yet received a 7 day notice.

Depending on the information you’d like to provide in your notifications, you may want to return more values than just the user’s email and password expiration date. For example, if you’d like your notification to address users by their name, you may want to return the name field as well (assuming that you have a name field in your users database).

The last statement we’ll need is a one that will update the respective notification columns in the database after a notification is sent. If we just sent out the 30 day notification to a user, we want to make sure this user isn’t returned upon the next script iteration for 30 day notices:

UPDATE users SET thirty = true WHERE email = 'someuser@email.com' AND thirty = false; # For 30 day notices

UPDATE users SET fourteen = true WHERE email = 'someuser@email.com' AND fourteen = false; # For 14 day notices

UPDATE users SET seven = true WHERE email = 'someuser@email.com' AND seven = false; # For 7 day notices

In our script we’ll iterate over the users returned from the first 3 queries. While iterating over those users, we’ll pass in the user’s email address to the UPDATE statement above which will update the notification status for that user.

Our script should look something like this:

#!/bin/bash
QUERY30DAYS="SELECT email,pw_expires_on::date FROM <database-name> WHERE pw_expires_on > now() + interval '29 days' AND pw_expires_on < interval '30 days' AND thirty = false;"

QUERY14DAYS="SELECT email,pw_expires_on::date FROM <database-name> WHERE pw_expires_on > now() + interval '13 days' AND pw_expires_on < interval '14 days' AND fourteen = false;"

QUERY7DAYS="SELECT email,pw_expires_on::date FROM <database-name> WHERE pw_expires_on > now() + interval '6 days' AND pw_expires_on < interval '7 days' AND seven = false;"

function notifyThirtyDays() {
  psql -q -t --no-align --field-separator ' ' -U <db-user> --no-password -h <server-ip> -d <database-name> -c "$QUERY30DAYS" | while read -a RESULT; do
  echo -e "Dear User, \n Your password will expire on ${RESULT[1]}" | mail -s "Password Notication" -r noreply@yourdomain.com ${RESULT[0]}
  echo "UPDATE users SET thirty = true WHERE email = '${RESULT[0]}';" | psql -U <db-user> --no-password -h <server-ip> -d <database-name>
}

function notifyFourteenDays() {
  psql -q -t --no-align --field-separator ' ' -U <db-user> --no-password -h <server-ip> -d <database-name> -c "$QUERY14DAYS" | while read -a RESULT; do
  echo -e "Dear User, \n Your password will expire on ${RESULT[1]}" | mail -s "Password Notication" -r noreply@yourdomain.com ${RESULT[0]}
  echo "UPDATE users SET fourteen = true WHERE email = '${RESULT[0]}';" | psql -U <db-user> --no-password -h <server-ip> -d <database-name>
}

function notifySevenDays() {
  psql -q -t --no-align --field-separator ' ' -U <db-user> --no-password -h <server-ip> -d <database-name> -c "$QUERY7DAYS" | while read -a RESULT; do
  echo -e "Dear User, \n Your password will expire on ${RESULT[1]}" | mail -s "Password Notication" -r noreply@yourdomain.com ${RESULT[0]}
  echo "UPDATE users SET seven = true WHERE email = '${RESULT[0]}';" | psql -U <db-user> --no-password -h <server-ip> -d <database-name>
}

notifyThirtyDays # Execute the function for 30 day notices
notifyFourteenDays # Execute the function for 14 day notices
notifySevenDays # Execute the function for  7 day notices

Don’t worry if you don’t understand all of the arguments passed to PSQL in the functions. For the most part, they are only used to give us nicer formatting of the rows returned from the database. Also, this code has a lot of cruft: we’ve repeated ourselves a ton which is bad. I plan to add prettier version of it on Github when I’ve got a moment. As I mentioned previously, we could say the same thing that this Bash script does in far fewer lines of Python or Ruby.

Dovecot

We now need to update the Dovecot user_query to look for our new pw_expires_on field.

For example, if our current user_query is:

SELECT email as user, password FROM users WHERE email = '%u';

It should be changed to:

SELECT email as user, password FROM users WHERE email = '%u' AND pw_expires_on > now();

This now requires that the password be correct AND that it not be expired.

Be certain to restart Dovecot after making this change.

Make sure that you don’t add this to the password_query. If you do, when the user’s password expires, Dovecot will stop delivering mail to their mailbox. In fact, Postfix will inform the remote host that there’s no existing user with whatever email address the sender addressed their message to.

A Note on Password Changes

Make sure that whatever mechanism your users are invoking to update their passwords includes a provision for setting the sent notification columns (thirty, fourteen and seven in my case) back to false after changing the password. If you forget to do this, users will stop getting the notications in the future.