When PostgreSQL is configured to require client certificates (via clientcert=1 in pg_hba.conf), you'll encounter the error "FATAL: connection requires a valid client certificate" if you don't properly provide one during connection. This is common in production environments where mutual TLS authentication is enforced.

To properly authenticate with psql using client certificates, you need to specify three key files:

psql "sslmode=verify-full \
user=dev \
host=db.prod \
sslcert=/path/to/client.crt \
sslkey=/path/to/client.key \
sslrootcert=/path/to/ca.crt"

• sslcert: Path to your client certificate (PEM format)
• sslkey: Path to your private key (PEM format)
• sslrootcert: Path to the CA certificate that signed the server's certificate
• sslmode: Should be verify-full or verify-ca for production

For frequent connections, create a ~/.pg_service.conf file:

[db_prod]
host=db.prod
user=dev
sslmode=verify-full
sslcert=/path/to/client.crt
sslkey=/path/to/client.key
sslrootcert=/path/to/ca.crt

Then connect using: psql service=db_prod

If you encounter issues, add these environmental variables before running psql:

export PGSSLMODE=verify-full
export PGSSLROOTCERT=/path/to/ca.crt
export PGSSLCERT=/path/to/client.crt
export PGSSLKEY=/path/to/client.key

For more verbose output, use: export PGSSLMODE=verify-full PGSSLDEBUG=1

PostgreSQL is strict about private key permissions. Ensure your key file has:

chmod 600 /path/to/client.key

And is owned by the connecting user.

If your certificate is in PKCS#12 format (.p12), first convert it:

openssl pkcs12 -in client.p12 -out client.crt -nodes -clcerts
openssl pkcs12 -in client.p12 -out client.key -nodes -nocerts

When connecting to a PostgreSQL server that requires client certificate authentication, simply using sslmode=require isn't enough. The error FATAL: connection requires a valid client certificate occurs because psql needs explicit paths to your client certificate files.

To successfully authenticate with a client certificate, you'll need to specify these parameters:

sslcert=/path/to/client.crt
sslkey=/path/to/client.key
sslrootcert=/path/to/root.crt  # Only if using a custom CA

Here's how to structure your psql command with all required SSL parameters:

psql "sslmode=require \
      user=dev \
      host=db.prod \
      sslcert=/etc/postgresql/client.crt \
      sslkey=/etc/postgresql/client.key \
      sslrootcert=/etc/postgresql/root.crt"

PostgreSQL is strict about file permissions:

• Certificate files should be readable by your user
• Private key (.key) must have 600 permissions
• All files should be owned by the connecting user

For convenience, you can set these as environment variables:

export PGSSLCERT=/path/to/client.crt
export PGSSLKEY=/path/to/client.key
export PGSSLROOTCERT=/path/to/root.crt
psql "sslmode=require user=dev host=db.prod"

If you still encounter problems:

# Verify certificate chain
openssl verify -CAfile root.crt client.crt

# Check certificate expiration
openssl x509 -in client.crt -noout -dates

# Test connection without psql
openssl s_client -connect db.prod:5432 -cert client.crt -key client.key -CAfile root.crt

For production environments, consider using ~/.pg_service.conf:

[dbprod]
host=db.prod
user=dev
sslmode=require
sslcert=/path/to/client.crt
sslkey=/path/to/client.key
sslrootcert=/path/to/root.crt

Then connect with: psql service=dbprod