我有运行 PostgreSQL 9.4 的 postfix 3.0.3。我的 main.cf 在此处：
broken_sasl_auth_clients = yes
inet_protocols = ipv4
local_recipient_maps = $virtual_mailbox_maps
mail_owner = postfix
mailbox_command = /usr/local/libexec/dovecot/deliver
mydestination = localhost
mynetworks = 192.0.0.0/8 127.0.0.0/8
mynetworks_style = subnet
relay_domains = pgsql:/usr/local/etc/postfix/pgsql/pgsql_relay_domains.cf
relay_recipient_maps = pgsql:/usr/local/etc/postfix/pgsql/pgsql_virtual_mailbox_maps.cf
setgid_group = maildrop
smtp_tls_note_starttls_offer = yes
smtp_use_tls = yes
smtpd_delay_reject = yes
smtpd_helo_required = yes
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
smtpd_sasl_auth_enable = yes
smtpd_sasl_path = /var/run/dovecot/auth-client
smtpd_sasl_security_options = noanonymous
smtpd_sasl_type = dovecot
smtpd_sender_restrictions = permit_sasl_authenticated, permit_mynetworks
smtpd_tls_CAfile = /usr/local/etc/postfix/ssl/smtpd.pem
smtpd_tls_cert_file = /usr/local/etc/postfix/ssl/smtpd.pem
smtpd_tls_key_file = /usr/local/etc/postfix/ssl/smtpd.pem
smtpd_tls_loglevel = 3
smtpd_tls_received_header = yes
smtpd_tls_session_cache_timeout = 3600s
smtpd_use_tls = no
tls_random_source = dev:/dev/urandom
unknown_local_recipient_reject_code = 550
virtual_alias_domains = pgsql:/usr/local/etc/postfix/pgsql/pgsql_virtual_mailbox_domains.cf
virtual_alias_maps = pgsql:/usr/local/etc/postfix/pgsql/pgsql_virtual_alias_maps.cf
virtual_gid_maps = static:1001
virtual_mailbox_base = /home/vmail
virtual_mailbox_domains = example.com
virtual_mailbox_maps = pgsql:/usr/local/etc/postfix/pgsql/pgsql_virtual_mailbox_maps.cf
virtual_minimum_uid = 1001
virtual_transport = dovecot
virtual_uid_maps = static:1001
请注意，virtual_mailbox_domains设置为example.com。一旦我将其设置为virtual_mailbox_domains = pgsql:/usr/local/etc/postfix/pgsql/pgsql_virtual_mailbox_domains.cf，我就会不断收到此错误消息
Dec 25 01:56:51 postfix/smtpd[11184]: NOQUEUE: reject: RCPT from mail-lb0-f174.google.com[209.85.217.174]: 454 4.7.1 <[email protected]>: Relay access denied; from=<[email protected]> to=<[email protected]> proto=ESMTP helo=<mail-lb0-f174.google.com>
然而，每当我跑步时，我都会得到postmap -q [email protected] pgsql:/usr/local/etc/postfix/pgsql/pgsql_virtual_mailbox_domains.cfexample.com
pgsql_virtual_mailbox_domains.cf看起来像这样：
user = vmail
password = qwerty
hosts = /tmp
dbname = vmail
query = SELECT split_part(username,'@',2) FROM users WHERE username = '%s'
我究竟做错了什么？
答案1
你能试一下吗
SELECT split_part(username,'@',2) FROM users WHERE split_part(username,'@',2) = '%s'?
因为 Postfix 在查询时应该发送域名而不是电子邮件地址virtual_mailbox_domains。
另外，您不必split_part在每个查询中执行两次，而是可以为域名创建一个列并对该列进行索引，以获得更好的性能。
希望这可以帮助。
答案2
如果virtual_mailbox_domainsPostfix 需要域列表，它不会查询地图（特别是不使用完整的电子邮件地址）。我对 Postgres 数据库了解不多，但我假设您的查询应该是这样的：
SELECT DISTINCT split_part(username, '@', 2) FROM users;
为了获得最佳性能，请考虑将域放在另一个表中，与用户分开。