[SOLVED] Virtual user mail system

nic96 · 2014-08-01 20:49:52

I followed the wiki article on installing a virtual user mail system. But I keep getting the following error

warning: connect to mysql server localhost: Access denied for user 'postfix_user'@'localhost' (using password: YES)
warning: proxy:mysql:/etc/postfix/virtual_alias_maps.cf: table lookup problem
warning: connect to mysql server localhost: Access denied for user 'postfix_user'@'localhost' (using password: YES)
warning: proxy:mysql:/etc/postfix/virtual_mailbox_domains.cf: table lookup problem
warning: proxy:mysql:/etc/postfix/virtual_alias_maps.cf: table lookup problem
warning: virtual_alias_domains lookup failure

Logging in with with mysql -u postfix_user -p works fine though and my username and password is correctly set in /etc/postfix/virtual_* config files.
Any Ideas on what I did wrong?

Last edited by nic96 (2014-08-03 20:15:09)

Gregosky · 2014-08-01 21:14:19

Hi,

Can you post your main.cf? Also what is the smtp authentication you are using? Is it cyrus sasl? Or maybe you are employing dovecot? Either option you need to show related config so we can try to help you :-)

Postfix is quite complex and it's difficult to say anything without seeing actual configuration.

Edit - I may be wrong here but this topic should rather land somewhere between networking and server administration - postfix does not sound newbie enough to me :-)

Last edited by Gregosky (2014-08-02 00:01:57)

nic96 · 2014-08-01 21:25:59

This is a comment stripped version of my postfix main.cf and also domain and host names changed to examples.

queue_directory = /var/spool/postfix

command_directory = /usr/bin

daemon_directory = /usr/lib/postfix

data_directory = /var/lib/postfix

mail_owner = postfix

myhostname = mail.mydomain.tld

mydomain = mydomain.tld

myorigin = $mydomain

mydestination = $myhostname, localhost.$mydomain, localhost, $mydomain

unknown_local_recipient_reject_code = 550

mynetworks_style = host

alias_maps = hash:/etc/postfix/aliases

alias_database = $alias_maps

home_mailbox = Maildir/
 
debug_peer_level = 2

debugger_command =
         PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
         ddd $daemon_directory/$process_name $process_id & sleep 5

sendmail_path = /usr/bin/sendmail

newaliases_path = /usr/bin/newaliases

mailq_path = /usr/bin/mailq

setgid_group = postdrop

html_directory = no

manpage_directory = /usr/share/man

sample_directory = /etc/postfix/sample

readme_directory = /usr/share/doc/postfix
inet_protocols = ipv4

relay_domains = * # May be a bad idea. You usually don't want postfix to forward mail from strangers.
virtual_alias_maps = proxy:mysql:/etc/postfix/virtual_alias_maps.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/virtual_mailbox_domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/virtual_mailbox_maps.cf
virtual_mailbox_base = /home/vmail
virtual_mailbox_limit = 512000000
virtual_minimum_uid = 5000
virtual_transport = virtual
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000
local_transport = virtual
local_recipient_maps = $virtual_mailbox_maps
transport_maps = hash:/etc/postfix/transport

smtpd_sasl_auth_enable = yes
smtpd_sasl_type = dovecot
smtpd_sasl_path = /var/run/dovecot/auth-client
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
smtpd_relay_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
smtpd_sasl_security_options = noanonymous
smtpd_sasl_tls_security_options = $smtpd_sasl_security_options
smtpd_tls_auth_only = yes
smtpd_tls_cert_file = /etc/ssl/private/server.crt
smtpd_tls_key_file = /etc/ssl/private/server.key
smtpd_sasl_local_domain = $mydomain
broken_sasl_auth_clients = yes
smtpd_tls_loglevel = 1

And here's my dovecot.conf:

protocols = imap
auth_mechanisms = plain
passdb {
    driver = sql
    args = /etc/dovecot/dovecot-sql.conf
}
userdb {
    driver = sql
    args = /etc/dovecot/dovecot-sql.conf
}

service auth {
    unix_listener auth-client {
        group = postfix
        mode = 0660
        user = postfix
    }
    user = root
}

mail_home = /home/vmail/%d/%u
mail_location = maildir:~

ssl_cert = </etc/ssl/private/server.crt
ssl_key = </etc/ssl/private/server.key

Last edited by nic96 (2014-08-01 21:29:03)

jasonwryan · 2014-08-01 21:39:38

Moving to Networking...

Gregosky · 2014-08-01 23:54:43

And what is the content of /etc/dovecot/dovecot-sql.conf?

I also assume you have created mysql database and granted access to relevant user (beware in mysql 'user' may be treated different way than 'user'@'localhost')

Log shows access denied - is mysql running on network port or on socket file? If on socket file - is that file located in standard directory? If non-standard directory then I would check permissions.

Last edited by Gregosky (2014-08-02 00:01:04)

nic96 · 2014-08-02 00:11:41

My /etc/dovecot/dovecot-sql.conf:

driver = mysql
connect = host=localhost dbname=postfix_db user=postfix_user password=mypasswd
# The new name for MD5 is MD5-CRYPT so you might need to change this depending on version
default_pass_scheme = MD5-CRYPT
# Get the mailbox
user_query = SELECT '/home/vmail/%d/%u' as home, 'maildir:/home/vmail/%d/%u' as mail, 5000 AS uid, 5000 AS gid, concat('dirsize:storage=',  quota) AS quota FROM mailbox WHERE username = '%u' AND active = '1'
# Get the password
password_query = SELECT username as user, password, '/home/vmail/%d/%u' as userdb_home, 'maildir:/home/vmail/%d/%u' as userdb_mail, 5000 as  userdb_uid, 5000 as userdb_gid FROM mailbox WHERE username = '%u' AND active = '1'
# If using client certificates for authentication, comment the above and uncomment the following
#password_query = SELECT null AS password, ‘%u’ AS user

mysql is running on both a port and a socket file.

Checking the permissions of mysqld.sock looks ok:

ls -l /run/mysqld/mysqld.sock
srwxrwxrwx 1 mysql mysql 0 Aug  1 20:30 /run/mysqld/mysqld.sock

Last edited by nic96 (2014-08-02 00:25:05)

Gregosky · 2014-08-03 10:31:39

I got logged out while writing my reply (d'oh!)

Ok, so first of all - grant access to your postfix_user@localhost. That hopefully should clear out 'access denied' error.

grant select on `postfix_db`.`mailbox` to postfix_user@localhost identified by PASSWORD('your password')
flush privileges;

Now - it seems you do not want to store plain passwords in your 'mailbox' table. Be aware that dovecot hashes may be generated different way than mysql hashes. I create hashes following way (I'm not using MD5):

doveadm pw -s SHA512-CRYPT

Next thing to check - how are you storing users in your 'mailbox' table? I'm storing whole user@domain string and then my authentication query looks like following (in my case %n returns user@domain rather than user):

password_query = \
  SELECT LOWER(`email`) as username, LOWER(`domain`), password \
  FROM `users` WHERE `email` = LOWER('%n') AND domain = LOWER('%d')

And last thing - you can always turn debug on and check what the issue is. In your dovecot.conf:

auth_verbose=yes

nic96 · 2014-08-03 20:13:52

Turns out the Access denied error was due to me have an apostrophe before and after the password in the sql.conf . Thanks alot for your help anyway.

Arch Linux

#1 2014-08-01 20:49:52

[SOLVED] Virtual user mail system

#2 2014-08-01 21:14:19

Re: [SOLVED] Virtual user mail system

#3 2014-08-01 21:25:59

Re: [SOLVED] Virtual user mail system

#4 2014-08-01 21:39:38

Re: [SOLVED] Virtual user mail system

#5 2014-08-01 23:54:43

Re: [SOLVED] Virtual user mail system

#6 2014-08-02 00:11:41

Re: [SOLVED] Virtual user mail system

#7 2014-08-03 10:31:39

Re: [SOLVED] Virtual user mail system

#8 2014-08-03 20:13:52

Re: [SOLVED] Virtual user mail system

Board footer