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 <[email protected]> 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 <[email protected]
>>>>> <mailto:[email protected]>> wrote:
>>>>>
>>>>>> On 31-01-14 16:45, KT Walrus wrote:
>>>>>>> I only have one domain for my mail addresses, e.g.
>>>>>>> [email protected] <mailto:[email protected]>.
>>>>>>>
>>>>>>> When I create a new account, should the userid be set to username and
>>>>>>> a single alias created for [email protected]
>>>>>>> <mailto:[email protected]> 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='[email protected] <mailto:login='[email protected]>',
>>>>>> sock_allow='inet:1.2.3.4:0'
>>>>>> login='[email protected] <mailto:login='[email protected]>',
>>>>>> 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
signature.asc
Description: OpenPGP digital signature
_______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
