2

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 cut 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

chmod og-rwx server.key in windows

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.

Pham
  • 21
  • 1
  • Try this: https://stackoverflow.com/questions/41481463/i-cant-run-postgresql-server-after-i-change-ssl-on-in-postgresql-conf-in-wind – Cao Minh Tu Aug 21 '22 at 09:53

0 Answers0