Setup postgresql server/client with self signed certs

psql-ssl.sh

# Instructions on how to setup secure postgresql server <-> client communication on two different machines.

# Set SERVER_HOSTNAME and CONNECTION_USER appropriately. Instructions are for Fedora 29, may need to be modified for other platforms

# Set DAYS to the number of days which you'd like certificates to be valid (after this time new client certificates will need to be created)

# General guide can be found here:
# https://www.howtoforge.com/postgresql-ssl-certificates

###

# Install an initialize postgres:
  dnf install postgresql-server
  postgresql-setup initdb
  cd /var/lib/pgsql/data

# Create server key (use any passphrase)
  openssl genrsa -des3 -out server.key 2048

# Remove passphrase
  openssl rsa -in server.key -out server.key

# Set permissions
  chmod 400 server.key
  chown postgres.postgres server.key

# Create server certificates (good for 10 years)
  openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/CN=SERVER_HOSTNAME'
  chmod 400 server.crt
  chown postgres.postgres server.crt
  cp server.crt root.crt

# Edit pg_hba.conf, add the following for each client which will connect to server:
  hostssl all             all            	<IP ADDR>/32      md5 clientcert=1

# Edit postgresql.conf to make following changes:
  listen_addresses = '<IP SERVER WILL LISTEN ON>'
  ssl = on
  ssl_ca_file = 'root.crt'

# Optionally change the following for performance purposes:
  shared_buffers = 4096MB
  work_mem = 64MB
  checkpoint_timeout = 60min

# Start postgres:
  service postgresql start
  
# Open firewall port (note: on other systems, zone will probably be different, perhaps "public")
  firewall-cmd --permanent --zone=FedoraServer --add-rich-rule='
    rule family="ipv4"
    source address="<CLIENT IP ADDR>/32"
    port protocol="tcp" port="5432" accept'
    
  firewall-cmd --reload

# On Client Machine:

# Create client key:
  mkdir .postgresql
  cd .postgresql
  openssl genrsa -des3 -out postgresql.key 2048
  openssl rsa -in postgresql.key -out postgresql.key
  
# Note: when renewing cert, follow guide from this point on down  
  
# Create client certificate request:
  openssl req -new -key postgresql.key -out postgresql.csr -subj '/CN=CONNECTION_USER'

# Copy to server
  scp postgresql.csr SERVER_HOSTNAME:~/

# Back on the Server Machine
# Create client certificate
  openssl x509 -req -days DAYS -in postgresql.csr -CA /var/lib/pgsql/data/root.crt -CAkey /var/lib/pgsql/data/server.key -out postgresql.crt -CAcreateserial

# Copy to client and root certs client machine:
  scp SERVER_HOSTNAME:~/postgresql.crt ~/.postgresql
  scp SERVER_HOSTNAME:/var/lib/pgsql/data/root.crt ~/.postgresql
  
# Set permissions:
   chmod 600 postgresql.*
  
# Try connecting:
  dnf install postgresql
  psql -UCONNECTION_USER -hSERVER_HOSTNAME