Any error in SOGo log file (/var/log/sogo/sogo.log)? and Dovecot log files (/var/log/dovecot/*.log)?

The imap.log shows as below

From : sogo

May  2 15:44:25 mail dovecot: imap-login: Disconnected (auth failed, 1 attempts in 8 secs): user=<hari@itechservices.in>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured, session=<hvqS5eSHatp/AAAB>
May  2 15:44:25 mail dovecot: imap-login: Disconnected (auth failed, 1 attempts in 6 secs): user=<hari@itechservices.in>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured, session=<Sr2x5eSHcNp/AAAB>

From:Roundcube

May  2 16:01:50 mail dovecot: imap-login: Disconnected (auth failed, 1 attempts in 2 secs): user=<hari@itechservices.in>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured, session=<i8U/JOWH5uh/AAAB>

If i try to login from roundcube it shows error as login failed.
If users login from outlook with imap or pop3 account, the ip based restriction works fine.

Thank You

Yes, it is working but the restriction is not working. Users can login from anywhere using  sogo and roundcube in spite of ip restriction. .

Sorry for the delay . I will enable debug mode and post the log.

Thank you

dovecot log
---------------

Oct  8 13:29:13 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<HPoLi2GUCNd/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:29:13 mail dovecot: auth-worker(13607): sql(md@pro2col.in,127.0.0.1,<HPoLi2GUCNd/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:29:13 mail dovecot: auth: Debug: sql(md@pro2col.in,127.0.0.1,<HPoLi2GUCNd/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:29:13 mail dovecot: auth: sql(md@pro2col.in,127.0.0.1,<HPoLi2GUCNd/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:29:15 mail dovecot: auth: Debug: client passdb out: FAIL#0111#011user=md@pro2col.in
Oct  8 13:29:15 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<eoJui2GUXNd/AAAB>): query: SELECT mailbox.password, mailbox.allow_nets FROM mailbox,domain WHERE mailbox.username='md@pro2col.in' AND mailbox.`enableimapsecured`=1 AND mailbox.active=1 AND mailbox.domain=domain.domain AND domain.backupmx=0 AND domain.active=1
Oct  8 13:29:15 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<eoJui2GUXNd/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:29:15 mail dovecot: auth-worker(13607): sql(md@pro2col.in,127.0.0.1,<eoJui2GUXNd/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:29:15 mail dovecot: auth: Debug: sql(md@pro2col.in,127.0.0.1,<eoJui2GUXNd/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:29:15 mail dovecot: auth: sql(md@pro2col.in,127.0.0.1,<eoJui2GUXNd/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:29:17 mail dovecot: auth: Debug: client passdb out: FAIL#0111#011user=md@pro2col.in
Oct  8 13:29:17 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<VSyGi2GUdNd/AAAB>): query: SELECT mailbox.password, mailbox.allow_nets FROM mailbox,domain WHERE mailbox.username='md@pro2col.in' AND mailbox.`enableimapsecured`=1 AND mailbox.active=1 AND mailbox.domain=domain.domain AND domain.backupmx=0 AND domain.active=1
Oct  8 13:29:17 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<VSyGi2GUdNd/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:29:17 mail dovecot: auth-worker(13607): sql(md@pro2col.in,127.0.0.1,<VSyGi2GUdNd/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:29:17 mail dovecot: auth: Debug: sql(md@pro2col.in,127.0.0.1,<VSyGi2GUdNd/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:29:17 mail dovecot: auth: sql(md@pro2col.in,127.0.0.1,<VSyGi2GUdNd/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:29:19 mail dovecot: auth: Debug: client passdb out: FAIL#0111#011user=md@pro2col.in
Oct  8 13:38:41 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<HQQtrWGUdo5/AAAB>): query: SELECT mailbox.password, mailbox.allow_nets FROM mailbox,domain WHERE mailbox.username='md@pro2col.in' AND mailbox.`enableimapsecured`=1 AND mailbox.active=1 AND mailbox.domain=domain.domain AND domain.backupmx=0 AND domain.active=1
Oct  8 13:38:41 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<HQQtrWGUdo5/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:38:41 mail dovecot: auth-worker(13607): sql(md@pro2col.in,127.0.0.1,<HQQtrWGUdo5/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:38:41 mail dovecot: auth: Debug: sql(md@pro2col.in,127.0.0.1,<HQQtrWGUdo5/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:38:41 mail dovecot: auth: sql(md@pro2col.in,127.0.0.1,<HQQtrWGUdo5/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:38:43 mail dovecot: auth: Debug: client passdb out: FAIL#0111#011user=md@pro2col.in
Oct  8 13:38:47 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<uSFErWGUio5/AAAB>): query: SELECT mailbox.password, mailbox.allow_nets FROM mailbox,domain WHERE mailbox.username='md@pro2col.in' AND mailbox.`enableimapsecured`=1 AND mailbox.active=1 AND mailbox.domain=domain.domain AND domain.backupmx=0 AND domain.active=1
Oct  8 13:38:47 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<uSFErWGUio5/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:38:47 mail dovecot: auth-worker(13607): sql(md@pro2col.in,127.0.0.1,<uSFErWGUio5/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:38:47 mail dovecot: auth: Debug: sql(md@pro2col.in,127.0.0.1,<uSFErWGUio5/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:38:47 mail dovecot: auth: sql(md@pro2col.in,127.0.0.1,<uSFErWGUio5/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:38:49 mail dovecot: auth: Debug: client passdb out: FAIL#0111#011user=md@pro2col.in
Oct  8 13:38:49 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<vEWnrWGUFI9/AAAB>): query: SELECT mailbox.password, mailbox.allow_nets FROM mailbox,domain WHERE mailbox.username='md@pro2col.in' AND mailbox.`enableimapsecured`=1 AND mailbox.active=1 AND mailbox.domain=domain.domain AND domain.backupmx=0 AND domain.active=1
Oct  8 13:38:49 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<vEWnrWGUFI9/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:38:49 mail dovecot: auth-worker(13607): sql(md@pro2col.in,127.0.0.1,<vEWnrWGUFI9/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:38:49 mail dovecot: auth: Debug: sql(md@pro2col.in,127.0.0.1,<vEWnrWGUFI9/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:38:49 mail dovecot: auth: sql(md@pro2col.in,127.0.0.1,<vEWnrWGUFI9/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:38:51 mail dovecot: auth: Debug: client passdb out: FAIL#0111#011user=md@pro2col.in
Oct  8 13:38:51 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<fnS+rWGUMI9/AAAB>): query: SELECT mailbox.password, mailbox.allow_nets FROM mailbox,domain WHERE mailbox.username='md@pro2col.in' AND mailbox.`enableimapsecured`=1 AND mailbox.active=1 AND mailbox.domain=domain.domain AND domain.backupmx=0 AND domain.active=1
Oct  8 13:38:51 mail dovecot: auth-worker(13607): Debug: sql(md@pro2col.in,127.0.0.1,<fnS+rWGUMI9/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:38:51 mail dovecot: auth-worker(13607): sql(md@pro2col.in,127.0.0.1,<fnS+rWGUMI9/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:38:51 mail dovecot: auth: Debug: sql(md@pro2col.in,127.0.0.1,<fnS+rWGUMI9/AAAB>): allow_nets: Matching for network 27.5.145.153
Oct  8 13:38:51 mail dovecot: auth: sql(md@pro2col.in,127.0.0.1,<fnS+rWGUMI9/AAAB>): allow_nets check failed: IP 127.0.0.1 not in allowed networks
Oct  8 13:38:53 mail dovecot: auth: Debug: client passdb out: FAIL#0111#011user=md@pro2col.in

Thank You
Murali

If i remove 127.0.0.1 from allow_nets, it is not accessible even from allowed ip. If i add the ip 127.0.0.1 to the allow_nets then the roundcube is accessible from any ip. I need ip based login restriction from roundcube or sogo . I am posting the sql query below .

Below is without any restriction

MariaDB [vmail]> SELECT username, allow_nets FROM mailbox WHERE username="md@pro2col.in" LIMIT 1;
+---------------+------------+
| username      | allow_nets |
+---------------+------------+
| md@pro2col.in | NULL       |
+---------------+------------+
1 row in set (0.00 sec)

Below is with ip based restriction which does not login even from allow_nets ip

MariaDB [vmail]> SELECT username, allow_nets FROM mailbox WHERE username="md@pro2col.in" LIMIT 1;
+---------------+----------------+
| username      | allow_nets     |
+---------------+----------------+
| md@pro2col.in | 122.165.122.65 |
+---------------+----------------+
1 row in set (0.00 sec)

Below is restriction with 127.0.0.1 added to allow_nets but restriction not working and accessible from any where

MariaDB [vmail]> SELECT username, allow_nets FROM mailbox WHERE username="md@pro2col.in" LIMIT 1;
+---------------+--------------------------+
| username      | allow_nets               |
+---------------+--------------------------+
| md@pro2col.in | 122.165.67.122,127.0.0.1 |
+---------------+--------------------------+
1 row in set (0.00 sec)

Thank You
Murali

driver = mysql
default_pass_scheme = CRYPT
connect = host=127.0.0.1 port=3306 dbname=vmail user=vmail password=xxxxxxx

# Required by doveadm tools which require to list all mail users.
iterate_query = SELECT username AS user FROM mailbox

password_query = SELECT mailbox.password, mailbox.allow_nets \
        FROM mailbox,domain \
       WHERE mailbox.username='%u' \
             AND mailbox.`enable%Ls%Lc`=1 \
             AND mailbox.active=1 \
             AND mailbox.domain=domain.domain \
             AND domain.backupmx=0 \
             AND domain.active=1

user_query = SELECT \
            LOWER('%u') AS master_user, \
            LOWER(CONCAT(mailbox.storagebasedirectory, '/', mailbox.storagenode, '/', mailbox.maildir)) AS home, \
            CONCAT(mailbox.mailboxformat, ':~/', mailbox.mailboxfolder, '/') AS mail, \
            CONCAT('*:bytes=', mailbox.quota*1048576) AS quota_rule \
        FROM mailbox,domain \
       WHERE mailbox.username='%u' \
             AND mailbox.`enable%Ls%Lc`=1 \
             AND mailbox.active=1 \
             AND mailbox.domain=domain.domain \
             AND domain.backupmx=0 \
             AND domain.active=1

I have already enabled the debug mode in dovecot and posted the log . If you want some more log after enabling the allow_nets, i will post it .

Thank You

As i mentioned earlier if i add "127.0.0.1" as ONE OF allowed ip address , the restriction is not happening in roundcube or  in sogo webmail. But it works only from mail client like outlook , thunderbird etc. I am planning to upgrade the latest version available by ordering paid support with you. Will it solve the issue.