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 <r...@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 <b...@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 <profe...@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
