How do i pass virtual usernames from postfix to procmail?

Question

I'm trying to move spam mails to the junk folder automatically with a procmailrc file. Right now I've got postfix running with virtualmail, storing mailbox-settings etc in mysql:

virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual-aliases.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual-domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual-mailboxes.cf
virtual_minimum_uid = 1111
virtual_uid_maps = static:1111
virtual_gid_maps = static:1111
virtual_mailbox_base = /home/vmail

I also got procmail as a virtual transport enabled:

virtual_transport = procmail
procmail_destination_recipient_limit = 1

main.cf:

procmail unix - n n - - pipe flags=DRXhuq user=vmail
  argv=/usr/bin/procmail -m E_SENDER=$sender E_RECIPIENT=$recipient ER_USER=$user ER_DOMAIN=$domain ER_DETAIL=$extension NEXTHOP=$nexthop /etc/procmail.d/default.rc

Now in /etc/procmail.d/default.rc I want to route mails to /home/vmail/[DOMAIN]/[USERNAME], where USERNAME is not the $recipient or the $user value but another value stored in my database. Is there any way I can get that name from the database to procmail, so it can route mails correctly?

+----------+--------------+------+-----+---------------------------------------------------------------------+-------+
| Field    | Type         | Null | Key | Default                                                             | Extra |
+----------+--------------+------+-----+---------------------------------------------------------------------+-------+
| domain   | varchar(255) | NO   | PRI | NULL                                                                |       |
| email    | varchar(255) | NO   | PRI | NULL                                                                |       |
| user     | varchar(255) | NO   |     | NULL                                                                |       |
| password | varchar(255) | NO   |     | {CRYPT}$2y$05$M93Wk.20E31Let6AsWvjx.5eEfsw3ZM1jpha/XkPq6O5PPaiDgc/6 |       |
| location | varchar(255) | NO   |     | NULL                                                                |       |
| quota    | bigint       | NO   |     | 10000000000                                                         |       |
| enabled  | tinyint      | NO   |     | 0                                                                   |       |
+----------+--------------+------+-----+---------------------------------------------------------------------+-------+

the mail is delivered to /home/vmail/DOMAIN/user where user is a custom username that doesn't need to be the same as the email.

SELECT * FROM [table] WHERE user = 'xyz';

+-----------+----------------------+------+---------------------------------------------------------------------+----------------+-------------+---------+
| domain    | email                | user | password | location       | quota       | enabled |
+-----------+----------------------+------+---------------------------------------------------------------------+----------------+-------------+---------+
| exam.ple  | mymail               | xyz  | password | exam.ple/xyz/  | 50000000000 |       1 |

Within Procmail, something like `USERNAME=\`quertytool --user="$user"\`;` is that what you are trying to ask? — tripleee, Sep 25 '22 at 16:21
Just did that :) The problem is the ```$user``` variable doesn't pass the right information. — Ratatouille1945, Sep 26 '22 at 11:13
Thanks. Could you please still clarify how the information you want to extract is represented in this schema, perhaps with some actual SQL you would like to run? — tripleee, Sep 26 '22 at 11:14

score 0 · Answer 1 · answered Sep 26 '22 at 11:37

If I'm correctly able to guess what you are trying to ask, you would need to run an SQL query for every incoming email message. This sounds rather heavy, but isn't hard to do.

In /etc/procmailrc, something like this (where I obviously had to guess a bit how your pieces fit together, and pseudo-code the actual command to query your SQL database):

loc=`sql "select concat(domain, '/', user) from [table] where email = '$ER_USER';"`
DEFAULT=/home/vmail/$loc

Compiling this into a separate Postfix map (which you update every time the database changes) would probably be a more scalable solution.

How do i pass virtual usernames from postfix to procmail?

1 Answers1