Thanks again. Things are slowly becoming clear to me. I did find that dovecot has an allow_nets parameter on the password_query that can specify the IPs that a user is allowed to login from.
http://wiki2.dovecot.org/PasswordDatabase/ExtraFields/AllowNets So, I should be able this to allow login only from my local Roundcube for some users and allow all client ips for others. I haven’t tried any of this yet as I’m still just trying to understand what all the options are. On Feb 1, 2014, at 11:41 AM, Reindl Harald <h.rei...@thelounge.net> wrote: > one thing to open your mind about capabilities with mysql / views > the postfix query looks simple, well but look at the underlying views > they are here to support my "all what is thinkable" backends :-) > > the same may help to marry own tables with dbmail ones for dovecot-proxy > ______________________________________________ > > [root@testserver:~]$ cat /etc/postfix/mysql-recipients.cf > user = dbmail_readonly > password = ******** > dbname = dbmail > hosts = unix:/var/lib/mysql/mysqld_dbmail.sock > query = select alias from dbma_recipients where alias='%s'; > > [root@testserver:~]$ cat /etc/postfix/mysql-transport.cf > user = readonly > password = ******** > dbname = dbmail > hosts = unix:/var/lib/mysql/mysqld_dbmail.sock > query = select transport from dbma_transports where mydestination='%s' or > mydestination='%d' order by transport > desc limit 1; > ______________________________________________ > > CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER > VIEW `dbma_recipients` AS select > `dbmail_aliases`.`alias` AS `alias` from `dbmail_aliases` where > (not((`dbmail_aliases`.`alias` like '%@'))) union > (select concat('@',`dbma_mta`.`mydestination`) AS `alias` from `dbma_mta` > where (`dbma_mta`.`transport` <> > 'dbmail-lmtp:127.0.0.1:24')) union (select > `dbma_recipient_relay`.`mydestination` AS `alias` from > `dbma_recipient_relay`) union (select `dbma_aliases_global`.`alias` AS > `alias` from `dbma_aliases_global`) > > CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER > VIEW `dbma_transports` AS select > `dbma_mta`.`mydestination` AS `mydestination`,`dbma_mta`.`transport` AS > `transport` from `dbma_mta` union (select > `dbma_recipient_relay`.`mydestination` AS > `mydestination`,`dbma_recipient_relay`.`transport` AS `transport` from > `dbma_recipient_relay`) union (select `dbma_transports_error`.`mydestination` > AS > `mydestination`,`dbma_transports_error`.`transport` AS `transport` from > `dbma_transports_error`) > > Am 01.02.2014 17:23, schrieb Reindl Harald: >> Am 01.02.2014 17:04, schrieb KT Walrus: >>> Thanks for the configs. It really helps. >>> >>> I’m still trying to understand the dovecot password_query. Does the “‘Y’ >>> AS nologin” mean that dovecot >>> does not authenticate the password, but pass the password on to DBMail to >>> authenticate? >> >> no physical account, dovecot can act in mixed mode too, that's why there is >> a "proxy" field too >> in fact it means give a column "nologin" with always "Y" as value in the >> result back >> >>> Can Dovecot do the authentication and have DBMail accept this >>> authentication? >> >> dovecot does the authentication between client/server >> >>> Why does the dovecot password_query you gave repeat the password as >>> “password” and as “pass”? >> >> because that is the data dovecot uses for authentication against DBMail >> >>> I currently have a separate database for dovecot (and my other scripts) to >>> use for >>> authentication. Can I set it up so the only authentication is done using >>> this separate >>> database and not have DBMail (which will be inaccessible from everyone but >>> dovecot proxy) >>> do any authentication at all? >> >> not sure how to handle the auth between dovecot and the DBMail backend >> server in that case >> finally dovecot is not interested from which table these data are coming as >> long they >> lead to the expected result >> >>> Or, will DBMail always do its own authentication regardless of whether the >>> proxy did the authentication? >> >> yes, dovecot acts from the view of DBmail like a ordianry client >> >>> Just trying to understand how all this works so I don’t make a mistake that >>> will compromise securit >> >> you should find all informations there, for deeper details in case of >> dovecot and it's capabilities >> in case of proxying i would suggest the dovecot list, the developer is >> active there, dovecot has like >> DBMail one core-contributor of code >> >> http://wiki2.dovecot.org/PasswordDatabase/ExtraFields/Proxy >> >>> On Feb 1, 2014, at 10:25 AM, Reindl Harald <h.rei...@thelounge.net> wrote: >>> >>>> BTW: >>>> >>>> the password query may be one solution to restrict clients, consult the >>>> dovecot documentation >>>> how you get the client-ip in the game and with a smart join 'Y' AS proxy >>>> and 'N' AS proxy may >>>> do what you need and a webinterface for such settings should be trivial >>>> >>>> password_query = SELECT passwd as password, '127.0.0.1' as host, userid as >>>> destuser, passwd AS pass, 'Y' AS >>>> nologin, 'Y' AS nodelay, 'Y' AS proxy FROM dbmail_users WHERE userid='%u' >>>> >>>> Am 01.02.2014 16:22, schrieb Reindl Harald: >>>>> by defintion DBMail only can see the proxy IP because the client itself >>>>> never touches it directly, so restritions of the origin IP belong to >>>>> dovecot, below a working dovecot/dbmail-proxy config >>>>> >>>>> this one needs plaintext-passwords in the database because >>>>> "auth_mechanisms" CRAM-MD5 >>>>> this small snippet before shows another benefit of such a setup with >>>>> dovecot >>>>> >>>>> auth-caching and finally that makes it clear no direct connection >>>>> client/dbmail >>>>> in doubt that enforces security because you need a succesfull >>>>> authentication >>>>> to bypass any imap/pop3 command to the dbmail services >>>>> >>>>> passwords encrypted or not is a different discusssion, in my case there >>>>> are hundrets >>>>> of clients with configurations from 10 years ago where we did not have >>>>> TSL/SSL at >>>>> all and in that case offer CRAM-MD5 is more important and force them all >>>>> to TLS >>>>> impossible - frankly if someone is that deep on the server to access the >>>>> user-table >>>>> i have other problems as for sure random generated one-service passwords >>>>> >>>>> auth_cache_ttl = 600 >>>>> auth_cache_negative_ttl = 600 >>>>> auth_username_chars = >>>>> abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@% >>>>> auth_username_translation = >>>>> %@AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz >>>>> __________________________________________________________________________________ >>>>> >>>>> [root@testserver:~]$ cat /etc/dovecot/sql.conf >>>>> driver = mysql >>>>> connect = host=/var/lib/mysql/mysqld_dbmail.sock >>>>> dbname=dbmail user=dbmail password=*************** >>>>> password_query = SELECT passwd as password, '127.0.0.1' as host, >>>>> userid as destuser, passwd AS pass, 'Y' AS >>>>> nologin, 'Y' AS nodelay, 'Y' AS proxy FROM dbmail_users WHERE userid='%u' >>>>> default_pass_scheme = plain >>>>> __________________________________________________________________________________ >>>>> >>>>> [root@testserver:~]$ cat /etc/dovecot/dovecot.conf >>>>> # provided services >>>>> protocols = imap pop3 >>>>> >>>>> # configure ssl >>>>> ssl = yes >>>>> ssl_cert = </etc/postfix/certs/localhost.pem >>>>> ssl_key = </etc/postfix/certs/localhost.pem >>>>> ssl_cipher_list = >>>>> ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES128-SHA:ECDHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES256-SHA256:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA:AES256-SHA:DHE-RSA-CAMELLIA128-SHA:DHE-RSA-CAMELLIA256-SHA:CAMELLIA128-SHA:CAMELLIA256-SHA:ECDHE-RSA-DES-CBC3-SHA:DES-CBC3-SHA:!SSLv2 >>>>> ssl_prefer_server_ciphers = yes >>>>> ssl_parameters_regenerate = 0 >>>>> >>>>> # configure imap-proxy >>>>> service imap-login { >>>>> inet_listener imap { >>>>> address = <PUBLIC-IP> >>>>> port = 143 >>>>> } >>>>> inet_listener imaps { >>>>> address = <PUBLIC-IP> >>>>> port = 993 >>>>> } >>>>> vsz_limit = 128M >>>>> service_count = 0 >>>>> process_min_avail = 1 >>>>> process_limit = 1 >>>>> client_limit = 200 >>>>> } >>>>> >>>>> # configure pop3-proxy >>>>> service pop3-login { >>>>> inet_listener pop3 { >>>>> address = <PUBLIC-IP> >>>>> port = 110 >>>>> } >>>>> inet_listener pop3s { >>>>> address = <PUBLIC-IP> >>>>> port = 995 >>>>> } >>>>> vsz_limit = 128M >>>>> service_count = 0 >>>>> process_min_avail = 1 >>>>> process_limit = 1 >>>>> client_limit = 200 >>>>> } >>>>> >>>>> # default settings >>>>> imap_capability = IMAP4 IMAP4rev1 ACL RIGHTS=texk >>>>> NAMESPACE CHILDREN SORT QUOTA >>>>> THREAD=ORDEREDSUBJECT UNSELECT IDLE >>>>> login_greeting = >>>>> login_log_format_elements = %u %r %m %k >>>>> login_log_format = %{login_status}: %s >>>>> mail_max_userip_connections = 100 >>>>> auth_mechanisms = CRAM-MD5 DIGEST-MD5 SCRAM-SHA-1 APOP >>>>> LOGIN PLAIN >>>>> disable_plaintext_auth = no >>>>> shutdown_clients = no >>>>> version_ignore = yes >>>>> >>>>> # Logging >>>>> syslog_facility = mail >>>>> >>>>> # authentication process >>>>> auth_worker_max_count = 50 >>>>> auth_cache_size = 1024 >>>>> auth_cache_ttl = 600 >>>>> auth_cache_negative_ttl = 600 >>>>> auth_username_chars = >>>>> abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@% >>>>> auth_username_translation = >>>>> %@AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz >>>>> >>>>> # debug options >>>>> auth_debug = no >>>>> auth_debug_passwords = no >>>>> auth_verbose = no >>>>> mail_debug = no >>>>> verbose_ssl = no >>>>> >>>>> # configure proxy-database >>>>> passdb { >>>>> driver = sql >>>>> args = /etc/dovecot/sql.conf >>>>> } >>>>> >>>>> # we are not using local users >>>>> userdb { >>>>> driver = static >>>>> args = static uid=10000 gid=10000 home=/dev/null >>>>> } >>>>> >>>>> # configure backend for postfix sasl-auth >>>>> service auth { >>>>> unix_listener /var/spool/postfix/private/auth { >>>>> mode = 0660 >>>>> user = postfix >>>>> group = postfix >>>>> } >>>>> } >>>>> __________________________________________________________________________________ >>>>> >>>>> Am 01.02.2014 16:07, schrieb KT Walrus: >>>>>> Thanks. If my dbmail IMAP server is behind a dovecot IMAP proxy, will >>>>>> the usermap deny/allow IP addresses work? >>>>>> Or, will dbmail see the IP address of the dovecot proxy and not be able >>>>>> to see the clientip? >>>>>> >>>>>> If this is a problem, should I enforce the IP restrictions in dovecot >>>>>> proxy and not use dbmail usermaps? >>>>>> >>>>>> On Jan 31, 2014, at 4:48 PM, Paul J Stevens <p...@nfg.nl >>>>>> <mailto:p...@nfg.nl>> wrote: >>>>>> >>>>>>> On 31-01-14 16:45, KT Walrus wrote: >>>>>>>> I only have one domain for my mail addresses, e.g. >>>>>>>> usern...@domain.com <mailto:usern...@domain.com>. >>>>>>>> >>>>>>>> When I create a new account, should the userid be set to username and >>>>>>>> a single alias created for usern...@domain.com >>>>>>>> <mailto:usern...@domain.com> for this userid? Is >>>>>>>> this the best way to set up new accounts? >>>>>>> >>>>>>> The userid is the login handle. Choose whatever you prefer. Users will >>>>>>> generally expect to be able to login with their address. If the address >>>>>>> equals the login, you don't need to add the alias. That would be >>>>>>> redundant. >>>>>>>> >>>>>>>> I see the aliases table has a column called client_idnr. What is >>>>>>>> this column used for? >>>>>>> >>>>>>> That field is in the users table. It's an archaeological anachronism >>>>>>> that was never used, except by third-party users. >>>>>>> >>>>>>>> >>>>>>>> I also want to set up IMAP/POP3 access to allow only IMAP/POP3 for a >>>>>>>> certain group of users, and deny IMAP/POP3 access to all others >>>>>>>> except from a Roundcube Mail installation on my website. I assume I >>>>>>>> can do this using Usermaps feature, but I’m having a little >>>>>>>> difficulty understanding the best way to set this up. >>>>>>>> >>>>>>>> What rows should I have in my usermaps table to implement this? Do I >>>>>>>> need 4 rows per user to allow IMAP/POP3 access from the web or >>>>>>>> Roundcube Mail servers or can I set things up such that there is a >>>>>>>> group for the users and only add a couple of rows to grant access to >>>>>>>> all users in the group? >>>>>>> >>>>>>> First set your default policy: >>>>>>> >>>>>>> login=ANY, sock_allow='inet:10.0.0.1:143' >>>>>>> >>>>>>> to allow any connection on a non-routed address - i.e. from roundcube. >>>>>>> >>>>>>> Block anyone else: >>>>>>> >>>>>>> login=ANY, sock_deny='inet:1.2.3.4:0' >>>>>>> >>>>>>> on the external public address >>>>>>> >>>>>>> Next set specific access for designated users: >>>>>>> >>>>>>> login='us...@domain.org <mailto:login='us...@domain.org>', >>>>>>> sock_allow='inet:1.2.3.4:0' >>>>>>> login='us...@domain.org <mailto:login='us...@domain.org>', >>>>>>> sock_allow='inet:1.2.3.4.0' >>>>>>> >>>>>>> The match on ANY takes a lower precedence than the full login match. >>>>>>> More specific CIDR blocks also take precedence over less specific ones. >>>>>>> >>>>>>>> Lastly, I want to have an admin IMAP user that can login (only from >>>>>>>> localhost) and access/update/create/delete mailboxes for any existing >>>>>>>> user using IMAP. What is the best way to set this up? I’ve been >>>>>>>> assuming I need to set up an ACL for each user mailbox to allow the >>>>>>>> admin user access, but I don’t think this will allow the admin user >>>>>>>> to create/delete mailboxes (and autosubscribe the user to them). Is >>>>>>>> there a way to set up a wildcard ACL (mailbox id 0?) to allow access >>>>>>>> to the admin user to all mailboxes? >>>>>>> >>>>>>> Don't do that! Bad Idea! Don't use IMAP to casually grant access to >>>>>>> other people's mailboxes. Integrity alert. Bad karma. Bad business. Just >>>>>>> plain creepy, imo. And *very* bad security from someone who is worried >>>>>>> about compute cycles in password cracks. >>>>>>> >>>>>>> You must have us confused with exchange > > _______________________________________________ > DBmail mailing list > DBmail@dbmail.org > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail _______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
