Date: Fri, 31 Jan 2014 19:01:31 +0000 (UTC)
From: Lou Picciano <loupicciano@comcast.net>
To: pgsql-admin  <pgsql-admin@postgresql.org>
Cc: Mark Steben <mark.steben@drivedominion.com>
Message-ID: 
 <1676386945.897876.1391194891512.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net>
In-Reply-To: 
 <CADyzmyxGJpVNBVALfJPHtLtcO=RMpxbGPPUu8QetFKOFqZnxtw@mail.gmail.com>
Subject: Re: configuring openssl for postgres 9.2 for the first time
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Hello Mark:

Cursory review? Looks like this line in you pg_hba.conf will cause the server to demand a 'login' name of '10.10.4.34' -- the 'Common Name' of the cert you're presenting. but you're trying to login as 'postgres'.

hostssl  all                all          0.0.0.0/0             cert clientcert=1

The six-ticket ride, just for fun? Try adding the ROLE 10.10.4.34, with login privs, of course, to your cluster. Then add this line to pg_hba.conf: 

hostssl  all     "10.10.4.34"     0.0.0.0/0             cert clientcert=1

Also, check that your log reports the server _first_ trying the SSL connection. If not, you may not be using an SSL-enabled client, a requirement. (Do you have other lines in pg_hba.conf? These may be in play...) 

...and welcome to The Joys of Cert Authentication on PostgreSQL. The Good News? It works great! (It's at the core of our infrastructure here).

Lou Picciano


----- Original Message -----
From: "Mark Steben" <mark.steben@drivedominion.com>
To: pgsql-admin@postgresql.org
Sent: Thursday, January 30, 2014 2:00:53 PM
Subject: [ADMIN] 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 | 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 


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin