configuring openssl for postgres 9.2 for the first time

[Mark Steben <mark.steben@xxxxxxxxxxxxxxxxx>]

Hello,

We are looking to provide openssl methodology into our testing environment.  I've run into this issue

when attempting to access from a client to a remote postgres server after SSL configuration:

from client 10.10.4.34:
psql -U postgres marktst -h 10.10.4.52
psql: FATAL:  no pg_hba.conf entry for host "10.10.4.34", user "postgres", database "marktst", SSL off

Here are the steps I've taken trying to follow postgresql 9.2 docs sections 17.9 and 30.17:

on CLIENT (10.10.4.34)

 I. Created a 'self-signed' certificate (in home directory /home/postgres/.postgresql:)

   A. openssl  req -new -text -out postgresql.req  (create request)

     ***NOTE - the 'common name' I entered in when prompted was the ip address 10.10.4.34 ***

   B. 1. openssl rsa -in privkey.pem -out postgresql.key

       2. rm privkey.com (these two steps to remove the passphrase from certificate)

   C. 1. openssl  req -x509 -in postgresql.req -text -key postgresql.key -out postgresql.crt

       2. chmod 600 postgresql.key (to generate package and renounce 'world authority')

2. secure copied postgresql.crt to the 9.2 data directory in server 10.10.4.52.  The name I copied
        to was root.crt

on SERVER (10.10.4.52)

  I. Created a 'self signed' certificate

     A. openssl req -new -text -out server.req

        ***NOTE - the 'common name' entered when prompted was ip address 10.10.4.52

     B. 1. openssl rsa -in privkey.pem -out server.key

         2. rm privkey.pem (to remove passphrase from certificate)

     C. 1. openssl req -x509 -in server.req -text -key server.key -out.server.crt

         2. chmod 600 serverkey

  II. Copied server.key and server.crt to the data directory

  III  re-installed postgres from source using config option --with-openssl (along with make, make
              install)

 IV. made the following changes to postgresql, pg.hba.conf files and restarted server

    A. postgresql.conf

        1.  ssl = on

        2.  ssl_ca_file = root.crt

        3. ssl_cert_file = server.crt

        4. uncommented ssl_ciphers to ensure all the defaults allowed

        5. ssl_key_file = server.key

    B. pg_hba.conf

        1. added one line:
                    hostssl  all                all          0.0.0.0/0             cert clientcert=1

I can login locally as postgres as I have a local entry in pg_hba.conf.

Any insight appreciated.  thank you,

Mark Steben 
 
 Database Administrator
@utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com