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:
The first step in connecting to a postgresql database is to “Add Server” under the File Menu and add your server details:
Ensure to select the SSL Tab and choose “require” from the pull down menu:
Upon completing the information and choosing OK, PgAdmin3 will connect to the database if successful and look similar to the image below:
Summary:
The postgresql server is fully configured and ready for use with SSL. In Part III, streaming replication is demonstrated.