Postgresql 9.x Streaming Repilication on FreeBSD – Part II SSL

Configuring SSL for Postgresql:

Setting up SSL on Postgresql is quite simple.  Our requirements are to enforce SSL Encryption on all Postgresql database connections.  To that end, the creation of self-signed certificates will not be explored in this post.

 

Edit /usr/local/pgsql/postgresql.conf:

Change the following parameters:

ssl = on                                # (change requires restart)
ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'       # allowed SSL ciphers 
                                        # (change requires restart)
ssl_cert_file = 'ldap1cert.pem'         # (change requires restart)
ssl_key_file = 'ldap1key.pem'           # (change requires restart)
ssl_ca_file = 'cacert.pem'              # (change requires restart)

 

The location of the various SSL Server Certificates are relative to the data directory, and thus, need to be place in the directory /usr/local/pgsql/data/

 

Edit /usr/local/pgsql/pg_hba.conf:

Change the following parameters to match your environment:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
hostssl all             all             192.168.0.0/24          md5
 

 

Connect to Postgresql with PgAdmin3:

If you have not installed PgAdmin3, please visit their URL at www.pgadmin.org to obtain the necessary source or binary files.

Opening PgAdmin3 the following screen should appear:

 

pgadmin1

 

 

The first step in connecting to a postgresql database is to “Add Server” under the File Menu and add your server details: 

 

pgadmin2

 

 

Ensure to select the SSL Tab and choose “require” from the pull down menu:

 

pgadmin3

 

 

Upon completing the information and choosing OK, PgAdmin3 will connect to the database if successful and look similar to the image below:

 

pgadmin4

 

Summary:

The postgresql server is fully configured and ready for use with SSL.  In Part III, streaming replication is demonstrated.

 

Reference:

www.postgresql.org

www.pgadmin.org

Leave a Reply

Your email address will not be published.