Search code examples
postgresqlpg-dump

"PGSSLMODE=allow pg_dump -h host -p 5432 " seems to have no effect on SSL Mode

I'm trying to connect to the remote PostgreSQL server to do a database dump of some tables. I'm using PGSSLMODE=allow pg_dump -h host -p 5432

However it fails everytime with pg_dump: error: connection to database "schema_name" failed: FATAL: SSL connection is required. Please specify SSL options and retry. FATAL: no pg_hba.conf entry for host "x.x.x.x", user "admin", database "schema_name", SSL on

Solution

  • That's working as it should:

    First, it tries an unencrypted connection (allow prefers unencrypted connections). That gives you the FATAL: SSL connection is required from the server.

    Then it tries an encrypted connection, but no rule in pg_hba.conf allows that connection, so you are again rejected.

    Solution: add an appropriate pg_hba.conf line and use SSLMODE=require to attempt only an encrypted connection and avoid the first error message.