[Dovecot] Disable IMAP for ONE user only

[Rick Romero]

Duh.  'ONE user only' would be the clue.  So your query would be like:
SELECT email as user, if(%r = '127.0.0.1' & user = 'yourloginname',
password,imap_password) as password FROM virtual_users WHERE email='%u';

Also, test ! and Google!   I'm throwing this out off the top of my
head..  I think the double == was wrong.

The best way test this is just replace the %u with your username and %r
with either 127.0.0.1 or anything else on the MySQL command line and make
sure what's returned is the password you're expecting.

Rick

Quoting Rick Romero <rick at havokmon.com>:

> You don't need vpopmail - that's just an example.  It has it's own table
> structure.   
>
> Are you the only user - I missed that part of the question.  If so,
ignore
> the 'Bit Operator' part, you won't need it.  That's to allow different
> types of access per user (and makes the query that much more complex).
>
> Change your user table structure and add a 2nd password field named
> 'imap_password', then change your Dovecot query SQL to the below:
>
> SELECT email as user, if(%r == '127.0.0.1', password,imap_password) as
> password FROM virtual_users WHERE email='%u';
>
> That will return the contents of 'password' when you use webmail
(assuming
> it's all installed on one box), and 'imap_password' when you connect from
> any other system. 
>
> If you're unfamiliar with modifing MySQL tables, install phpmyadmin (and
> lock it down) or another visual MySQL client.
>
> If there are multiple users, you'll need to either change the query to
> just
> match your username or add another field to do a bit check and make the
> query more complex... :)
>
> Rick
>
> Quoting SIW <bov at bsdpanic.com>:
>
>> Hi Rick
>>
>> I really appreciate your response!
>>
>> Unfortunately my SQL is, how can we say, very basic. I built my server
>> using the Linode guide at:
>>
>>
https://library.linode.com/email/postfix/postfix2.9.6-dovecot2.0.19-mysql
>>
>> Currently my password query looks as follows:
>>
>> password_query = SELECT email as user, password FROM virtual_users WHERE
>> email='%u';
>>
>> I'm not familiar with VPopMail, would I need it in this situation?
>> Currently I use Postfix/Dovecot/MySQL/Apache/Roundcube.
>>
>> On 05/05/2014 21:32, Rick Romero wrote:
>>> Quoting Professa Dementia <professa at dementianati.com>:
>>>
>>>> On 5/5/2014 1:05 PM, SIW wrote:
>>>>> Thats a good point.
>>>>>
>>>>> If I block IMAP/SMTP access to ONE user does that mean that
particular
>>>>> user can't use Roundcube anymore?
>>>>
>>>> That is correct.  If you block IMAP, then webmail will not work.
>>>
>>> Not necessarily.
>>>
>>> From:
>>> http://wiki2.dovecot.org/AuthDatabase/VPopMail
>>>
>>> "logically this means: show password for user=%n at domain=%d when imap
>>> on
>>> the account is not disabled and connection is not comming from
localhost
>>> when webmail access on the account is not disabled and if imap for the
>>> domain is not disabled and (connection is not comming from localhost
>
> when
>>> webmail access for the domain is not disabled) when vlimits are not
>>> overriden on the account "
>>> #
>>> password_query = select pw_passwd as password FROM vpopmail LEFT JOIN
>>> limits ON vpopmail.pw_domain=limits.domain WHERE pw_name='%n' and
>>> pw_domain='%d' and !(pw_gid & 8) and ('%r'!='127.0.0.1' or !(pw_gid &
>
> 4))
>>> and ( ('%r'!='127.0.0.1' or COALESCE(disable_webmail,0)!=1) and
>>> COALESCE(disable_imap,0)!=1 or (pw_gid & 8192) );
>>>
>>> So construct your SQL query in a way that your bit field in MySQL
>>> disables
>>> all access for a single user except when the source IP is your webmail
>>> server.
>>>
>>> If you want multiple passwords, you can modify the password_query with
>>> iif
>>> statements based on the source IP or protocol.
>>>
>>> Like:
>>> select iif(%r == '127.0.0.1' & pw_name== 'yourname' &
>>> pw_domain='yourdomain',pw_webmailpasswrd,pw_passwd) as pw_passwd from
>>> vpopmail .....
>>> Of course that's specific to the vpopmail table... modify as needed for
>>> your own table structure...
>>> Rick
>
>