We currently use pg_service.conf and certificates to log into our databases. Users have their own ~/.postgresql/postgresql.[crt|key] and everything is happy.
We are testing a cloud based postgres which is requiring password based authentication, however our existing certificates are causing the cloud server to reject SSL connections.
[woodring@ibeam]$ psql -U woodring -h host1 "dbname=mydb sslmode=require"
psql: SSL error: tlsv1 alert unknown ca
I can make it work from the command line:
[woodring@ibeam]$ psql -U woodring -h host1 "dbname=mydb sslmode=require sslcert= sslkey="
Password for user woodring:
psql (9.5.13, server 9.6.3)
WARNING: psql major version 9.5, server major version 9.6.
Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
mydb=> \q
If I put the blanks into pg_service.conf:
[mydb]
dbname=mydb
host=host1
sslmode=require
sslcert=
sslkey=
It does not work.
[woodring@ibeam]$ PGSERVICE=mydb psql
psql: SSL error: tlsv1 alert unknown ca
I tried the opposite of moving the .postgresql directory to a different name and putting a hard coded certificate path in pg_service, but it looks to have its own sets of challenges.
Thanks for any suggestions.
George Woodring