PostgreSQL – SSL connection without providing the root-ca certificate

postgresqlssl

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

Best Answer

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.

Related Question