# Postfix and Mysql

## trossachs

I am trying to incorporate PF and MS together starting with virtual mailboxes. I have followed various instructions, but when I comment out the "hash" lines with regards to the virtual mailbox and uncomment the ones prelisted with Mysql, I get the following error:

```
fatal: unsupported dictionary type: mysql
```

When I emerged PF I ensured that I added MS as a useflag. Has anyone ever had this prob?

----------

## steveb

Could you verify, that you have MySQL support enabled in Postfix?

```
postconf -m
```

If you don't see a line with "mysql", then Postfix does not have MySQL support and you need to remerge Postfix. If you see "mysql", then could you please post the output of:

```
postconf -n
```

and maybe the output of

```
equery u postfix
```

cheers

SteveB

----------

## trossachs

```
postconf -m
```

Mysql confirmed

```
postconf -n

alias_database = hash:/etc/postfix/databases/aliases.cf

alias_maps = $alias_database

biff = yes

bounce_size_limit = 500000

command_directory = /usr/sbin

config_directory = /etc/postfix

content_filter = smtp-amavis:[127.0.0.1]:10024

daemon_directory = /usr/libexec/postfix

daemon_timeout = 1800000s

debug_peer_level = 2

disable_dns_lookups = no

hash_queue_names = deferred, defer active bounce flush incoming

header_size_limit = 500000

home_mailbox = .maildir/

local_destination_concurrency_limit = 6

mail_spool_directory = /var/spool/mail

mailbox_command = /usr/bin/procmail

mailq_path = /usr/bin/mailq.postfix

manpage_directory = /usr/share/man

masquerade_domains = hash:/etc/postfix/databases/masquerade.cf

maximal_queue_lifetime = 15d

message_size_limit = 51200000

mime_header_checks = regexp:/etc/postfix/filters/mime_checks.cf

minimal_backoff_time = 300s

mydestination = $myhostname $mydomain localhost.$mydomain www.$mydomain ftp.$mydomain /etc/postfix/databases/my_destination.cf

mydomain = foo.co.uk

myhostname = box.foo.co.uk

mynetworks = hash:/etc/postfix/databases/network_table.cf

myorigin = $mydomain

newaliases_path = /usr/bin/newaliases.postfix

queue_run_delay = 320s

readme_directory = /usr/share/doc/postfix-1.1.12/README_FILES

relayhost = [smtp.eclipse.co.uk]

sample_directory = /usr/share/doc/postfix-1.1.12/samples

sendmail_path = /usr/sbin/sendmail.postfix

service_throttle_time = 60s

setgid_group = postdrop

smtp_data_init_timeout = 300s

smtp_data_xfer_timeout = 400s

smtp_host_lookup = dns, native

smtp_mail_timeout = 600s

smtp_quit_timeout = 500s

smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination

smtpd_timeout = 600s

soft_bounce = no

strict_rfc821_envelopes = yes

transport_retry_time = 120s

trigger_timeout = 15s

unknown_local_recipient_reject_code = 450

virtual_alias_maps = hash:/etc/postfix/databases/virtual_table.cf

virtual_gid_maps = static:5000

virtual_mailbox_base = /home/vhosts

virtual_mailbox_domains = hash:/etc/postfix/databases/virtual_domains.cf

virtual_mailbox_maps = hash:/etc/postfix/databases/virtual_mailbox.cf

virtual_minimum_uid = 100

virtual_transport = virtual

virtual_uid_maps = static:5000

```

----------

## kashani

 *JulesF wrote:*   

> 
> 
> ```
> 
> alias_database = hash:/etc/postfix/databases/aliases.cf
> ...

 

I'd suspect that you need to swap hash with mysql in each line above. You'd not accessing a data hash file, but making a mysql call. 

```

virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf

```

If you add proxy:mysql: like I did Postfix will mux db queries over the same db connection which will result in less db connections. Using less db connections is good in a large mail system.

kashani

----------

## kashani

And you have some very wacky setting in there for various defaults. The ones that jumped out at me was 15d for the queue to timeout when the default is 5d and daemon_timeoute which you have set to 1800000s instead of the default 18000s. Also I see that you have references to Postfix 1.1 in your config... are you really running Postfix 1.1 or have you never fully updated your main.cf.

kashani

----------

## trossachs

Kashani, all the hash calls which are listed there were put back because Mysql did not work. Having reverted back to the old setting, I have begun to call up the hash commands to keep the server running.

And yes you are quite correct, as I have upgraded PF, I did not change the main.cf file in its entirety.

Am running postfix 2.2.2-r1.

----------

## kashani

I'd think about taking the main.cf 2.2 generated and using that as your base. 2.2 is pretty different especially when it comes to db calls. Here's the PostfixAdmin pages on the difference between db calls in 2.2 and 2.1 or ealier.

http://postfixwiki.org/index.php?title=Virtual_Users_and_Domains_with_Courier-IMAP_and_MySQL#mysql_virtual_alias_maps.cf

kashani

----------

## steveb

 *JulesF wrote:*   

> Kashani, all the hash calls which are listed there were put back because Mysql did not work. Having reverted back to the old setting, I have begun to call up the hash commands to keep the server running.
> 
> And yes you are quite correct, as I have upgraded PF, I did not change the main.cf file in its entirety.
> 
> Am running postfix 2.2.2-r1.

 Wow! You did not changed main.cf but are runing 2.2.x of Postfix?

Could you post the content of the *.cf files, where you have the MySQL statements for the lookup (please don't post the password filed. We don't need that).

cheers

SteveB

----------

## trossachs

Why the surprise SteveB?

----------

## steveb

 *JulesF wrote:*   

> Why the surprise SteveB?

 Because the ebuild normaly creates a ._cfg000._xxx files and if you do a etc-update, then it will show you the difference. Ignoring those differences is not the best thing you could do to your Gentoo box  :Wink: 

Anyway... could you please post your main.cf, master.cf and the other *.cf files where you use MySQL lookups?

Another issue/question: What GCC version did you use, when you compiled Postfix? I ask this, because when I compile Postfix with GCC >= 3.4.0, then Postfix is going crazy. But compiling it with < GCC-3.4.0 everything works as expected (currently I run Postfix 2.2.5 with MySQL 4.1.14 integration).

cheers

SteveB

----------

## trossachs

```
gcc v3.3.6
```

Main.cf is listed above in an earlier post, master.cf listed below:

```
# ==========================================================================

# service type  private unpriv  chroot  wakeup  maxproc command + args

#               (yes)   (yes)   (yes)   (never) (100)

# ==========================================================================

smtp      inet  n       -       n       -       -       smtpd

#submission inet n      -       n       -       -       smtpd

#       -o smtpd_etrn_restrictions=reject

#       -o smtpd_client_restrictions=permit_sasl_authenticated,reject

#smtps    inet  n       -       n       -       -       smtpd

#  -o smtpd_tls_wrappermode=yes -o smtpd_sasl_auth_enable=yes

#submission   inet    n       -       n       -       -       smtpd

#  -o smtpd_etrn_restrictions=reject

#  -o smtpd_enforce_tls=yes -o smtpd_sasl_auth_enable=yes

#628      inet  n       -       n       -       -       qmqpd

pickup    fifo  n       -       n       60      1       pickup

cleanup   unix  n       -       n       -       0       cleanup

qmgr      fifo  n       -       n       300     1       qmgr

#qmgr     fifo  n       -       n       300     1       oqmgr

tlsmgr    unix  -       -       n       1000?   1       tlsmgr

rewrite   unix  -       -       n       -       -       trivial-rewrite

bounce    unix  -       -       n       -       0       bounce

defer     unix  -       -       n       -       0       bounce

trace     unix  -       -       n       -       0       bounce

verify    unix  -       -       n       -       1       verify

flush     unix  n       -       n       1000?   0       flush

proxymap  unix  -       -       n       -       -       proxymap

smtp      unix  -       -       n       -       -       smtp

# When relaying mail as backup MX, disable fallback_relay to avoid MX loops

relay     unix  -       -       n       -       -       smtp

        -o fallback_relay=

#       -o smtp_helo_timeout=5 -o smtp_connect_timeout=5

showq     unix  n       -       n       -       -       showq

error     unix  -       -       n       -       -       error

discard   unix  -       -       n       -       -       discard

local     unix  -       n       n       -       -       local

virtual   unix  -       n       n       -       -       virtual

lmtp      unix  -       -       n       -       -       lmtp

anvil     unix  -       -       n       -       1       anvil

scache    unix  -       -       n       -       1       scache

#

# ====================================================================

# Interfaces to non-Postfix software. Be sure to examine the manual

# pages of the non-Postfix software to find out what options it wants.

#

# Many of the following services use the Postfix pipe(8) delivery

# agent.  See the pipe(8) man page for information about ${recipient}

# and other message envelope options.

# ====================================================================

#

# maildrop. See the Postfix MAILDROP_README file for details.

# Also specify in main.cf: maildrop_destination_recipient_limit=1

#

maildrop  unix  -       n       n       -       -       pipe

  flags=DRhu user=vmail argv=/usr/local/bin/maildrop -d ${recipient}

#

# The Cyrus deliver program has changed incompatibly, multiple times.

#

old-cyrus unix  -       n       n       -       -       pipe

  flags=R user=cyrus argv=/cyrus/bin/deliver -e -m ${extension} ${user}

# Cyrus 2.1.5 (Amos Gouaux)

# Also specify in main.cf: cyrus_destination_recipient_limit=1

cyrus     unix  -       n       n       -       -       pipe

  user=cyrus argv=/cyrus/bin/deliver -e -r ${sender} -m ${extension} ${user}

#

# See the Postfix UUCP_README file for configuration details.

#

uucp      unix  -       n       n       -       -       pipe

  flags=Fqhu user=uucp argv=uux -r -n -z -a$sender - $nexthop!rmail ($recipient)

#

# Other external delivery methods.

#

ifmail    unix  -       n       n       -       -       pipe

  flags=F user=ftn argv=/usr/lib/ifmail/ifmail -r $nexthop ($recipient)

bsmtp     unix  -       n       n       -       -       pipe

  flags=Fq. user=foo argv=/usr/local/sbin/bsmtp -f $sender $nexthop $recipient

smtp-amavis unix -      -       n       -       3  smtp

        -o smtp_data_done_timeout=1200

        -o smtp_send_xforward_command=yes

        -o disable_dns_lookups=yes

        -o max_use=20

127.0.0.1:10025 inet n  -       n       -       -  smtpd

        -o content_filter=

        -o local_recipient_maps=

        -o relay_recipient_maps=

        -o smtpd_restriction_classes=

        -o smtpd_delay_reject=no

        -o smtpd_client_restrictions=permit_mynetworks,reject

        -o smtpd_helo_restrictions=

        -o smtpd_sender_restrictions=

        -o smtpd_recipient_restrictions=permit_mynetworks,reject

        -o mynetworks_style=host

        -o mynetworks=127.0.0.0/8

        -o strict_rfc821_envelopes=yes

        -o smtpd_error_sleep_time=0

        -o smtpd_soft_error_limit=1001

        -o smtpd_hard_error_limit=1000

        -o smtpd_client_connection_count_limit=0

        -o smtpd_client_connection_rate_limit=0

        -o receive_override_options=no_header_body_checks,no_unknown_recipient_checks

```

The Mysql files are listed below:

mysql-virtual_domains.cf

```
user = foo

password = password

dbname = postfix

table = domains

select_field = 'virtual'

where_field = domain

hosts = 127.0.0.1

```

mysql-virtual_email2email.cf

```
user = foo

password = password

dbname = postfix

table = users

select_field = email

where_field = email

hosts = 127.0.0.1

```

mysql-virtual_forwardings.cf

```
user = foo

password = password

dbname = postfix

table = forwardings

select_field = destination

where_field = source

hosts = 127.0.0.1

```

mysql-virtual_mailboxes.cf

```
user = foo

password = password

dbname = postfix

table = users

select_field = CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')

where_field = email

hosts = 127.0.0.1

```

I got this from a howto on the net.

----------

## steveb

If you are using the 2.2.x series of Postfix, then your MySQL files should look like this:

mysql-virtual_domains.cf

```
user = foo

password = password

dbname = postfix

query = SELECT virtual FROM domains WHERE domain='%s'

hosts = localhost
```

mysql-virtual_email2email.cf

```
user = foo

password = password

dbname = postfix

query = SELECT email FROM users WHERE email='%s'

hosts = localhost
```

mysql-virtual_forwardings.cf

```
user = foo

password = password

dbname = postfix

query = SELECT destination FROM forwardings WHERE source='%s'

hosts = localhost
```

mysql-virtual_mailboxes.cf

```
user = foo

password = password

dbname = postfix

query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'

hosts = localhost
```

cheers

SteveB

----------

