Tls

When implementing secure PostgreSQL connections, certificate creation forms the foundation of your PKI infrastructure. Whether you’re setting up a development environment or deploying enterprise-grade systems, understanding proper certificate creation practices is crucial. This guide explores certificate creation from the simple OpenSSL approach to enterprise-grade practices employed by major financial institutions like Credit Suisse/UBS and media companies like BBC. Related Reading: For implementing DN-based certificate authentication in PostgreSQL, see our DN Authentication guide. ...

Today, I’m diving into Distinguished Name (DN) authentication—a powerful feature that enables certificate-based authentication when the Common Name (CN) in your client certificate doesn’t match your PostgreSQL username. This approach is essential in enterprise environments where certificate naming conventions don’t align with database user naming requirements. Version Compatibility: The clientname=DN feature was introduced in PostgreSQL 14. If you’re using PostgreSQL 13 or earlier versions, this DN authentication method will not work and you’ll need to use traditional CN-based certificate authentication instead. ...

Mutual TLS (mTLS) for PostgreSQL provides strong, passwordless authentication and encryption. The most common cause of failed secure connections in real deployments is incorrect Subject Alternative Name (SAN) handling and trust configuration on the client side. This post explains how to set up SANs correctly, how hostname verification really works, and how to align PostgreSQL with enterprise PKI practices—using this repository’s cluster as a concrete example. Why SAN Matters More Than CN Modern TLS stacks validate the server’s identity against the SAN extension on the server certificate—not the Common Name (CN). When a PostgreSQL client connects to a host name (or IP), it will check: ...

Client certificate authentication in PostgreSQL provides a secure, passwordless way to authenticate users. Instead of relying on passwords, clients present valid X.509 certificates to prove their identity. What to Configure Server-Side Requirements SSL certificates: Server certificate + client certificates PostgreSQL SSL settings: Enable SSL and configure certificate paths Authentication rules: Configure pg_hba.conf for certificate-based auth User mapping: Link certificate Common Names to database users Client-Side Requirements Client certificate: Valid X.509 certificate for the user Private key: Matching private key for the certificate Root certificate: Server’s certificate for verification Connection parameters: Proper SSL mode and certificate paths How to Configure 1. Generate SSL Certificates # Create SSL directory mkdir -p /var/lib/postgresql/17/main/ssl cd /var/lib/postgresql/17/main/ssl # Generate server certificate openssl genrsa -out server.key 4096 openssl req -new -key server.key -out server.csr \ -subj "/C=US/ST=State/L=City/O=Org/OU=OrgUnit/CN=localhost" openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt # Generate client certificate for user 'appuser' openssl genrsa -out appuser.key 4096 openssl req -new -key appuser.key -out appuser.csr \ -subj "/C=US/ST=State/L=City/O=Org/OU=OrgUnit/CN=appuser" openssl x509 -req -days 365 -in appuser.csr \ -CA server.crt -CAkey server.key -CAcreateserial -out appuser.crt # Set permissions chown postgres:postgres *.crt *.key chmod 600 *.key chmod 644 *.crt 2. Configure PostgreSQL SSL Settings Add to postgresql.conf: ...