configuring openssl for postgres 9.2 for the first time

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 <http://www.autorevenue.com/> | Autobase<http://www.autobase.net/>

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 <http://www.autorevenue.com/>

<http://autobasedigital.net/marketing/DD12_sig.jpg>