# Not sure how to interpret this postmap error



## nathanvy (Feb 21, 2022)

I'm following along with the workaround.org tutorial for setting up a mailserver.  Previously I'd been running dovecot+postfix+postgres for 3-4 years successfully on Debian, but I thought I'd test drive FreeBSD.  I am currently stuck on trying to set up postfix's virtual aliases.  I created the following file:


```
# cat pgsql-virtual-mailbox-domains.cf
user = mailuser
password = supersecret
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_domains WHERE name='%s'
```

If I attempt this query manually, it works:


```
# psql -U mailuser mailserver
psql (14.1)
Type "help" for help.

mailserver=> select 1 from virtual_domains where name='mydomain.tld';
 ?column?
----------
        1
(1 row)
```

So I inserted that line into my postfix config and tried to run a sample query, but it didn't work and I don't know how to debug what's going on.  Googling the error message has not produced anything of value:


```
# postconf virtual_mailbox_domains=proxy:pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf
# service postfix reload
postfix/postfix-script: refreshing the Postfix mail system
# postmap -v -q example.org proxy:pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf
postmap: name_mask: ipv4
postmap: name_mask: host
postmap: inet_addr_local: configured 2 IPv4 addresses
postmap: been_here: <scrubbed>/32: 0
postmap: been_here: 127.0.0.1/32: 0
postmap: mynetworks_core: <scrubbed>/32 127.0.0.1/32
postmap: connect to subsystem /var/spool/postfix/private/proxymap
postmap: /var/spool/postfix/private/proxymap socket: wanted attribute: protocol
postmap: input attribute name: protocol
postmap: input attribute value: proxymap_protocol
postmap: /var/spool/postfix/private/proxymap socket: wanted attribute: (list terminator)
postmap: input attribute name: (end)
postmap: send attr request = open
postmap: send attr table = pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf
postmap: send attr flags = 0
postmap: /var/spool/postfix/private/proxymap socket: wanted attribute: status
postmap: input attribute name: status
postmap: input attribute value: 0
postmap: /var/spool/postfix/private/proxymap socket: wanted attribute: flags
postmap: input attribute name: flags
postmap: input attribute value: 524385
postmap: /var/spool/postfix/private/proxymap socket: wanted attribute: (list terminator)
postmap: input attribute name: (end)
postmap: dict_proxy_open: connect to map=pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf status=0 server_flags=warn_dup|pattern|lock|utf8_request
postmap: dict_open: proxy:pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf
postmap: send attr request = lookup
postmap: send attr table = pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf
postmap: send attr flags = 524353
postmap: send attr key = example.org
postmap: /var/spool/postfix/private/proxymap socket: wanted attribute: status
postmap: input attribute name: status
postmap: input attribute value: 2
postmap: /var/spool/postfix/private/proxymap socket: wanted attribute: value
postmap: input attribute name: value
postmap: input attribute value: (end)
postmap: /var/spool/postfix/private/proxymap socket: wanted attribute: (list terminator)
postmap: input attribute name: (end)
postmap: dict_proxy_lookup: table=pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf flags=warn_dup|lock|utf8_request key=example.org -> status=2 result=
postmap: fatal: table proxy:pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf: query error: Application error
```

I'm completely stumped.  Can anyone shed some light on what's going on?  I get the same query error/application error irrespective of which domain I try to query.  Help!


----------



## nathanvy (Feb 22, 2022)

Okay thanks to the postfix-users mailing list I've determined this is because the version of postfix that's packaged does not support postgresql.

I reinstalled from the ports tree and now I'm hitting a charset encoding error:


```
# postmap -v -q example.org pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf
postmap: name_mask: ipv4
postmap: name_mask: host
postmap: inet_addr_local: configured 2 IPv4 addresses
postmap: been_here: <redacted>/32: 0
postmap: been_here: 127.0.0.1/32: 0
postmap: mynetworks_core: <redacted>/32 127.0.0.1/32
postmap: cfg_get_str: /usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf: user = mailuser
postmap: cfg_get_str: /usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf: password = <redacted>
postmap: cfg_get_str: /usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf: dbname = mailserver
postmap: cfg_get_str: /usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf: result_format = %s
postmap: cfg_get_int: /usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf: expansion_limit = 0
postmap: cfg_get_str: /usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf: query = SELECT 1 FROM virtual_domains WHERE name='%s'
postmap: cfg_get_str: /usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf: domain =
postmap: cfg_get_str: /usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf: hosts = 127.0.0.1
postmap: dict_open: pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf
postmap: dict_pgsql_get_active: attempting to connect to host 127.0.0.1
postmap: dict_pgsql: successful connection to host 127.0.0.1
postmap: warning: dict_pgsql: cannot set the encoding to LATIN1, skipping 127.0.0.1
postmap: fatal: table pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf: query error: Application error
```

My postgres database is set to use UTF8 so I'm not sure why it's trying to set encoding to LATIN1, and once again googling the error message is not returning anything fruitful.

I'm getting very close to giving up and going back to Linux where all this stuff just worked the first try without any fuss, but I'll give it one more shot.


----------



## nathanvy (Feb 23, 2022)

Welp, as I write this post, Debian is being installed on my server.  The only way to get the FreeBSD port of postfix to speak UTF8 to Postgresql appears to be manually editing the source files and recompiling postfix, which is not something I have time nor motivation to do.  It's just too much work, and this is only package 1 of 3+.  Who knows how much mangling dovecot will require?

Deploying a server like this is supposed to enable me to be more productive; the server is supposed to be a means to an end.  Wrestling with the server is not supposed to be the main activity.

Dear FreeBSD, I gave you the good ol' college try, but my time is too valuable to spend fscking around with manually editing ports, when I know Debian exists and this setup "Just Works" (tm) since I have it running on a different server right now.

Goodbye FreeBSD, I can't decide if I'll miss you or not.

If anyone finds their way here via google, they might find the following links illustrative:  
https://marc.info/?l=postfix-users&m=164556610702287&w=2




__





						'Re: Patch: Encoding problem when talking to postgresql' - MARC
					





					marc.info


----------

