Skip to content

Pgadmin Ssl Cert

DodaTech 2 min read

In this tutorial, you'll learn about Fix pgAdmin SSL Certificate Errors. We cover key concepts, practical examples, and best practices.

You configure PostgreSQL for SSL, generate certificates, and try to connect from pgAdmin. Instead of a secure session, you get "SSL error: certificate verify failed" or "connection to server at [...] failed: no pg_hba.conf entry".

Wrong ❌

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

# pg_hba.conf
hostssl all all 0.0.0.0/0 md5

In pgAdmin, you create a server with SSL mode set to require and nothing else. Connection fails with:

FATAL:  no pg_hba.conf entry for host "192.168.1.50", user "postgres", database "postgres", SSL on

You enable SSL but pg_hba still says md5 for SSL connections — it should be scram-sha-256 or cert.

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'            # often overlooked
ssl_min_protocol_version = 'TLSv1.3'

# pg_hba.conf
hostssl all all 0.0.0.0/0 scram-sha-256
# Or for client-cert auth:
hostssl all all 0.0.0.0/0 cert clientcert=1

In pgAdmin server registration → SSL tab:

  • SSL mode: verify-ca or verify-full
  • Client certificate: <a href="/databases/postgresql/">postgresql</a>.crt
  • Client key: <a href="/databases/postgresql/">postgresql</a>.key
  • Root CA: root.crt

After restarting PostgreSQL:

sudo systemctl restart postgresql

Now pgAdmin shows a green connected status and the query tool runs securely.

Root Cause

Three typical SSL failures: (1) pg_hba.conf not updated to use hostssl or a cert-compatible auth method; (2) missing ssl_ca_file prevents chain validation; (3) pgAdmin's SSL mode set to require skips certificate verification, causing a mismatch when pg_hba demands cert.

Prevention

  • Always set ssl_ca_file even if you don't use client certificates — it validates the server cert chain.
  • Use verify-full in pgAdmin to ensure the server hostname matches the certificate CN.
  • Test SSL from psql: psql "sslmode=verify-full host=..." before touching pgAdmin.
  • Keep certificates on a rotation schedule (< 1 year for production).

Common Mistakes with ssl cert

  1. Non-exhaustive pattern matches that compile with warnings then crash at runtime
  2. Misunderstanding that String is [Char] with poor performance for large text operations
  3. Using foldl instead of foldl' causing stack overflow on large lists

These mistakes appear frequently in real-world PGADMIN code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ

**Q: Do I need a CA-signed certificate or can I use self-signed?**

A: Self‑signed works fine. Add the self‑signed CA to pgAdmin's Root CA field.

**Q: Why does pgAdmin say "SSL connection required" after I enabled it?**

A: Change host to hostssl in pg_hba.conf — the first matching rule wins. If a host rule matches before hostssl, SSL isn't enforced.

**Q: Does the server key need a password?**

A: PostgreSQL cannot prompt for a passphrase on startup, so ssl_key_file must be unencrypted (openssl rsa -in server.key -out server_nopass.key).

**Q: Can I use SSL with a connection pooler like PgBouncer?**

A: Yes — configure SSL on PgBouncer and set server_tls_sslmode in its config.

Hardening PostgreSQL security is covered in detail in the [DodaTech PostgreSQL Security course](https://dodatech.com/courses/postgresql-security).

← Previous Pandas Dataframe Merge Next → Phpmyadmin Blowfish Secret

Built by the developers of DodaTech

Doda Browser, DodaZIP & Durga Antivirus Pro

Home Browse Quick Fix