1 Topic by akliouev

  • akliouev
  • Member
  • Offline
  • Registered: 2017-08-21
  • Posts: 3

Topic: Updating mailbox.passwordlastchange automaticaly

==== Required information ====
- iRedMail version (check /etc/iredmail-release):
- Linux/BSD distribution name and version:
- Store mail accounts in which backend (LDAP/MySQL/PGSQL):
- Web server (Apache or Nginx):
- Manage mail accounts with iRedAdmin-Pro?
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
======== Required information ====
- iRedMail version (check /etc/iredmail-release):
- Linux/BSD distribution name and version:
- Store mail accounts in which backend (LDAP/MySQL/PGSQL):
- Web server (Apache or Nginx):
- Manage mail accounts with iRedAdmin-Pro?
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
======== Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.5-1
- Linux/BSD distribution name and version: Ubuntu
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? No
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Hi!

When using SQL the vmail.mailbox table contains a 'passwordlastchange' column, that keeps the last time the user's password was changed.
I've noticed that only iRedAdmin was actually updating this field and my Web frontend (SoGO) did not even have access to this column by default.

I created a trigger that seems to be working  and updating the 'passwordlastchange' field automatically when anything changes the 'password' field with an UPDATE statement to the vmail.mailbox table:

delimiter //
create TRIGGER PasswordChanged BEFORE UPDATE ON mailbox
FOR EACH ROW
BEGIN
  IF NEW.password <> OLD.password THEN
     SET NEW.passwordlastchange=NOW();
  END IF;
END;//
delimiter ;

Hope this is useful and can be incorporated in the the default database schema in an upcoming release
Regards,
Alex

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,163

Re: Updating mailbox.passwordlastchange automaticaly

Great. Any similar solution to get it working with PostgreSQL?

BTW, Both iRedAdmin (open source edition and iRedAdmin-Pro) and Roundcube will update this column when user change password. but we cannot custom the SQL command SOGO used to change password.

3 Reply by akliouev

  • akliouev
  • Member
  • Offline
  • Registered: 2017-08-21
  • Posts: 3

Re: Updating mailbox.passwordlastchange automaticaly

For Postgres it would be something like this:

\c vmail
CREATE OR REPLACE FUNCTION PasswordChanged () RETURNS trigger LANGUAGE plpgsql AS $function$
BEGIN
IF NEW.password <> OLD.password THEN
     NEW.passwordlastchange=NOW();
END IF ;
RETURN NEW;
END;
$function$

CREATE TRIGGER PasswordChanged BEFORE UPDATE ON mailbox
FOR EACH ROW EXECUTE PROCEDURE PasswordChanged();

I don't have a proper Postgress SQL sandbox so I'd appreciate if somebody would test that code first and verify that the backup script do save the triggers in Postgres ;-)

Implementing this in the default schema would allow proper maintanance of of fields even if some third-party script will change the table. I.e. I have a user activation script in python tht forces users to select a new password and does update the mailbox.password field. With the trigger the passwordlastchange will be updated automatically so I don''t have to worry about this in the python code.

Regards,
Alex

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,163

Re: Updating mailbox.passwordlastchange automaticaly

I will test your SQL trigger later (feel free to remind me if i didn't post any update here). Thanks very much for sharing.