postgresql ssl server client root certs demo

gistfile1.txt

https://www.youtube.com/watch?v=FWK3lR6bSn8
https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
https://www.postgresql.org/docs/current/ssl-tcp.html
https://www.cybertec-postgresql.com/en/setting-up-ssl-authentication-for-postgresql/
https://luppeng.wordpress.com/2021/08/07/create-and-install-ssl-certificates-for-postgresql-database-running-locally/
https://www.alibabacloud.com/blog/599116


pgbouncer
https://www.crunchydata.com/blog/improving-pgbouncer-security-with-tlsssl

also patroni has examples on haproxy ssl
https://github.com/zalando/patroni/blob/a8cfd46801c8de7bdfb0b68835148124da0db4f5/Dockerfile.citus

postgres@pg:~/citusdb/certs$
postgres@pg:~/citusdb/certs$ mkdir CA
postgres@pg:~/citusdb/certs$ cd CA
postgres@pg:~/citusdb/certs/CA$ openssl genrsa  -out rootCA.key 2048
postgres@pg:~/citusdb/certs/CA$ ls -lrt
total 4
-rw------- 1 postgres postgres 1704 Mar 27 15:25 rootCA.key
postgres@pg:~/citusdb/certs/CA$ openssl req -x509 -new -key rootCA.key -days 1000 -out rootCA.crt
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:
Email Address []:
postgres@pg:~/citusdb/certs/CA$ ls -lrt
total 8
-rw------- 1 postgres postgres 1704 Mar 27 15:25 rootCA.key
-rw-rw-r-- 1 postgres postgres 1245 Mar 27 15:26 rootCA.crt
postgres@pg:~/citusdb/certs/CA$ cd ../
postgres@pg:~/citusdb/certs$ mkdir server
postgres@pg:~/citusdb/certs$ cd server/
postgres@pg:~/citusdb/certs/server$ openssl genrsa -out server.key 2048
postgres@pg:~/citusdb/certs/server$ openssl req -x509 -new -key server.key -out server.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:pg.mshome.net
Email Address []:
postgres@pg:~/citusdb/certs/server$ openssl x509 -req -in server.csr -CA ../CA/rootCA.crt -CAKey ../CA/rootCA.key -CAcreateserial -out server.crt -days 500
x509: Use -help for summary.
postgres@pg:~/citusdb/certs/server$ openssl x509 -req -in server.csr -CA ../CA/rootCA.crt -CAkey ../CA/rootCA.key -CAcreateserial -out server.crt -days 500
Unable to load certificate request input
4097F87D837F0000:error:0480006C:PEM routines:get_name:no start line:../crypto/pem/pem_lib.c:763:Expecting: CERTIFICATE REQUEST
postgres@pg:~/citusdb/certs/server$ ls -lrt
total 8
-rw------- 1 postgres postgres 1704 Mar 27 15:27 server.key
-rw-rw-r-- 1 postgres postgres 1310 Mar 27 15:29 server.csr
postgres@pg:~/citusdb/certs/server$ openssl x509 -req -in server.csr ^CA ../CA/rootCA.crt -CAkey ../CA/rootCA.key -CAcreateserial -out server.crt -days 500
postgres@pg:~/citusdb/certs/server$ ls -lrt
total 8
-rw------- 1 postgres postgres 1704 Mar 27 15:27 server.key
-rw-rw-r-- 1 postgres postgres 1310 Mar 27 15:29 server.csr
postgres@pg:~/citusdb/certs/server$ rm server.*
postgres@pg:~/citusdb/certs/server$ openssl genrsa -out server.key 2048                                                     postgres@pg:~/citusdb/certs/server$ openssl req -new -key server.key -out server.csr                                        You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:pg.mshome.net
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
postgres@pg:~/citusdb/certs/server$ openssl x509 -req -in server.csr -CA ../CA/rootCA.crt -CAkey ../CA/rootCA.key -CAcreateserial -out server.crt -days 500
Certificate request self-signature ok
subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = pg.mshome.net
postgres@pg:~/citusdb/certs/server$ cd ..
postgres@pg:~/citusdb/certs$ mkdir client
postgres@pg:~/citusdb/certs$ cd client/
postgres@pg:~/citusdb/certs/client$ openssl genrsa -out client.key 2048
postgres@pg:~/citusdb/certs/client$ openssl req -new -key client.key -out client.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:ubuntu
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
postgres@pg:~/citusdb/certs/client$ openssl x509 -req -in client.csr -CA ../CA/rootCA.crt -CAkey ../CA/rootCA.key -CAcreateserial -out client.crt -days 500
Certificate request self-signature ok
subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = ubuntu
postgres@pg:~/citusdb/certs/client$ cd
postgres@pg:~$ cp ~/citusdb/certs/CA/rootCA.crt .
postgres@pg:~$ cp ~/citusdb/certs/server/server.key .
postgres@pg:~$ cp ~/citusdb/certs/server/server.crt .

postgres@pg:~$ chmod 600 server.
server.crt  server.key
postgres@pg:~$ chmod 600 server.
server.crt  server.key
postgres@pg:~$ chmod 600 server.* rootCA.crt
postgres@pg:~$ cd /tmp/
postgres@pg:/tmp$ rm -rf ~/.postgresql/*
postgres@pg:/tmp$ initdb -D db1
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory db1 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Kolkata
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D db1 -l logfile start

postgres@pg:/tmp$ cp ~/server.
server.crt  server.key
postgres@pg:/tmp$ cp ~/server.* db1/
postgres@pg:/tmp$ cp ~/rootCA.crt db1/
postgres@pg:/tmp$ cd db1


postgres@pg:/tmp$ pg_ctl -D db1 -l db1.log start
waiting for server to start.... done
server started
postgres@pg:/tmp$ openssl x509 -in ~/citusdb/certs/client/client.crt -noout -subject -nameopt RFC2253 | sed "s/^subject=//"
CN=ubuntu,O=Internet Widgits Pty Ltd,ST=Some-State,C=AU
postgres@pg:/tmp$ psql
psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1), server 16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.

postgres=# create role ubuntu login password '123';
CREATE ROLE
postgres=# create database ubuntu owner ubuntu;
CREATE DATABASE
postgres=#
\q
postgres@pg:/tmp$ sudo su - postgres
postgres@pg:~$ psql -h 172.31.214.66  --set=sslmode=verify^C
postgres@pg:~$
logout
postgres@pg:/tmp$ sudo su - ubuntu
ubuntu@pg:~$ mkdir ~/.postgresql
ubuntu@pg:~$ sudo su - postgres
postgres@pg:~$ pwd
/var/lib/postgresql
postgres@pg:~$
logout
ubuntu@pg:~$ cd ~/.postgresql
ubuntu@pg:~/.postgresql$ cp /var/lib/postgresql/
ubuntu@pg:~/.postgresql$ sudo cp /var/lib/postgresql/citusdb/certs/CA/rootCA.crt root.crt
ubuntu@pg:~/.postgresql$ sudo cp /var/lib/postgresql/citusdb/certs/client/client.crt ubuntu.crt
ubuntu@pg:~/.postgresql$ ls -lrt
total 8
-rw-r--r-- 1 root root 1245 Mar 27 15:51 root.crt
-rw-r--r-- 1 root root 1147 Mar 27 15:52 ubuntu.crt
ubuntu@pg:~/.postgresql$ sudo chown ubuntu:ubuntu *
ubuntu@pg:~/.postgresql$ chmod 0600 *
ubuntu@pg:~/.postgresql$ ls -lrt
total 8
-rw------- 1 ubuntu ubuntu 1245 Mar 27 15:51 root.crt
-rw------- 1 ubuntu ubuntu 1147 Mar 27 15:52 ubuntu.crt
ubuntu@pg:~/.postgresql$ sudo cp /var/lib/postgresql/citusdb/certs/client/client.key ubuntu.key
ubuntu@pg:~/.postgresql$ sudo chown ubuntu:ubuntu *
ubuntu@pg:~/.postgresql$ chmod 0600 *
ubuntu@pg:~/.postgresql$ psql -h ^C
ubuntu@pg:~/.postgresql$ psql -h 172.31.214.66 -U ubuntu
psql: error: connection to server at "172.31.214.66", port 5432 failed: FATAL:  connection requires a valid client certificate
connection to server at "172.31.214.66", port 5432 failed: FATAL:  no pg_hba.conf entry for host "172.31.214.66", user "ubuntu", database "ubuntu", no encryption
ubuntu@pg:~/.postgresql$ ls -lrt
total 12
-rw------- 1 ubuntu ubuntu 1245 Mar 27 15:51 root.crt
-rw------- 1 ubuntu ubuntu 1147 Mar 27 15:52 ubuntu.crt
-rw------- 1 ubuntu ubuntu 1704 Mar 27 15:52 ubuntu.key
ubuntu@pg:~/.postgresql$    psql "sslmode=verify-ca sslrootcert=server-ca.pem \
      sslcert=client-cert.pem sslkey=client-key.pem \
      hostaddr=INSTANCE_IP_ADDRESS \
      user=postgres dbname=DB_NAME"
   ^C
ubuntu@pg:~/.postgresql$ psql "sslmode=verify-ca sslrootcert=root.crt sslcert=ubuntu.crt sslkey=ubuntu.key hostaddr=172.31.214.66 user=ubuntu dbname=ubuntu"
psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1), server 16.2 (Ubuntu 16.2-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

ubuntu=>
\q


ubuntu=> select * from pg_stat_ssl ;
-[ RECORD 1 ]-+---------------------------------------------------------
pid           | 2670
ssl           | t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
client_dn     | /C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=ubuntu
client_serial | 247943288920497466161063302944676715560125203755
issuer_dn     | /C=AU/ST=Some-State/O=Internet Widgits Pty Ltd



--------------------------------------------------------------------------------------------

  519  openssl ecparam -name prime256v1 -genkey -noout -out ca.key
  520  openssl req -new -x509 -sha256 -key ca.key -out ca.crt -subj "/CN=pg.mshome.net"
  522  openssl ecparam -name prime256v1 -genkey -noout -out server.key
  523  openssl req -new -sha256 -key server.key -out server.csr -subj "/CN=127.0.0.1"
  524  openssl x509 -req -in server.csr -CA ../CA/ca.crt  -CAkey ../CA/ca.key -CAcreateserial -out server.crt -days 365 -sha256
  540  openssl ecparam -name prime256v1 -genkey -noout -out client.key
  541  openssl req -new -sha256 -key client.key -out client.csr -subj "/CN=postgres"
  542  openssl x509 -req -in client.csr -CA ../CA/ca.crt -CAkey ../CA/ca.key -CAcreateserial -out client.crt -days 365 -sha256
  554  history | grep openssl
  559  openssl ecparam -name prime256v1 -genkey -noout -out server.key
  560  openssl req -new -sha256 -key server.key -out server.csr -subj "/CN=172.18.16.178"
  561  openssl x509 -req -in server.csr -CA ../CA/ca.crt  -CAkey ../CA/ca.key -CAcreateserial -out server.crt -days 365 -sha256

we need to connect via CN for ssl verify, so if cert is for 172 then 127 will fail and otherwise.
with verify-ca, only ca needs to be verified, we need to create ssl client cert for each pg user

postgres@pg:~/certs$ find CA
CA
CA/ca.key
CA/ca.crt
postgres@pg:~/certs$ find server
server
server/server.key
server/server.crt
server/server.csr
postgres@pg:~/certs$ find client/
client/
client/client.key
client/client.csr
client/client.crt
postgres@pg:~/certs$ grep ssl db1/postgresql.conf
ssl = on
ssl_ca_file = '/var/lib/postgresql/certs/CA/ca.crt'
ssl_cert_file = '/var/lib/postgresql/certs/server/server.crt'
#ssl_crl_file = ''
#ssl_crl_dir = ''
ssl_key_file = '/var/lib/postgresql/certs/server/server.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off
postgres@pg:~/certs$ grep ssl db1/pg_hba.conf
# hostssl       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl     DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# - "hostssl" is a TCP/IP socket that is SSL-encrypted
# - "hostnossl" is a TCP/IP socket that is not SSL-encrypted
#hostssl  all             all             127.0.0.1/32            trust clientcert=verify-full
hostnossl all all 0.0.0.0/0 reject
hostssl   all all 127.0.0.1/32 trust clientcert=verify-ca
hostssl   all all 0.0.0.0/0 trust clientcert=verify-full





postgres@pg:~/certs$ psql "sslmode=verify-ca sslrootcert=/var/lib/postgresql/certs/CA/ca.crt sslcert=/var/lib/postgresql/certs/client/client.crt sslkey=/var/lib/postgresql/certs/client/client.key host=172.18.16.178 user=postgres dbname=postgres"
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#
\q
postgres@pg:~/certs$ psql "sslmode=verify-ca sslrootcert=/var/lib/postgresql/certs/CA/ca.crt sslcert=/var/lib/postgresql/certs/client/client.crt sslkey=/var/lib/postgresql/certs/client/client.key host=127.0.0.1 user=postgres dbname=postgres"
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#
\q
postgres@pg:~/certs$ psql "sslmode=verify-full sslrootcert=/var/lib/postgresql/certs/CA/ca.crt sslcert=/var/lib/postgresql/certs/client/client.crt sslkey=/var/lib/postgresql/certs/client/client.key host=127.0.0.1 user=postgres dbname=postgres"
psql: error: connection to server at "127.0.0.1", port 5432 failed: server certificate for "172.18.16.178" does not match host name "127.0.0.1"
postgres@pg:~/certs$ psql "sslmode=verify-full sslrootcert=/var/lib/postgresql/certs/CA/ca.crt sslcert=/var/lib/postgresql/certs/client/client.crt sslkey=/var/lib/postgresql/certs/client/client.key host=172.18.16.178 user=postgres dbname=postgres" psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#
\q
postgres@pg:~/certs$ psql "sslmode=verify-full sslrootcert=/var/lib/postgresql/certs/CA/ca.crt sslcert=/var/lib/postgresql/certs/client/client.crt sslkey=/var/lib/postgresql/certs/client/client.crt host=172.18.16.178 user=postgres dbname=postgres"
psql: error: connection to server at "172.18.16.178", port 5432 failed: could not load private key file "/var/lib/postgresql/certs/client/client.crt": PEM lib
postgres@pg:~/certs$ psql "sslmode=verify-full sslrootcert=/var/lib/postgresql/certs/CA/ca.crt sslcert=/var/lib/postgresql/certs/client/client.crt sslkey=/var/lib/postgresql/certs/server/server.key host=172.18.16.178 user=postgres dbname=postgres"
psql: error: connection to server at "172.18.16.178", port 5432 failed: could not load private key file "/var/lib/postgresql/certs/server/server.key": key values mismatch
postgres@pg:~/certs$ psql "sslmode=verify-full sslrootcert=/var/lib/postgresql/certs/CA/ca.crt sslcert=/var/lib/postgresql/certs/server/server.crt sslkey=/var/lib/postgresql/certs/server/server.key host=172.18.16.178 user=postgres dbname=postgres"
psql: error: connection to server at "172.18.16.178", port 5432 failed: FATAL:  "trust" authentication failed for user "postgres"