I am trying to set up postfix virtual domains via postgresql database,
and I have come up against an error I can't resolve. I am running
FreeBSD 13, using postfix and postgresql from the FreeBSD packages.
I created the file pgsql-virtual-mailbox-domains.cf:
# cat pgsql-virtual-mailbox-domains.cf
user = mailuser
password = redacted
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
Additional info below:
# postconf -n
command_directory = /usr/local/sbin
compatibility_level = 3.6
daemon_directory = /usr/local/libexec/postfix
data_directory = /var/db/postfix
debug_peer_level = 2
debugger_command = PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
ddd $daemon_directory/$process_name $process_id & sleep 5
html_directory = /usr/local/share/doc/postfix
inet_protocols = ipv4
mail_owner = postfix
mailq_path = /usr/local/bin/mailq
manpage_directory = /usr/local/man
meta_directory = /usr/local/libexec/postfix
mynetworks_style = host
newaliases_path = /usr/local/bin/newaliases
queue_directory = /var/spool/postfix
readme_directory = /usr/local/share/doc/postfix
sample_directory = /usr/local/etc/postfix
sendmail_path = /usr/local/sbin/sendmail
setgid_group = maildrop
shlib_directory = /usr/local/lib/postfix
unknown_local_recipient_reject_code = 550
virtual_mailbox_domains =
proxy:pgsql:/usr/local/etc/postfix/pgsql-virtual-mailbox-domains.cf
# postconf -Mf
smtp inet n - n - - smtpd
pickup unix n - n 60 1 pickup
cleanup unix n - n - 0 cleanup
qmgr unix n - n 300 1 qmgr
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
proxywrite unix - - n - 1 proxymap
smtp unix - - n - - smtp
relay unix - - n - - smtp
-o syslog_name=postfix/$service_name
showq unix n - n - - showq
error unix - - n - - error
retry 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
postlog unix-dgram n - n - 1 postlogd
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!
N
