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@xxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx>
To: pgsql-admin@xxxxxxxxxxxxxx
Sent: Thursday, January 30, 2014 2:00:53 PM
Subject: 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
the following is found in /home/root/.postgresql/postgresql.crt -----BEGIN CERTIFICATE----- MIIEDTCCA3agAwIBAgIJAMVc+XoyW7hAMA0GCSqGSIb3DQEBBQUAMIG2MQswCQYD VQQGEwJVUzEWMBQGA1UECBMNTWFzc2FjaHVzZXR0czETMBEGA1UEBxMKUGl0dHNm aWVsZDEiMCAGA1UEChMZRG9taW5pb24gRGVhbGVyIFNvbHV0aW9uczENMAsGA1UE CxMEdGVjaDEZMBcGA1UEAxMQUm9vdCBDZXJ0aWZpY2F0ZTEsMCoGCSqGSIb3DQEJ ARYdbWFyay5zdGViZW5AZHJpdmVkb21pbmlvbi5jb20wHhcNMTQwMTMxMTgwNDU0 WhcNMTYxMDI3MTgwNDU0WjCBtjELMAkGA1UEBhMCVVMxFjAUBgNVBAgTDU1hc3Nh Y2h1c2V0dHMxEzARBgNVBAcTClBpdHRzZmllbGQxIjAgBgNVBAoTGURvbWluaW9u IERlYWxlciBTb2x1dGlvbnMxDTALBgNVBAsTBHRlY2gxGTAXBgNVBAMTEFJvb3Qg Q2VydGlmaWNhdGUxLDAqBgkqhkiG9w0BCQEWHW1hcmsuc3RlYmVuQGRyaXZlZG9t aW5pb24uY29tMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDNzyJBZ8jSwpGT 1KWpLbpD2EryM4doqGvlMMYp436FATbLe2OrYGuIDLju4wN63Dv/G9EWk8yx8J+g o7OXyBsq7rtpRJwhAUvVqZan5sZVTzkzr1Grf8GCsrbZ23cc8L0+IgFRXRaj0EwJ WirsgGbxYGWIVC6rZTV+NIJH5FqVmQIDAQABo4IBHzCCARswHQYDVR0OBBYEFKOV U5bUGOwXhCVubBf0f05E83ZcMIHrBgNVHSMEgeMwgeCAFKOVU5bUGOwXhCVubBf0 f05E83ZcoYG8pIG5MIG2MQswCQYDVQQGEwJVUzEWMBQGA1UECBMNTWFzc2FjaHVz ZXR0czETMBEGA1UEBxMKUGl0dHNmaWVsZDEiMCAGA1UEChMZRG9taW5pb24gRGVh bGVyIFNvbHV0aW9uczENMAsGA1UECxMEdGVjaDEZMBcGA1UEAxMQUm9vdCBDZXJ0 aWZpY2F0ZTEsMCoGCSqGSIb3DQEJARYdbWFyay5zdGViZW5AZHJpdmVkb21pbmlv bi5jb22CCQDFXPl6Mlu4QDAMBgNVHRMEBTADAQH/MA0GCSqGSIb3DQEBBQUAA4GB AGEqRiqJ8GPILJBga6Ws4/5RFKVTjId11o8VPQGRs1GKlsWmHjZduSakjGMB+osA eUymnIUcnakkHp6FblZDF933Dfg/wBWtIMwtfFFdVKnfYlsrJ2IQYcQqraND8wNO NonxHImtn3r89ctPE+rML6mwJFx9cdBvnxXAQ/UQuUMt -----END CERTIFICATE----- the following is found in /home/root/.postgresql/cacert.pem less cacert.pem -----BEGIN CERTIFICATE----- MIIEDTCCA3agAwIBAgIJAMVc+XoyW7hAMA0GCSqGSIb3DQEBBQUAMIG2MQswCQYD VQQGEwJVUzEWMBQGA1UECBMNTWFzc2FjaHVzZXR0czETMBEGA1UEBxMKUGl0dHNm aWVsZDEiMCAGA1UEChMZRG9taW5pb24gRGVhbGVyIFNvbHV0aW9uczENMAsGA1UE CxMEdGVjaDEZMBcGA1UEAxMQUm9vdCBDZXJ0aWZpY2F0ZTEsMCoGCSqGSIb3DQEJ ARYdbWFyay5zdGViZW5AZHJpdmVkb21pbmlvbi5jb20wHhcNMTQwMTMxMTgwNDU0 WhcNMTYxMDI3MTgwNDU0WjCBtjELMAkGA1UEBhMCVVMxFjAUBgNVBAgTDU1hc3Nh Y2h1c2V0dHMxEzARBgNVBAcTClBpdHRzZmllbGQxIjAgBgNVBAoTGURvbWluaW9u IERlYWxlciBTb2x1dGlvbnMxDTALBgNVBAsTBHRlY2gxGTAXBgNVBAMTEFJvb3Qg Q2VydGlmaWNhdGUxLDAqBgkqhkiG9w0BCQEWHW1hcmsuc3RlYmVuQGRyaXZlZG9t aW5pb24uY29tMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDNzyJBZ8jSwpGT 1KWpLbpD2EryM4doqGvlMMYp436FATbLe2OrYGuIDLju4wN63Dv/G9EWk8yx8J+g o7OXyBsq7rtpRJwhAUvVqZan5sZVTzkzr1Grf8GCsrbZ23cc8L0+IgFRXRaj0EwJ WirsgGbxYGWIVC6rZTV+NIJH5FqVmQIDAQABo4IBHzCCARswHQYDVR0OBBYEFKOV U5bUGOwXhCVubBf0f05E83ZcMIHrBgNVHSMEgeMwgeCAFKOVU5bUGOwXhCVubBf0 f05E83ZcoYG8pIG5MIG2MQswCQYDVQQGEwJVUzEWMBQGA1UECBMNTWFzc2FjaHVz ZXR0czETMBEGA1UEBxMKUGl0dHNmaWVsZDEiMCAGA1UEChMZRG9taW5pb24gRGVh bGVyIFNvbHV0aW9uczENMAsGA1UECxMEdGVjaDEZMBcGA1UEAxMQUm9vdCBDZXJ0 aWZpY2F0ZTEsMCoGCSqGSIb3DQEJARYdbWFyay5zdGViZW5AZHJpdmVkb21pbmlv bi5jb22CCQDFXPl6Mlu4QDAMBgNVHRMEBTADAQH/MA0GCSqGSIb3DQEBBQUAA4GB AGEqRiqJ8GPILJBga6Ws4/5RFKVTjId11o8VPQGRs1GKlsWmHjZduSakjGMB+osA eUymnIUcnakkHp6FblZDF933Dfg/wBWtIMwtfFFdVKnfYlsrJ2IQYcQqraND8wNO NonxHImtn3r89ctPE+rML6mwJFx9cdBvnxXAQ/UQuUMt -----END CERTIFICATE----- (END) The following is found in /home/root/.postgresql/private/cakey.pem less cakey.pem -----BEGIN RSA PRIVATE KEY----- Proc-Type: 4,ENCRYPTED DEK-Info: DES-EDE3-CBC,B5EF1690EBFB6FC9 wiRPS6vm6ohtCIqVWxJsbX6B+spFtmvgf5JCwMtPi+/E+Ez0b69lKWu/4cLuojKQ toc79HLYqFj53tHSM7/Qofkn+m3hqYk8cSsgZDk49G5SrpBSLifcQ1DgaI4y94Ik HE67OOI8uIAD034ESTFkZ4wIcsZTlEjkze8fwfEnTFipocalenmox6TpmvXiMliD u0Kdfk0xThrB/4v3iD4sNJO/IGIeb9oMEluqxzYDR4F1ss1lB1DkFyOgQZYudd4t s3ieHdudiRpknIGCkBw58m89tRzmOFFjUsIuXdlGyqwh5v5+GfxrzV/mFngidtLY AhMs22ZBvKJPe/+RyjdVPeXnUgnyyX3JS/UOJCgoLsaEWfCneG0gaHf2tjWgqFQ8 G1s/alKLQWvbLV3Cs9qHI+kp+168LwHDrIVPbrByOd/qqaimqZC4JHU8mptW8zR8 ezR+vyEVUYsVZBr6WfkK/5vSlTMnhuNZX42ISwsR0LsL7lv3kmRxMZMHVjfk6PxU P9up/bMGDZEs1p7BMz1rrVloyTAmq4+md6bXob5knPNauhCUcmoouJyqG5cld4jU Cys0VlQknFJmdhuerBWU6Fw0oPo67y/oAWU0bKN/evWTpKqzVfcQxa4xV27juPSR N5hQqNs4BA6mqCWxz5SHEzrrE6rZJtSLfE1PKYc0kKXSyZ8Fi0z1mw1ir4/JhXsA aWO27Iyd1U32O8GyCYswCV1wJA7KLwTUw+ynrPu+WKxXXcF17VKbpK2ehLGh235c nWV4CGqVFBZXhW4RdJu0zf4M8Ev4oz5qxnMrpOp4WnHq383s8LblAg== -----END RSA PRIVATE KEY----- (END)
Attachment:
pg_hba.conf
Description: Binary data
-- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
