Habilitar conexoes SSL ao PG

• Generate a private key (you must provide a passphrase).

openssl genrsa -des3 -out server.key 1024

• Remove the passphrase.

openssl rsa -in server.key -out server.key

• Create the server certificate. -subj is a shortcut to avoid prompting for the info. -x509 produces a self signed certificate rather than a certificate request.

openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=thebrain.ca/emailAddress=info@thebrain.ca'

• Since we are self-signing, we use the server certificate as the trusted root certificate.

cp server.crt root.crt

• Set appropriate permission and owner on the files.

chmod 400 server.key
chown postgres.postgres server.key server.crt root.crt

• You'll need to edit pg_hba.conf. For example:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust

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

• You need to edit postgresql.conf to actually activate ssl:

ssl = on
ssl_ca_file = '/LOCATION_OF_FILE/root.crt'
ssl_cert_file = '/LOCATION_OF_FILE/server.crt'
ssl_key_file = '/LOCATION_OF_FILE/server.key'

• Restart Postgresql server.