1

Topic: sql force change password

iRedMail version (check /etc/iredmail-release)
==> 0.9.9
- Deployed with iRedMail Easy or the downloadable installer?
==>downloadable installer?
- Linux/BSD distribution name and version
==>  Debian Linux 9
- Store mail accounts in which backend (LDAP/MySQL/PGSQL)
==> MySQL
- Web server (Apache or Nginx)
==> Nginx
- Manage mail accounts with iRedAdmin-Pro?
==> Yes.
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.

Hi,

After we enable sql_force_change_password plugin we force mail user to change password in 90 days it is working correctly, but we have facing some difficulties while excluding some of domains which do not require this feature.

Excluded domains users also get notification email for changing their password.

changes are done in below mention file
/opt/iredapd/settings.py

Excluded domains are in below format with comma separated and single quotes

CHANGE_PASSWORD_NEVER_EXPIRE_USERS = ['example.com', 'domain.com']

Please note we have excluded 150 domains.

Thanks
Sunil

----

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

2

Re: sql force change password

If it doesn’t work, please turn on debug mode in iRedAPD and reproduce the issue again, extract related log from /var/log/iredapd/iredapd.log and paste here for troubleshooting.

3

Re: sql force change password

ZhangHuangbin wrote:

If it doesn’t work, please turn on debug mode in iRedAPD and reproduce the issue again, extract related log from /var/log/iredapd/iredapd.log and paste here for troubleshooting.

Hi,

following are the logs as requested

################################################

Aug  1 18:08:16 ns1 iredapd [policy] request=smtpd_access_policy
Aug  1 18:08:16 ns1 iredapd [policy] protocol_state=RCPT
Aug  1 18:08:16 ns1 iredapd [policy] protocol_name=ESMTP
Aug  1 18:08:16 ns1 iredapd [policy] client_address=x.x.x.x
Aug  1 18:08:16 ns1 iredapd [policy] client_name=x.x.*.x
Aug  1 18:08:16 ns1 iredapd [policy] client_port=29755
Aug  1 18:08:16 ns1 iredapd [policy] reverse_client_name=x.x.*.x
Aug  1 18:08:16 ns1 iredapd [policy] helo_name=SUNILPC
Aug  1 18:08:16 ns1 iredapd [policy] sender=user@sender.name
Aug  1 18:08:16 ns1 iredapd [policy] recipient=user@receiver.name
Aug  1 18:08:16 ns1 iredapd [policy] recipient_count=0
Aug  1 18:08:16 ns1 iredapd [policy] queue_id=
Aug  1 18:08:16 ns1 iredapd [policy] instance=b954.5f256238.7aed4.0
Aug  1 18:08:16 ns1 iredapd [policy] size=0
Aug  1 18:08:16 ns1 iredapd [policy] etrn_domain=
Aug  1 18:08:16 ns1 iredapd [policy] stress=
Aug  1 18:08:16 ns1 iredapd [policy] sasl_method=LOGIN
Aug  1 18:08:16 ns1 iredapd [policy] sasl_username=user@sender.name
Aug  1 18:08:16 ns1 iredapd [policy] sasl_sender=
Aug  1 18:08:16 ns1 iredapd [policy] ccert_subject=
Aug  1 18:08:16 ns1 iredapd [policy] ccert_issuer=
Aug  1 18:08:16 ns1 iredapd [policy] ccert_fingerprint=
Aug  1 18:08:16 ns1 iredapd [policy] ccert_pubkey_fingerprint=
Aug  1 18:08:16 ns1 iredapd [policy] encryption_protocol=TLSv1
Aug  1 18:08:16 ns1 iredapd [policy] encryption_cipher=ECDHE-RSA-AES256-SHA
Aug  1 18:08:16 ns1 iredapd [policy] encryption_keysize=256
Aug  1 18:08:16 ns1 iredapd [policy] policy_context=
Aug  1 18:08:16 ns1 iredapd --> Apply plugin: reject_null_sender
Aug  1 18:08:16 ns1 iredapd <-- Result: DUNNO
Aug  1 18:08:16 ns1 iredapd --> Apply plugin: wblist_rdns
Aug  1 18:08:16 ns1 iredapd Found SASL username, bypass rDNS check for outbound.
Aug  1 18:08:16 ns1 iredapd <-- Result: DUNNO
Aug  1 18:08:16 ns1 iredapd --> Apply plugin: reject_sender_login_mismatch
Aug  1 18:08:16 ns1 iredapd Sender: user@sender.name, SASL username: user@sender.name
Aug  1 18:08:16 ns1 iredapd SKIP: sender == sasl username.
Aug  1 18:08:16 ns1 iredapd <-- Result: DUNNO
Aug  1 18:08:16 ns1 iredapd --> Apply plugin: greylisting
Aug  1 18:08:16 ns1 iredapd Found SASL username, bypass greylisting for outbound email.
Aug  1 18:08:16 ns1 iredapd <-- Result: DUNNO
Aug  1 18:08:16 ns1 iredapd --> Apply plugin: throttle
Aug  1 18:08:16 ns1 iredapd Found sasl_username, consider this sender as an internal sender.
Aug  1 18:08:16 ns1 iredapd Check sender throttling.
Aug  1 18:08:16 ns1 iredapd [SQL] query target domain of given alias domain (sender.name): #012SELECT alias_domain.target_domain#012               FROM alias_domain, domain#012              WHERE domain.active=1#012                    AND domain.domain=alias_domain.target_domain#012                    AND alias_domain.alias_domain='sender.name'#012              LIMIT 1
Aug  1 18:08:16 ns1 iredapd [SQL] query result: None
Aug  1 18:08:16 ns1 iredapd [SQL] Query throttle setting: #012        SELECT id, account, priority, period, max_msgs, max_quota, msg_size#012          FROM throttle#012         WHERE kind='outbound' AND account IN ('x.x.x.x', '@ip', 'user@sender.name', '@sender.name', '@.', '@.sender.name', '@.com', 'x.x.x.*', 'x.x.*.x')#012         ORDER BY priority DESC
Aug  1 18:08:16 ns1 iredapd [SQL] Query result: []
Aug  1 18:08:16 ns1 iredapd No sender throttle setting.
Aug  1 18:08:16 ns1 iredapd Bypass recipient throttling (found sasl_username).
Aug  1 18:08:16 ns1 iredapd <-- Result: DUNNO
Aug  1 18:08:16 ns1 iredapd --> Apply plugin: sql_alias_access_policy
Aug  1 18:08:16 ns1 iredapd [SQL] query access policy: #012SELECT accesspolicy#012               FROM alias#012              WHERE address='user@receiver.name'#012              LIMIT 1
Aug  1 18:08:16 ns1 iredapd [SQL] query result: None
Aug  1 18:08:16 ns1 iredapd [SQL] query target domain of given alias domain (receiver.name): #012SELECT alias_domain.target_domain#012               FROM alias_domain, domain#012              WHERE domain.active=1#012                    AND domain.domain=alias_domain.target_domain#012                    AND alias_domain.alias_domain='receiver.name'#012              LIMIT 1
Aug  1 18:08:16 ns1 iredapd [SQL] query result: None
Aug  1 18:08:16 ns1 iredapd Recipient domain is not an alias domain.
Aug  1 18:08:16 ns1 iredapd <-- Result: DUNNO Recipient is not a mail alias account or no access policy
Aug  1 18:08:16 ns1 iredapd --> Apply plugin: amavisd_wblist
Aug  1 18:08:16 ns1 iredapd [SQL] query target domain of given alias domain (sender.name): #012SELECT alias_domain.target_domain#012               FROM alias_domain, domain#012              WHERE domain.active=1#012                    AND domain.domain=alias_domain.target_domain#012                    AND alias_domain.alias_domain='sender.name'#012              LIMIT 1
Aug  1 18:08:16 ns1 iredapd [SQL] query result: None
Aug  1 18:08:16 ns1 iredapd [SQL] query target domain of given alias domain (receiver.name): #012SELECT alias_domain.target_domain#012               FROM alias_domain, domain#012              WHERE domain.active=1#012                    AND domain.domain=alias_domain.target_domain#012                    AND alias_domain.alias_domain='receiver.name'#012              LIMIT 1
Aug  1 18:08:16 ns1 iredapd [SQL] query result: None
Aug  1 18:08:16 ns1 iredapd Possible policy senders: ['user@sender.name', '@sender.name', '@.', '@.sender.name', '@.com', 'x.x.x.x', 'x.x.x.*', 'x.x.*.x']
Aug  1 18:08:16 ns1 iredapd Possible policy recipients: ['user@receiver.name', '@receiver.name', '@.', '@.receiver.name', '@.com']
Aug  1 18:08:16 ns1 iredapd [SQL] query local domain (sender.name): #012SELECT domain#012                   FROM domain#012                  WHERE domain='sender.name' AND active=1 #012                  LIMIT 1
Aug  1 18:08:16 ns1 iredapd SQL query result: (u'sender.name',)
Aug  1 18:08:16 ns1 iredapd Apply wblist for outbound message.
Aug  1 18:08:16 ns1 iredapd [SQL] Query local addresses: #012SELECT id, email#012               FROM users#012              WHERE email IN ('user@sender.name', '@sender.name', '@.', '@.sender.name', '@.com', 'x.x.x.x', 'x.x.x.*', 'x.x.*.x')#012           ORDER BY priority DESC
Aug  1 18:08:16 ns1 iredapd Local addresses (in `users`): [(1L, '@.')]
Aug  1 18:08:16 ns1 iredapd [SQL] Query external addresses: #012SELECT id, email#012               FROM mailaddr#012              WHERE email IN ('user@receiver.name', '@receiver.name', '@.', '@.receiver.name', '@.com')#012           ORDER BY priority DESC
Aug  1 18:08:16 ns1 iredapd Addresses (in `mailaddr`): [(170L, 'user@receiver.name')]
Aug  1 18:08:16 ns1 iredapd [SQL] Query CIDR network: #012SELECT id, email#012               FROM mailaddr#012              WHERE email LIKE '111.%%'#012           ORDER BY priority DESC
Aug  1 18:08:16 ns1 iredapd [SQL] Query outbound wblist: #012SELECT rid, sid, wb#012               FROM outbound_wblist#012              WHERE sid IN (1) AND rid IN (170)
Aug  1 18:08:16 ns1 iredapd No wblist found.
Aug  1 18:08:16 ns1 iredapd [SQL] query local domain (receiver.name): #012SELECT domain#012                   FROM domain#012                  WHERE domain='receiver.name' AND active=1 #012                  LIMIT 1
Aug  1 18:08:16 ns1 iredapd SQL query result: (u'receiver.name',)
Aug  1 18:08:16 ns1 iredapd Apply wblist for inbound message.
Aug  1 18:08:16 ns1 iredapd [SQL] Query local addresses: #012SELECT id, email#012               FROM users#012              WHERE email IN ('user@receiver.name', '@receiver.name', '@.', '@.receiver.name', '@.com')#012           ORDER BY priority DESC
Aug  1 18:08:16 ns1 iredapd Local addresses (in `users`): [(3L, 'user@receiver.name'), (1L, '@.')]
Aug  1 18:08:16 ns1 iredapd [SQL] Query external addresses: #012SELECT id, email#012               FROM mailaddr#012              WHERE email IN ('user@sender.name', '@sender.name', '@.', '@.sender.name', '@.com', 'x.x.x.x', 'x.x.x.*', 'x.x.*.x')#012           ORDER BY priority DESC
Aug  1 18:08:16 ns1 iredapd No record found in SQL database.
Aug  1 18:08:16 ns1 iredapd No valid sender id or recipient id.
Aug  1 18:08:16 ns1 iredapd <-- Result: DUNNO
Aug  1 18:08:16 ns1 iredapd --> Apply plugin: sql_force_change_password
Aug  1 18:08:16 ns1 iredapd SQL to get mailbox.passwordlastchange of sender (user@sender.name): SELECT passwordlastchange FROM mailbox WHERE username='user@sender.name' LIMIT 1
Aug  1 18:08:16 ns1 iredapd Returned SQL Record: (datetime.datetime(2020, 1, 30, 15, 58, 17),)
Aug  1 18:08:16 ns1 iredapd Date of password last change: 2020-01-30 15:58:17
Aug  1 18:08:16 ns1 iredapd Sender didn't change password in last 1 days.
Aug  1 18:08:16 ns1 iredapd <-- Result: REJECT Your Password expired or never changed As per password policy, please change your password in webmail before sending email or contact Administrator
Aug  1 18:08:16 ns1 iredapd Session ended.
Aug  1 18:08:16 ns1 iredapd [x.x.x.x] RCPT, user@sender.name => user@receiver.name, REJECT Your Password expired or never changed As per password policy, please change your password in webmail before sending email or contact Administrator [sasl_username=user@sender.name, sender=user@sender.name, client_name=x.x.*.x, reverse_client_name=x.x.*.x, helo=SUNILPC, encryption_protocol=TLSv1, encryption_cipher=ECDHE-RSA-AES256-SHA, server_port=, process_time=0.0141s]
Aug  1 18:08:16 ns1 iredapd [SQL] Insert into smtp_sessions: #012        INSERT INTO smtp_sessions (#012            time, time_num,#012            action, reason, instance,#012            client_address, client_name, reverse_client_name, helo_name,#012            encryption_protocol, encryption_cipher,#012            server_address, server_port,#012            sender, sender_domain,#012            sasl_username, sasl_domain,#012            recipient, recipient_domain)#012        VALUES (#012            '2020-08-01 12:38:16', 1596285496,#012            'REJECT', 'Your Password expired or never changed As per password policy, please change your password in webmail before sending email or contact Administrator', 'b954.5f256238.7aed4.0',#012            'x.x.x.x', 'x.x.*.x', 'x.x.*.x', 'SUNILPC',#012            'TLSv1', 'ECDHE-RSA-AES256-SHA',#012            '', '',#012            'user@sender.name', 'sender.name',#012            'user@sender.name', 'sender.name',#012            'user@receiver.name', 'receiver.name')

################################################


Thanks
Sunil

4

Re: sql force change password

RajeshM wrote:

Aug  1 18:08:16 ns1 iredapd Sender didn't change password in last 1 days.
Aug  1 18:08:16 ns1 iredapd [x.x.x.x] RCPT, user@sender.name => user@receiver.name, REJECT Your Password expired or never changed As per password policy, ...

As you can see, iRedAPD correctly rejects the smtp session.

5 (edited by RajeshM 2020-08-03 13:13:13)

Re: sql force change password

ZhangHuangbin wrote:
RajeshM wrote:

Aug  1 18:08:16 ns1 iredapd Sender didn't change password in last 1 days.
Aug  1 18:08:16 ns1 iredapd [x.x.x.x] RCPT, user@sender.name => user@receiver.name, REJECT Your Password expired or never changed As per password policy, ...

As you can see, iRedAPD correctly rejects the smtp session.

Hi,

We know that it is rejecting but problem is that it is rejected to those domain or user which has been excluded from this policy.

sender.name has been excluded in iredAPD.


Thanks,
Sunil

6

Re: sql force change password

RajeshM wrote:

Aug  1 18:08:16 ns1 iredapd [policy] sasl_method=LOGIN
Aug  1 18:08:16 ns1 iredapd [policy] sasl_username=user@sender.name

Check iRedAPD source code:
https://github.com/iredmail/iRedAPD/blo … ord.py#L43

If sender performed smtp authentication, and its email address or domain name is listed in CHANGE_PASSWORD_NEVER_EXPIRE_USERS, it will be bypassed.

So the question is: Did you list email address "user@sender.name" (the real one) or domain "sender.name" in parameter "CHANGE_PASSWORD_NEVER_EXPIRE_USERS"?

7 (edited by RajeshM 2020-08-05 22:01:39)

Re: sql force change password

ZhangHuangbin wrote:
RajeshM wrote:

Aug  1 18:08:16 ns1 iredapd [policy] sasl_method=LOGIN
Aug  1 18:08:16 ns1 iredapd [policy] sasl_username=user@sender.name

Check iRedAPD source code:
https://github.com/iredmail/iRedAPD/blo … ord.py#L43

If sender performed smtp authentication, and its email address or domain name is listed in CHANGE_PASSWORD_NEVER_EXPIRE_USERS, it will be bypassed.

So the question is: Did you list email address "user@sender.name" (the real one) or domain "sender.name" in parameter "CHANGE_PASSWORD_NEVER_EXPIRE_USERS"?

Hi,

issue resolved by putting comma at the end and then close bracket like below

CHANGE_PASSWORD_NEVER_EXPIRE_USERS = ['example.com', 'domain.com',]

now it is working correctly.


Thanks
Sunil