Re: [ADMIN] start/restart PostgreSQL service with SSL enabled

On Thu, Oct  5, 2017 at 07:43:10PM +0000, Ponnam, Bhavani wrote:
> Hi,
> 
>       I currently have PostgreSQL 9.5 installed on a Windows 10 machine. I have
> enabled SSL and configured key & crt files with pass phrase. I know that I have
> to enter the PEM pass phrase everytime I try to restart. So I tried using
> pg_ctl to start Postgres, but I have few issues:
> 
>  1. Since I installed Postgres as Windows service, running pg_ctl does not
>     start my service. I see postgresql exe instances running in Task manager
>     but my windows service never gets updated to Running.
>  2. After running the following command:
> 
> pg_ctl -D "c:\Program Files\PostgreSQL\9.5\data" restart -w
> 
> I was able to enter the PEM pass phrase since I got a prompt and was able to
> connect. But when my application is trying to establish the connection with
> Postgresql, the application is waiting since there are prompts on the command
> windows where I executed the pg_ctl command to enter the PEM pass phrase. I
> think this makes the application totally unusable.
> 
>  3. Per our security requirements, we cannot remove the PEM pass phrase, so how
>     do I control the PostgreSQL service – start & restart with the pass phrase
>     and without user interaction.

Sorry to be coming to this very late.  I have an answer for you, but I
don't think you are going to like it.

A discussion on the topic from a year ago is here:

    https://www.postgresql.org/message-id/flat/29982.1483412575%40sss.pgh.pa.us#29982.1483412575@sss.pgh.pa.us

The crux of the discussion is this:

    Before we leave this area, though, there is a loose end that
    requires more thought.    That is, what about passphrase-protected
    server keys?  Our documentation suggests that if you have one,
    the server will demand the passphrase just once at server start
    and then all is good.  I'm not sure if that's at all practical
    in modern usage, but in any case it's not going to be reasonable
    to put a passphrase in again at every SIGHUP.  On Windows things
    are even worse; you'd have to give the passphrase again
    to every spawned backend.  (But that was true already.)

Technically, on Unix-like systems, we load the password-protected SSL
private key once on server start and keep it for all backends that are
created via fork().  On Windows, we have to use CreateProcess(), and we
have no mechnism to pass the SSL private key to each new backend. 
(There was brief discussion of using a pipe for this, but no code.)

The discussion generated this patch:

    https://www.postgresql.org/message-id/E1cOpbx-0003PR-Kx@gemulon.postgresql.org

where it adds this text to our documentation:

    Using a passphrase also disables the ability to change the
    server's SSL configuration without a server restart.  Furthermore,
-->    passphrase-protected private keys cannot be used at all on Windows.

Basically, passphrase-protected private keys have never worked on
Windows because of CreateProcess(), but now it is documented.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +