2

I set the following parameters to enable SSL on my PostgreSQL server:

  • ssl = True
  • ssl_ca_file = 'path-to-root-ca'
  • ssl_cert_file = 'path-to-server-crt'
  • ssl_key_file = 'path-to-server-key'

For my tests, I use the PGSSLROOTCERT variable which contains the path to the root ca certificate.

I realized that it is still possible for a client to connect to the database without providing the root CA certificate and that the prompt states that an SSL connection is used:

psql "postgres://user:XXXXXX@hostname:port/postgres"
psql (10.2, server 10.3 (Debian 10.3-1.pgdg90+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q

psql "postgres://user:XXXXXX@hostname:port/postgres?sslmode=verify-ca"
psql (10.2, server 10.3 (Debian 10.3-1.pgdg90+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \q

If the PGSSLROOTCERT variable is set to a wrong value:

psql "postgres://user:XXXXXX@hostname:port/postgres?sslmode=verify-ca"
psql: SSL error: certificate verify failed

The pg_hba.conf file:

local  all  postgres    trust
hostssl  all  all  0.0.0.0/0  md5
1

In the absence of PGSSLROOTCERT or sslrootcert, the library libpq (which psql uses) looks in a compiled-in default location for the file. And apparently, it finds it at that location.

For Linux, that location is ~/.postgresql/root.crt. Although it is possible that "Debian 10.3-1.pgdg90+1" patched it to look someplace else instead.

  • Thanks for your response. My client is a computer running under MacOS: there isn't such directoy as ~/.postgresql. – TheWhiteWolf Apr 22 '18 at 9:55
  • Ah of course, Debian refers to the server, not the client. What I would do then is run something like strace -eopen psql ... and see what files it is actually opening. (Although I don't know if MacOS has strace, it probably has something similar) – jjanes Apr 22 '18 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.