请求提供Windows及CentOS环境下PostgreSQL SSL连接测试步骤

Got it, let's walk through how to test PostgreSQL SSL connections on both Windows and CentOS step by step. I'll cover server configuration, certificate setup (if you don't have existing ones), client testing, and how to confirm the connection is actually using SSL.

Step 1: Check if PostgreSQL supports SSL #

First, verify your PostgreSQL instance has SSL enabled. Log into the PostgreSQL shell as a superuser:

sudo -u postgres psql

Then run this command to check SSL status:

SHOW ssl;

If it returns on, you're good to go. If not, you might need to reinstall PostgreSQL with SSL support (most package-managed installs like from yum/dnf include this by default).

Step 2: Configure PostgreSQL for SSL #

Edit the postgresql.conf file (location varies by version, usually /var/lib/pgsql/<your-version>/data/postgresql.conf):

sudo vi /var/lib/pgsql/14/data/postgresql.conf

Find and uncomment/set these lines:

ssl = on
ssl_cert_file = '/var/lib/pgsql/14/data/server.crt'  # Path to your server certificate
ssl_key_file = '/var/lib/pgsql/14/data/server.key'    # Path to your server private key
ssl_ca_file = '/var/lib/pgsql/14/data/root.crt'       # Optional, if using a CA-signed cert

Next, edit pg_hba.conf to allow SSL connections. Add this line (adjust the IP range and auth method to match your needs):

hostssl all all 0.0.0.0/0 scram-sha-256  # Allow remote SSL connections with scram auth
# Or for local testing only:
# hostssl all all 127.0.0.1/32 trust

Step 3: Generate self-signed certificates (if you don't have CA-signed ones) #

If you're testing with self-signed certs, use OpenSSL to generate them:

openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=<your-server-hostname-or-IP>"

Move the certs to the PostgreSQL data directory and set correct permissions:

sudo mv server.crt server.key /var/lib/pgsql/14/data/
sudo chown postgres:postgres /var/lib/pgsql/14/data/server.crt /var/lib/pgsql/14/data/server.key
sudo chmod 600 /var/lib/pgsql/14/data/server.key  # Critical: private key must be read-only by postgres

Step 4: Restart PostgreSQL #

Apply the changes by restarting the service:

sudo systemctl restart postgresql-14

Step 5: Test the SSL connection #

From a client (local or remote), connect with SSL required:

psql "host=<server-IP-or-hostname> dbname=<your-db> user=<your-user> sslmode=require"

Once connected, verify SSL is being used:

SELECT ssl_is_used();  -- Returns 't' if SSL is active
-- For more details:
SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid();

Step 1: Verify SSL support #

Open the PostgreSQL shell (psql) from the Start Menu, then run:

SHOW ssl;

If it returns on, you're ready. If not, reinstall PostgreSQL with the SSL option checked (the default installer includes this).

Step 2: Configure PostgreSQL for SSL #

Navigate to your PostgreSQL data directory (usually C:\Program Files\PostgreSQL\<version>\data). Open postgresql.conf in Notepad or a text editor, then uncomment/set these lines:

ssl = on
ssl_cert_file = 'server.crt'  # Relative path, or use absolute like 'C:/Program Files/PostgreSQL/14/data/server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'       # Optional for CA-signed certs

Edit pg_hba.conf to allow SSL connections. Add this line:

hostssl all all 0.0.0.0/0 scram-sha-256
# Or local testing:
# hostssl all all 127.0.0.1/32 trust

Step 3: Generate self-signed certificates #

If you don't have certs, use OpenSSL (install Git for Windows to get OpenSSL, or download it separately). Open Git Bash or a command prompt with OpenSSL in PATH, then run:

openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=<your-windows-hostname-or-IP>"

Copy server.crt and server.key to the PostgreSQL data directory. Then set permissions:

1. Right-click each file → Properties → Security tab
2. Click Edit → Add → Enter postgres (the PostgreSQL service user) → Check Names → OK
3. Give the postgres user Read permissions → Apply → OK

Step 4: Restart PostgreSQL #

Open the Services manager (press Win+R, type services.msc):

• Find the PostgreSQL service (e.g., postgresql-x64-14)
• Right-click → Restart

Step 5: Test the SSL connection #

Open Command Prompt or PowerShell, then run:

psql "host=localhost dbname=<your-db> user=<your-user> sslmode=require"

Once connected, verify SSL is active with the same queries as CentOS:

SELECT ssl_is_used();
SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid();

Quick Notes on SSL Modes #

• sslmode=require: Forces SSL connection, but doesn't verify the certificate (good for testing)
• sslmode=verify-ca: Verifies the certificate was signed by a trusted CA
• sslmode=verify-full: Verifies the CA and that the certificate's hostname matches the server you're connecting to

For self-signed certs with verify-ca/verify-full, copy your root.crt to:

• CentOS: ~/.postgresql/root.crt
• Windows: %APPDATA%\postgresql\root.crt

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