CentOS7下Postfix对接PostgreSQL存储中继信息与可接受域配置求助

Hey there! Let's walk through getting Postfix to read your PostgreSQL relay table correctly—since you already have the PostgreSQL-enabled Postfix from CentOS Plus, half the battle is won. Here's a step-by-step breakdown:

1. Configure Postfix to Use PostgreSQL Lookups #

First, you need to tell Postfix to pull relay rules from your PostgreSQL database. Edit your /etc/postfix/main.cf and add/update these lines (adjust based on exactly what you're trying to achieve):

# Define mappings for relay domains and transport rules
relay_domains = pgsql:/etc/postfix/relay_domains_pgsql.cf
transport_maps = pgsql:/etc/postfix/transport_pgsql.cf

• relay_domains: Tells Postfix which domains it's allowed to relay mail for
• transport_maps: Specifies where to send mail for those domains (your relay server)

2. Create PostgreSQL Configuration Files for Postfix #

Next, make the .cf files referenced above. These files tell Postfix how to connect to your PostgreSQL instance and what queries to run.

/etc/postfix/transport_pgsql.cf #

This file fetches the relay server for a given destination domain:

# Database connection details
user = postfix               # PostgreSQL user with read access to your table
password = your_secure_password  # Password for the PostgreSQL user
hosts = 10.x.x.x             # Your PostgreSQL server IP (use 127.0.0.1 if local)
dbname = your_mail_database  # Name of the database containing your table
query = SELECT relay FROM your_table_name WHERE destination = '%s' LIMIT 1;

• %s is a placeholder that Postfix replaces with the destination domain/address from the incoming mail.
• Ensure your relay field stores valid Postfix transport strings, like smtp://relay.example.com:587 or local: for local delivery.

/etc/postfix/relay_domains_pgsql.cf #

This file verifies whether a domain is allowed to be relayed:

user = postfix
password = your_secure_password
hosts = 10.x.x.x
dbname = your_mail_database
query = SELECT 'OK' FROM your_table_name WHERE destination = '%s' LIMIT 1;

If the query returns OK, Postfix will allow relaying for that domain.

3. Optimize Your Database Table (Optional but Recommended) #

Your existing table has the core fields, but you can tweak it for better functionality and performance:

• Expand field lengths: Change destination and relay to varchar(255) or text to accommodate longer domain names/transport strings (including wildcards like *.example.com).
• Add an active flag: Add a boolean column named active (default true) to toggle rules on/off without deleting them. Update your queries to include AND active = true.
• Add indexes: Speed up frequent lookups by indexing the destination column:

  CREATE INDEX idx_destination ON your_table_name (destination);
  

4. Set Up PostgreSQL Permissions #

Make sure the Postfix user can actually read your table. Log into PostgreSQL and run these commands:

-- Create a PostgreSQL user for Postfix (if you haven't already)
CREATE USER postfix WITH PASSWORD 'your_secure_password';
-- Grant read access to your relay table
GRANT SELECT ON your_table_name TO postfix;

Also, update your pg_hba.conf to allow the Postfix server to connect to PostgreSQL. For a local connection, add:

host your_mail_database postfix 127.0.0.1/32 md5

For a remote server, replace 127.0.0.1/32 with your Postfix server's IP range. Then reload PostgreSQL: systemctl reload postgresql-10

5. Test the Configuration #

Before reloading Postfix, verify the database lookups work:

# Test if Postfix can fetch the relay for a domain
postmap -q "example.com" pgsql:/etc/postfix/transport_pgsql.cf
# Test if the domain is allowed for relaying
postmap -q "example.com" pgsql:/etc/postfix/relay_domains_pgsql.cf

If both return expected results, reload Postfix:

systemctl reload postfix

You can use a tool like swaks to send a test email and confirm relaying works:

swaks --to test@example.com --from sender@yourdomain.com --server localhost

Quick Note on MailScanner #

Since MailScanner sits between Postfix and delivery, you don't need to configure it to read this PostgreSQL table directly—Postfix will handle routing the mail to the correct relay after MailScanner finishes filtering. If you want to add additional filtering rules in MailScanner that use PostgreSQL, that's a separate setup, but it doesn't affect the relay configuration we just covered.

内容的提问来源于stack exchange，提问作者Kenneth_H