On 11/03/2010 17:14, Leonardo Rodrigues wrote:

Em 11/03/2010 09:53, mailing@securitylabs.it escreveu:

...

Just insert a column in the MySQL table with the host relative to the domain. This is my configuration on the proxy:

password_query = SELECT users.clear AS password, domains.host, '%u*proxy' AS destuser, 'proxy' AS pass, 'Y' AS proxy FROM users,domains WHERE users.username = '%u' AND users.enabled = '1' AND domains.domain = '%d'

In the table "domains" I have a column "domain" with the list of domains I want to proxy, and a column "host" with the IPof the pop/imap server.

and for local domains i could return 127.0.0.1 as host .... is 

that what you're doing for local domains ?

No because I don't have local domains, I use proxy on public network to allow access to the real servers on the private LAN, but I think you have to use proxy_maybe and 127.0.0.1 for local domains:

http://wiki.dovecot.org/PasswordDatabase/ExtraFields/Proxy

On Sat, 2010-03-13 at 21:19 -0300, Leonardo Rodrigues wrote:

...

.., domains.host as host, 'Y' as proxy_maybe, .. from emails, domains where domains.domain = '%d', ..

So it's really exactly the same as per-user quota, except you're just returning it per-domain in the query.

 but if i simply add that, query will always return nothing when 

user is not local.

 where endereco='%u'

 will make it return an empty resultset when user does not exists 

locally.

So are you saying that the users table contains only local users, while some domain table contains all domains and their destination servers? Then you'll just need to do outer join. Something like:

.., domains.host as host, 'Y' as proxy_maybe, .. from domains outer join emails on (username = '%u') where domain.domain = '%d'

Em 13/03/2010 23:12, Leonardo Rodrigues escreveu:

Timo i've tried JOINs, but i've never really understood those 

crazy things (i'm really very far from being a SQL expert).

but seems i got the expected results using UNION and adjusting 

queries to have the same number of columns. First i tried union with normal queries but couldnt get it because queries must have the same number of rows. Then i adjusted it ... and seems its OK.

 got it working with few tweaks .... i had to return '127.0.0.1' as 

host for local users ... without that, login process was segfaulting. And had to return %w as password for the proxied-domains query.

 query is:   (with linebreaks for easy understanding)

password_query = select endereco as user, password, '/var/spool/mail/%u' as userdb_home, 'maildir:/var/spool/mail/%u' as userdb_mail, 8 as userdb_uid, 12 as userdb_gid, concat('*:storage=', quota) as userdb_quota_rule, 'Trash:storage=100M' as userdb_quota_rule2, 'Y' as proxy_maybe, '127.0.0.1' as host from emails where endereco = '%u' and ativa = '1' union select NULL as user, '%w' as password, NULL as userdb_home, NULL as userdb_mail, NULL as userdb_uid, NULL as userdb_gid, NULL as userdb_quota_rule, NULL as userdb_quota_rule2, 'Y' as proxy_maybe, imapproxy.host as host from imapproxy where imapproxy.dominio = '%d'

 that worked flawlessly  :) And i didnt had to use JOINs hehehehe

 log from the main server, for a proxied user:

Mar 13 23:48:48 correio dovecot: imap-login: proxy(some@proxieddomain.com.br): started proxying to 10.252.25.2:143: user=<some@proxieddomain.com.br>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured

 log from the 10.252.25.2 server:

Mar 13 23:48:31 correio dovecot: imap-login: Login: user=<some@proxieddomain.com.br>, method=PLAIN, rip=192.168.1.2, lip=10.252.25.2

 the only minor problem is that when local users get logged in on 

the main server, logs shows that they were proxied to 127.0.0.1 ....

Mar 13 23:33:52 correio dovecot: pop3-login: Login: user=<localuser@localdomain.com.br>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured Mar 13 23:33:52 correio dovecot: pop3-login: proxy(localuser@localdomain.com.br): started proxying to 127.0.0.1:110: user=<localuser@localdomain.com.br>, method=PLAIN, rip=10.255.176.67, lip=192.168.1.2

 i can easily live with that .... but i would like to see local 

users logged as local connections not proxied to 127.0.0.1.

--

Atenciosamente / Sincerily,
Leonardo Rodrigues
Solutti Tecnologia
http://www.solutti.com.br

Minha armadilha de SPAM, NÃO mandem email
gertrudes@solutti.com.br
My SPAMTRAP, do not email it