Alias with all users in mysql table for postfix/dovecot

Question

I have a table with users for my mail server. This table for imap authenfication in dovecot:

+-------------+------------------+-------------------+------------------+------+------+---------------------------------------+--------+---------+---------------------------+
| user_name   | domain_name      | passwd            | pwd_hash         | uid  | gid  | mailbox_basepath                      | enable | quota   | desc_rec                  |
+-------------+------------------+-------------------+------------------+------+------+---------------------------------------+--------+---------+---------------------------+
| logistic    | piduna.pp.ua     | loG-1990M         | _no_hash_passwd_ | 2000 | 2000 | /home/maildir/piduna.pp.ua/           |      1 | 2048000 | box for logistic          |
| 1c          | piduna.pp.ua     | 1c_user_1c        | _no_hash_passwd_ | 2000 | 2000 | /home/maildir/piduna.pp.ua            |      1 | 2048000 | Denisyuk V.V.             |
| admin       | piduna.pp.ua     | AAddMmM1N         | _no_hash_passwd_ | 2000 | 2000 | /home/maildir/piduna.pp.ua            |      1 | 2048000 | Admin                     |
| al.service  | piduna.pp.ua     | Alumo_Serv4321    | _no_hash_passwd_ | 2000 | 2000 | /home/maildir/piduna.pp.ua            |      1 | 2048000 | Alumo Service             |

Next, i use VIEW in MYSQL, where i make concatenatio username and my domain:

+------------------------------+-------------------------------------------------+-------------+------------------+------+------+---------------------------------------+---------+
| email_fqn                    | mailbox_userpath                                | user_name   | domain_name      | uid  | gid  | mailbox_basepath                      | quota   |
+------------------------------+-------------------------------------------------+-------------+------------------+------+------+---------------------------------------+---------+
| .logistic@piduna.pp.ua       | /home/maildir/piduna.pp.ua/public/.logistic     | .logistic   | piduna.pp.ua     | 2000 | 2000 | /home/maildir/piduna.pp.ua/public     | 2048000 |
| 1c@piduna.pp.ua              | /home/maildir/piduna.pp.ua/1c                   | 1c          | piduna.pp.ua     | 2000 | 2000 | /home/maildir/piduna.pp.ua            | 2048000 |
| admin@piduna.pp.ua           | /home/maildir/piduna.pp.ua/admin                | admin       | piduna.pp.ua     | 2000 | 2000 | /home/maildir/piduna.pp.ua            | 2048000 |
| al.service@piduna.pp.ua      | /home/maildir/piduna.pp.ua/al.service           | al.service  | piduna.pp.ua     | 2000 | 2000 | /home/maildir/piduna.pp.ua            | 2048000 |

I need create aliases. For example, on it@vpiduna.pp.ua, i need to send email on admin@vpiduna.pp.ua and al.service@vpiduna.pp.ua. I do it. This table:

+------------------------+-------------------------------------------------+
| source                 | destination                                     |
+------------------------+-------------------------------------------------+
| it@piduna.pp.ua        | admin@piduna.pp.ua, al.service@piduna.pp.ua     |
+------------------------+-------------------------------------------------+

In mysql-virtual_aliases.cf:

user = root
password = myPassword
dbname = mail_db
query = SELECT destination FROM virtual_aliases WHERE source='%s'
hosts = 127.0.0.1

And this working. But, in my organization, i have alias for all users. It name is all@piduna.pp.ua. And, when i created new user, i need add it in alias all@piduna.pp.ua. How to create alias, that take all user accounts from my first table automatically?

I think, that i need create table with one parametr: all@piduna.pp.ua, and after that, create VIEW where i make concatenatio user_name & domain, from first table ? Is this correct ? And how to do this correctly ?

How did you end up solving this? – Allen Luce Mar 15 '16 at 21:39 — Allen Luce, Mar 15 '16 at 21:39

score 0 · Answer 1 · answered Jan 15 '16 at 19:06

You might be able to do this with SQL trickiness:

SELECT IF((@addy:='%s') REGEXP '^all@.*', (
  SELECT email_fqn FROM your_email_view WHERE 
         domain=SUBSTRING(@addy,INSTR(@addy,'@')+1)
  ), (
  SELECT destination FROM virtual_aliases WHERE source=@addy
))

That should make it so any "all@" address will send to all email addresses that are in that domain.

Alias with all users in mysql table for postfix/dovecot

1 Answers1