Integrating Drupal with Postfix mail server

By Mapi99 on 21 Jan 2011 at 15:35 UTC

Hello all,

I have a linode server where i have complete root acces, i have a postfix mail server running.

But what i really want to do is somehow integrate postfix and drupal, i know there is a module for 6.x but it offers alot of features i dont need and i wish to use 7.x

Is it possible to let Postfix load or grab existing passwords and usernames from the drupal database and use them as email adresses?

Thanks,
mapi

Comments

Postfix + MySQL

gooney0 commented 21 January 2011 at 18:15

This is possible.

Checkout:

http://www.postfix.org/MYSQL_README.html

You may want to add a field or two to users or even create a module if the users aren't all in the same domain.

Hello Scott, thanks for the

Mapi99 commented 21 January 2011 at 22:55

Hello Scott, thanks for the reply

The page you linked doesn't specifically say about hooking up postfix to an existing db, it just says you can connect it to a mysql db.

So basically the way to do this is write a module that will generate the required rows and tables in the database, and POINT postfix to those instead of its own db, correct?

Thanks alot guys, hope to get some more insight,

Mapi

Yes

gooney0 commented 22 January 2011 at 14:57

It looks to me that you can specify the query to use in the Postfix configuration. If so you just need to put together a query which will get the user ids and email address from Drupal's table.

It may be wise to get Postfix working with MySQL first then get fancy.

Mapi, I'm in the same lime-green boat. I'm running aegir. I couldn't get the Mailfix module to work at all, though it might be a good starting point. An email module that depends on the hosting module might be the way forward - it could alter the site node edit-forms and also hook into the quotas part of aegir.

I'm not too keen on the idea of auto-creating email addresses for every drupal user, or the idea of letting people who's websites i'm hosting do that either. Quotas and roles would be the way forward. It could even link up with the uc_hosting module so people can but email addresses.

I've only done a few hook_form_alters and a fair bit of theming - I've never written a proper module. But i'm up for collaborating.
Linode's disk image restore FTW!
If I do try this, I'll be clicking that button a lot.

One thing I really have no idea about is the 'Drupal Way' of talking to the database. Can somebody point me in the right direction?

Drupal and mail integration.

KristVB commented 28 March 2011 at 07:27

you don't need mailfix.

Mail forwarding, is quite straightforward. All you need is root access on your server, a running postfix some mysql query magic.

Asume your site is called "drupal.org", your database drupal, and there is a mysql user "mail" with select privileges on "users", and password "mail".

create a file named "drupal_forward.cf". The content of this file is:


# Virtual Map
#
# Gets email and forward from mysql db.

user = mail
password = mail

dbname = drupal

query = SELECT mail  FROM users WHERE concat( name, "@drupal.org")  LIKE '%s' and status = 1;

You'll need to edit this file to make it fit your install.

Your postfix main.cf you need to edit so that it containst the following:

virtual_alias_domains = drupal.org
virtual_alias_maps = mysql:/etc/postfix/drupal_forward.cf

And there is a clever trick that allows me to send emails to all users that have a certain role. I have a second query configured like this:
drupal_roles.cf

# Virtual Map
#
# Forwards to role@drupal.org

user = mail
password = mail

dbname = drupal

query =  select group_concat(concat(u.mail) separator ', ') from users u left join (users_roles ur, role r) on (u.uid = ur.uid and ur.rid =r.rid) where concat(r.name, '@drupal.org') ='%s' and u.uid >1  group by r.name ;

For this to work the mail users now also needs select privileges on users_roles, and role.
My main.cf now needs to contain:

virtual_alias_domains = drupal.org
virtual_alias_maps = mysql:/etc/postfix/drupal_forward.cf,
                                    mysql:/etc/postfix/drupal_roles.cf

Postfix will check each map listed in the virtual_alias_maps variable. If you have a hash file (to have some manual aliases too) you can also list it there:

virtual_alias_domains = drupal.org
virtual_alias_maps = mysql:/etc/postfix/drupal_forward.cf,
                                    mysql:/etc/postfix/drupal_roles.cf,
                                    hash:/etc/postfix/drupal

On my site users actually have the option to have mail delivered to a local imap mailbox, but that is a bit more complicated. I did however solve that purely using postfix's mysql features, and dovecot for imap/pop. I did not need any special modules.

Thanks, this was really

tobbera commented 7 March 2013 at 13:32

Thanks, this was really helpful.

I have now managed to get postfix to lookup teacher1@example.org.au and return the email adress (Teacher1TEST@example.org.au) attached to that user in Drupal to postfix. But then Postfix gives me an error saying that the email address is not found. Does not matter if I set the email adress to teacher1@example.org.au in drupal, same error. Dont I need some kind of maildrop setting?

Mar  7 13:22:55 SSiS postfix/error[5934]: EC5D1683A3: to=<Teacher1TEST@example.org.au>, orig_to=<teacher1@example.org.au>, relay=none, delay=0.31, delays=0.16/0/0/0.15, dsn=5.0.0, status=bounced (User unknown in virtual alias table)

root@SiS:~# postconf -n
alias_database = hash:/etc/aliases
alias_maps = hash:/etc/aliases
append_dot_mydomain = no
biff = no
config_directory = /etc/postfix
debug_peer_level = 2
debug_peer_list = example.org.au
inet_interfaces = 188.40.217.122,127.0.0.1
mydestination = $myhostname, localhost.$mydomain, localhost
mynetworks = 127.0.0.0/8
recipient_delimiter = +
relayhost =
smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU)
virtual_alias_domains = example.org.au
virtual_alias_maps = mysql:/etc/postfix/drupal_forward.cf

root@SSiS:~# cat /etc/postfix/drupal_forward.cf
# Virtual Map
#
# Gets email and forward from mysql db.

hosts = 127.0.0.1
user = SiS_Dru_Adm
password = localpsw
dbname = SiS_Drupal

query = SELECT mail  FROM users WHERE concat( name, "@example.org.au")  LIKE '%s' and status = 1;

Thanks!

Working!

tobbera commented 9 March 2013 at 12:20

Got it to work with the following:

root@SiS:/etc/postfix# postconf -n
alias_database = hash:/etc/aliases
alias_maps = hash:/etc/aliases
append_dot_mydomain = no
biff = no
config_directory = /etc/postfix
inet_interfaces = 188.41.237.122,127.0.0.1
mydestination = $myhostname, localhost.$mydomain, localhost
mynetworks = 127.0.0.0/8
proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $virtual_mailbox_limit_maps
recipient_delimiter = +
relayhost =
smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU)
virtual_alias_maps = proxy:mysql:/etc/postfix/drupal_email.cf
virtual_gid_maps = static:5000
virtual_mailbox_base = /home/vmail
virtual_mailbox_domains = example.org.au
virtual_mailbox_maps = proxy:mysql:/etc/postfix/drupal_virtual_mailboxes.cf
virtual_uid_maps = static:5000


root@SiS:/etc/postfix# cat /etc/postfix/drupal_virtual_mailboxes.cf
hosts = 127.0.0.1
user = Dru_Adm
password = localus
dbname = Drupal
query = SELECT mail FROM users WHERE mail='%s' and status = 1;
query = SELECT CONCAT(SUBSTRING_INDEX(mail,'@',-1),'/',SUBSTRING_INDEX(mail,'@',1),'/') FROM users WHERE mail='%s'



root@SiS:/etc/postfix# cat /etc/postfix/drupal_email.cf
hosts = 127.0.0.1
user = Dru_Adm
password = localus
dbname = Drupal
query = SELECT mail FROM users WHERE mail='%s' and status = 1;