Secure Sockets Layer (SSL) is a standard security technology for establishing an encrypted link between a server and a client—typically a web server (website) and a browser, or a mail server and a mail client
On my main Server,
openssl genrsa -des3 -out server.key 1024 openssl rsa -in server.key -out server.key chmod og-rwx server.key openssl req -new -key server.key -days 3650 -out server.crt -x509
Note the hostname is host of the main machine above
cp server.crt root.crt openssl genrsa -des3 -out /tmp/postgresql.key 1024 openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr
Note that as above, the name should that of the user to connect other server
openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial
As seen above, I need to copy all the certs to the client machine. Note that the root cert is in the data directory as per my configuration.
Make sure you delete those certs from the /tmp directory after moving it to the client machine.
Update the above information in my postgresql.conf file of master
I make sure I include the client ip address in the pg_hba.conf file of the master as using hostssl.
$ pg_ctl stop $ pg_ctl start
Test SSL connection from client
psql 'host=masterprod port=5444 dbname=edb user=enterprisedb sslmode=verify-full sslcert=/var/lib/edb/as11/data/postgresql.crt sslkey=/var/lib/edb/as11/data/postgresql.key sslrootcert=/var/lib/edb/as11/data/root.crt'
As seen above, I can connect to any database as specified in the pg_hba.conf
If you don’t want to type in the long string, you might export it in the bash_profile. Note this is just a test.
So below is the formula how its supposed to be
$ psql ‘host=<db host name> port=<dbport> dbname=<db name> user=enterprisedb sslmode=verify-full sslcert=<path>/postgresql.crt sslkey=<path>/postgresql.key sslrootcert=<path>/root.crt’ Where: path= the path where certificate and key files were copied on the client. Example:
If you will like another user to use ssl to connect to the database, you will have to specify it as above as with the case with user benson. The user who you specify the connection mode as ‘cert clientcert=1’ must be the user which you included as common user which creating the cert keys.
You can know more about connecting an application using ssl from HERE
You could decide to incorporate md5 and ssl like below
Let me know if this was of help or if you will like to know more.