1

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.

  • 1
    Using TLS doesn't gain you anything on 127.0.0.1, so you might as well allow SYSTEM in without it. – gowenfawr Dec 19 '14 at 12:20
  • Wouldn't TLS help prevent an unauthorized process from sniffing or acting as Man in the Middle or spoofing as the System? – harpagornis Dec 19 '14 at 16:24
  • 1
    An authorized process with the privileges required to sniff has the privileges required to scrape memory, trace your process, assume SYSTEM, etc. etc. So the extra hardening of local "network" doesn't really add much. – gowenfawr Dec 19 '14 at 17:47
  • My sentence reads "unauthorized" process. – harpagornis Dec 19 '14 at 18:29
  • 1
    My sentence meant "unauthorized" :). TLS is one layer. You're trying to use it where it isn't necessary, and you're going to have problems because the system has expectations. If you'd like to bang your head against the wall, feel free, just don't do it under the illusion you're actually improving your security in any meaningful way. – gowenfawr Dec 19 '14 at 21:01

Your Answer

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

Browse other questions tagged or ask your own question.