CONFIGURING SSL AUTHENTIFICATION

by bensonyerima

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.

About the author

bensonyerima

Hi, I'm Benson Yerima, a database administrator with an obsession for all things tech. This blog is dedicated for helping people learn about database technology.

View all posts

10,138 Comments