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@xxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxxxxxxx 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@xxxxxxxxxx> 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 +
