From: Bruce Momjian <bruce@momjian.us>
To: Ponnam, Bhavani <Bhavani.Ponnam@Stryker.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] start/restart PostgreSQL service with SSL enabled
Date: Sun, 28 Jan 2018 08:58:38 -0500
Message-ID: <20180128135838.GA1731@momjian.us> (raw)
In-Reply-To: <DM5PR01MB2731BE4DE42A15FC48D5B53983700@DM5PR01MB2731.prod.exchangelabs.com>
References: <DM5PR01MB2731BE4DE42A15FC48D5B53983700@DM5PR01MB2731.prod.exchangelabs.com>
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...
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 +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bruce@momjian.us, Bhavani.Ponnam@Stryker.com
Subject: Re: [ADMIN] start/restart PostgreSQL service with SSL enabled
In-Reply-To: <20180128135838.GA1731@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox