1

I am not sure this topic will fit here but I don't know ask it where.

I am trying to open the SSL of PostgreSQL 10.16 on Windows10.

I read a lot of documents about creating an SSL certificate to PostgreSQL but documents spend on Windows 10 are rare and don't detail.

These are the steps which I did:

Step 1, I download the OpenSSL version of Windows from

https://slproweb.com/products/Win32OpenSSL.html

and install it with the path C:\OpenSSL-Win64, setting system variable.

Step 2, I use cmd run as an admin to create server key with command line:

genrsa -out server.key 4096

, setting appropriate permission and owner on the private key file (here https://stackoverflow.com/a/51463654)

icacls server.key /reset

icacls server.key /inheritance:r /grant:r "CREATOR OWNER:F"

and I got a response from cmd.exe in this command-line

C:\WINDOWS\system32>icacls server.key /reset
processed file:  server.key
Successfully processed 1 file; Failed processing 0 files

C:\WINDOWS\system32>icacls server.key /inheritance:r /grant:r "CREATOR OWNER:F"
processed file:  server.key
Successfully processed 1 files; Failed processing 0 files

continue to create the server certificate:

req -new -x509 -days 1826 -key server.key -out server.crt

Step 3, since I am self-signing, I use the server certificate as the trusted root certificate, so I have 3 files: server.key, server.crt, and root.crt (this is a copy of server crt)

I move these three files to C:\Program Files\PostgreSQL\10\data

Step 4, I am setting postgresql.conf:

listen_addresses = '*'
port = 5432
ssl=on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

and add command-line to the end of pg_hba.conf:

# IPv4 remote connections for authenticated users
hostssl   all     postgres             0.0.0.0/0            md5 clientcert=1

Finally, I get an error as below when I restart my PostgreSQL

The PostgreSQL -x64-10 -PostgreSQL Server 10 service on the Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

and in my log of PostgreSQL:

2021-03-28 16:35:44.735 +07 [7624] LOG:  database system was shut down at 2021-03-28 16:34:51 +07

2021-03-28 16:35:45.099 +07 [7044] LOG:  database system is ready to accept connections

2021-03-28 17:39:37.827 +07 [7044] LOG:  received fast shutdown request

2021-03-28 17:39:37.834 +07 [7044] LOG:  aborting any active transactions

2021-03-28 17:39:37.839 +07 [7044] LOG:  worker process: logical replication launcher (PID 7972) exited with exit code 1

2021-03-28 17:39:37.843 +07 [7880] LOG:  shutting down

2021-03-28 17:39:37.877 +07 [7044] LOG:  database system is shut down

I suspect PostgreSQL did not read the 3 files that I put in its data directory.

I have referenced these documents

https://www.howtoforge.com/postgresql-ssl-certificates

https://stackoverflow.com/questions/43959324/chmod-og-rwx-server-key-in-windows/51463654#51463654

https://www.postgresql.org/docs/10/ssl-tcp.html

I have been messing with it for many days and I don't know how to solve this problem.

4
  • are you sure that your version has ´With SSL support compiled in` else compile it yoirself
    – nbk
    Mar 28 at 14:29
  • @nbk I am not sure. How to know my version is SSL support compiled?
    – Pham
    Mar 28 at 14:47
  • see the chapter 16 postgresql.org/docs/10/installation.html i don't know how to che4ck postgres it the support is inclued, hopefully another user can tell you that, but i seems to me as you must complie it your self
    – nbk
    Mar 28 at 14:54
  • Thanks for your reminder, maybe I was too in a hurry and forgot this.
    – Pham
    Mar 28 at 15:02

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.