I need help understanding some basics about the postgres database server and SSL. I am developing a Windows desktop application that will connect to a postgres database, in single user mode, with both client and server on one machine, using 127.0.0.1.
Alternatively, the app could be installed on a network drive, allowing multiple users and connections. Either way, I would like all connections with SSL on in verify-full mode.
This is what I have in pg_hba.conf:
# TYPE DATABASE USER ADDRESS METHOD hostssl all all 127.0.0.1/32 cert clientcert=1 hostssl all all ::1/128 cert clientcert=1
In Windows 7, the postgres service starts automatically when I boot the machine, but I get the following error in pg_log file.
FATAL: connection requires a valid client certificate no pg_hba.conf entry for host "127.0.0.1", user "SYSTEM", database "postgres", SSL off
I think I understand the reason for this error. It is because a Windows startup SYSTEM process is attempting to connect to the postgres database and I do not have a postgres user id named SYSTEM or a client certificate for such user. I get this message in pg_log also when I restart postgres through Component Services.
As I said, I want all connections done with SSL on. So, should I create a user named SYSTEM in postgres along with a related client certificate for that user so that Windows can always connect as SYSTEM with the SYSTEM client certificate?
Is it necessary for the Windows SYSTEM process to connect first to the postgres database, maybe for housekeeping purposes, before my desktop app can connect to the application database using the application's user id and corresponding client certificate(s)?
I have also read about the feature in postgres for the pg_service.conf file. Is that something that would be of use for these two different types of connections, to the postgres database and to my application database? Thank you in advance.