Postgres Rule to invalidate user passwords every 60 days

Question

Hi all, I am having some trouble invalidating user passwords every 60 days on my postgres database:

CREATE RULE user_expiration AS ON UPDATE TO users DO INSTEAD UPDATE user SET user_expires = user_expires + '60'

This will work every time the user changes their password, however it also works every time any update is issued on the records. How can I ensure that it only updates on the change password event?

Answer 1

A trigger is a better solution. As of version 9.0 it has per column triggers, triggers that only fire when a certain column has been changed. In older versions you have to do the check inside the function, compare OLD.password and NEW.password before you deside to update the user_expires column.