Re: configuring openssl for postgres 9.2 for the first time

Hi Lou, thanks for response!

I tried your suggestion to create and test a 10.10.4.34 role on the client
and got the same error when attempted to access server
thru that role.

I think you hit the nail on the head though when you suggested my client is
not SSL enabled.
Here is my attempt to make the client ssl enabled and then establishing a
trusted CA certificate. I'm attempting to
follow the procedure set forth in the postgres docs.

Here is what I've attempted:

MY ATTEMPT TO CREATE A CA CERTIFICATE ON CLIENT
AND MAKE IT SSL-ENABLED

1. logged into client 10.10.4.34
in home root directory:
1a. mkdir .postgresql
1b. cd .postgresql
1c. mkdir private

2. openssl req -config /etc/pki/tls/openssl.cnf
-new -x509 -keyout private/cakey.pem -out cacert.pem -days 1000
3. openssl x509 -in cacert.pem -out postgresql.crt

4. scp postgresql.crt postgres(at)10(dot)10(dot)4(dot)52:/data/PSQL_9.2/root.crt

I've attached the various postgresql.crt and pem files as well as the
pg_hba.conf file used on the server.
in addition to the root.crt copied to the data directory mentioned in point
4 above, I earlier created
the server.crt and server.key also in the data directory as outlined in my
first post.

I'm sure I've missed something, probable user error. Any other help
appreciated

On Fri, Jan 31, 2014 at 2:01 PM, Lou Picciano <loupicciano(at)comcast(dot)net>wrote:

> 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(dot)steben(at)drivedominion(dot)com>
> To: pgsql-admin(at)postgresql(dot)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
>
>
>
>
>

--
*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>