Thread: 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 issuefrom 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
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)
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
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
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
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 addthis 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-enabledclient, 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 ourinfrastructure 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
On Jan 30, 2014, at 2:00 PM, Mark Steben <mark.steben@drivedominion.com> wrote:
when attempting to access from a client to a remote postgres server after SSL configuration:Hello,We are looking to provide openssl methodology into our testing environment. I've run into this issue
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
You might back off from ssl, client authentication just to see what happens with:
hostssl all all 0.0.0.0/0 md5 clientcert=1
this will provide the client auth of the server and require a password auth for the client. Hopefully that works first. I've seen your msg and had some effect with the following env variable, but it's probably a long shot:
"PGSSLMODE behaves the same as the sslmode"
PGSSLMODE=verify-full will cause the client to verify that the CN on the server certificate matches the hostname of the server. disable will only try a non-SSL connection which will not be compatible with the pg_hba config.
It is a bit of a fishing expedition.
Attachment
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 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@10.10.4.52:/data/PSQL_9.2/root.crt
On Fri, Jan 31, 2014 at 2:01 PM, Lou Picciano <loupicciano@comcast.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'.
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
--
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
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
Attachment
Hi Ray,
I just tried your suggestion:hostssl all all 0.0.0.0/0
md5 clientcert=1
and got the same error:
no pg_hba.conf entry for host "10.10.4.34", user "postgres", database "marktst", SSL off
no pg_hba.conf entry for host "10.10.4.34", user "postgres", database "marktst", SSL off
perhaps if I can get some insight as to how to determine what sslmode, (if any) my client
is subscribed to, then I can follow through further with Ray's recommendation.
thanks for any help,
On Fri, Jan 31, 2014 at 5:48 PM, Ray Stell <stellr@vt.edu> wrote:
On Jan 30, 2014, at 2:00 PM, Mark Steben <mark.steben@drivedominion.com> wrote:when attempting to access from a client to a remote postgres server after SSL configuration:Hello,We are looking to provide openssl methodology into our testing environment. I've run into this issue
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 offYou might back off from ssl, client authentication just to see what happens with:hostssl all all 0.0.0.0/0 md5 clientcert=1this will provide the client auth of the server and require a password auth for the client. Hopefully that works first. I've seen your msg and had some effect with the following env variable, but it's probably a long shot:"PGSSLMODE behaves the same as the sslmode"PGSSLMODE=verify-full will cause the client to verify that the CN on the server certificate matches the hostname of the server. disable will only try a non-SSL connection which will not be compatible with the pg_hba config.It is a bit of a fishing expedition.
--
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
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