How to fix SSL error: certificate does not match host name in PostgreSQL

First, find out what hostname is actually in the PostgreSQL server's SSL certificate. If you have access to the server, check the certificate file:

# Usually located at $PGDATA/server.crt
openssl x509 -in /var/lib/postgresql/15/main/server.crt -text -noout | grep -A 1 "Subject Alternative Name"

Or extract the Common Name:

openssl x509 -in /var/lib/postgresql/15/main/server.crt -noout -subject

Output will show something like:
- CN=my-database.example.com
- DNS:my-database.example.com, DNS:*.example.com

Note the exact hostname(s) listed—this is what you must connect with.

Change your connection string to use the exact hostname from the certificate.

Incorrect (using IP or different hostname):

psql -h 192.168.1.50 -U postgres -d mydb -W
# or
psql -h localhost -U postgres -d mydb -W

Correct (using the certificate's hostname):

psql -h my-database.example.com -U postgres -d mydb -W

For applications, update your connection string:

# Before
postgresql://user:[email protected]:5432/mydb?sslmode=verify-full

# After
postgresql://user:[email protected]:5432/mydb?sslmode=verify-full

Ensure that the hostname you're using resolves to the correct IP address:

nslookup my-database.example.com
# or
dig my-database.example.com

Compare the resolved IP address with your server's actual IP. If they don't match, update your DNS records or use a hostname that does resolve correctly.

If you're in a private network or using /etc/hosts, verify the entry:

grep my-database /etc/hosts

Update if needed:

echo "192.168.1.50    my-database.example.com" | sudo tee -a /etc/hosts

Test the connection using psql with verbose output to see exactly what's happening:

psql -h my-database.example.com -U postgres -d mydb -W \
  sslmode=verify-full \
  sslrootcert=/path/to/ca-cert.pem

For Node.js applications using node-postgres or similar libraries:

const client = new Client({
  host: 'my-database.example.com',
  port: 5432,
  user: 'postgres',
  password: 'password',
  database: 'mydb',
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('/path/to/ca-cert.pem', 'utf8'),
  },
});

If you get the error again, the hostname in the connection is still wrong.

Cloud-hosted PostgreSQL services provide a specific endpoint hostname that you must use:

AWS RDS:
- Endpoint: mydb.abc123xyz.us-east-1.rds.amazonaws.com
- Use this hostname exactly as provided
- Download the RDS CA certificate

psql -h mydb.abc123xyz.us-east-1.rds.amazonaws.com -U postgres -d mydb \
  sslmode=verify-full \
  sslrootcert=rds-ca-2023-root.pem

Azure Database for PostgreSQL:
- Endpoint: mydb.postgres.database.azure.com
- SSL is mandatory; obtain the DigiCert certificate from Azure portal

Google Cloud SQL:
- Endpoint: 1.2.3.4:5432 (public IP) or private IP in same VPC
- For SSL, use sslmode=require if IP-based, or sslmode=verify-full if using Cloud Proxy
- Download the server certificate from Cloud Console

Never use IP addresses with sslmode=verify-full on managed services—use their provided hostname.

If you're in development and must connect via IP or alias, you can reduce SSL verification, but this is not recommended for production:

# Verify certificate chain but skip hostname check
psql -h 192.168.1.50 -U postgres -d mydb -W sslmode=verify-ca

# Or, encrypt without verification (least secure)
psql -h 192.168.1.50 -U postgres -d mydb -W sslmode=require

For Node.js:

const client = new Client({
  host: '192.168.1.50',
  ssl: {
    rejectUnauthorized: false, // Skip hostname/chain verification
  },
});

This leaves you vulnerable to man-in-the-middle attacks. Fix the hostname/certificate mismatch instead.