Re: Support for cert auth in JDBC

Hello,

Perhaps someone in this forum may be able to help with implementing the
solution you desire, but perhaps you should speak more directly to the
individual who created the CerAuthFactory class or initiating the
report on Nov. 2, 2011.

I'm not sure how this forum is going to be of help to you with pgJDBC
when on your own acknowledgment the problem of connecting via SSL appears
to be with with the use of Hibernate.

danap.

Paula Price wrote:
> Dave,
>
> I have not spoken with Hibernate although I do think that the problem is
> most likely with hibernate (or hibernate in tomcat). Since I can get ssl
> certification working with the jdbc driver then the problem has to be
> elsewhere. I only wrote to this forum because I found that someone
> mentioned a similar problem Nov 2, 2011 and added a CertAuthFactory.
>
> Here is more detail on the problem:
>
> Although I downloaded the CertAuthFactory class ( from above mentioned
> thread), I have not tried adding it to the jdbc driver yet. My simple
> java code – that works fine - contains a connection call and returns an
> error if it cannot connect (client is windows 7, postgres 9.1.6 is
> running on red hat linux 5). Also, full authentication works with Java
> based application DbVisualizer9.0.
>
> My cert Common Name is postgres. The only way into the database is with
> a valid cert (unless you are local - I wanted to make sure I did not
> lock myself out of the database). Pg_hba.conf contains:
>
> # TYPE DATABASE USER CIDR-ADDRESS METHOD
>
> # "local" is for Unix domain socket connections only
>
> local all all trust
>
> # IPv4 local connections:
>
> #host all all 0.0.0.0/0 md5
>
> hostssl all all 123.123.123.0 255.255.0.0 cert
>
> # IPv6 local connections:
>
> #host all all ::1/128 trust
>
> When I use my simple java code, I am able to connect just fine using
> this notation:
>
> set JAVA_OPTS=%JAVA_OPTS% -Djavax.net.ssl.trustStore=C:/certs/truststore.jks
>
> set JAVA_OPTS=%JAVA_OPTS% -Djavax.net.ssl.trustStorePassword=password
>
> set JAVA_OPTS=%JAVA_OPTS% -Djavax.net.ssl.keyStore=C:/certs/keystore.jks
>
> set JAVA_OPTS=%JAVA_OPTS% -Djavax.net.ssl.keyStorePassword=password
>
> When I try to mix hibernate into the code, it acts as if it does not
> read in my client cert. I see that trustStore is read and I am able to
> see the Common Name in the stacktrace (javax.net.debug = all). When
> authentication reads in the client cert, it reads in total garbage and I
> have no clue what it thinks it is reading.
>
> Below is the relevant part of the stack trace.
>
> *****Note by Paula – I made a few simple changes to the stack trace to
> obscure some readable info – but nothing that should cause problems
> debugging.
>
> *** CertificateRequest
>
> Cert Types: RSA, DSS
>
> Cert Authorities:
>
> <CN=Development, OU=Development, O=Software, L=Colorado, ST=Colorado, C=US>
>
> [read] MD5 and SHA1 hashes: len = 158
>
> 0000: 0D 00 00 9A 02 01 02 00 95 00 93 30 81 90 31 0B ...........0..1.
>
> 0010: 30 09 06 03 55 04 06 13 02 55 53 31 11 30 0F 06 0...U....US1.0..
>
> 0020: 03 55 04 08 0C 08 43 6F 6C 6F 72 61 64 6F 31 19 .U....Colorado1.
>
> 0030: 30 17 06 03 55 04 07 0C 10 43 6F 6C 6F 72 61 64 0...U....Colorad
>
> 0040: 6F 20 53 70 72 69 6E 67 73 31 27 30 25 06 03 55 o1'0%..U
>
> 0050: 04 0A 0C 1E 49 6E 74 65 6C 6C 69 67 65 6E 74 20 ....
>
> 0060: 53 6F 66 74 77 61 72 65 20 53 6F 6C 75 74 69 6F Software
>
> 0070: 6E 73 31 14 30 12 06 03 55 04 0B 0C 0B 44 65 76 1.0...U....Dev
>
> 0080: 65 6C 6F 70 6D 65 6E 74 31 14 30 12 06 03 55 04 elopment1.0...U.
>
> 0090: 03 0C 0B 44 65 76 65 6C 6F 70 6D 65 6E 74 ...Development
>
> *** ServerHelloDone
>
> [read] MD5 and SHA1 hashes: len = 4
>
> 0000: 0E 00 00 00 ....
>
> *** Certificate chain
>
> ***
>
> *** ClientKeyExchange, RSA PreMasterSecret, TLSv1
>
> [write] MD5 and SHA1 hashes: len = 269
>
> 0000: 0B 00 00 03 00 00 00 10 00 01 02 01 00 20 20 D5 ............. .
>
> 0010: AB 4E 12 10 CE 70 A9 C3 52 1E 4D A9 E7 1B BC ED .N...p..R.M.....
>
> 0020: DD 3C 35 F6 B8 8F BF CB BE 31 8C A8 E2 0F E9 79 .<5......1.....y
>
> 0030: 0A 0B 58 B7 F7 D4 F8 F8 BC 01 9E 5A C4 9C B2 AF ..X........Z....
>
> 0040: 16 17 EB 2E 1A 75 DF 24 D3 22 35 0E 47 B8 09 09 .....u.$."5.G...
>
> 0050: 85 01 8E 7F 0B BE D4 BE F1 A0 C3 4E EF F4 10 5C ...........N...\
>
> 0060: 85 D6 A0 60 99 E3 2B 88 F4 06 EA 45 2C 83 34 56 ...`..+....E,.4V
>
> 0070: B1 36 90 BD 9B 7A 44 C8 CB 00 FF 27 3B 01 CD 19 .6...zD....';...
>
> 0080: 70 A5 A7 AF 7D 15 BF 5C C2 FA 7E 19 53 86 52 F0 p......\....S.R.
>
> 0090: A9 CA BF 5E 17 4C AA 63 BA 7D 6E 28 F9 2E FB C4 ...^.L.c..n(....
>
> 00A0: 17 68 24 8A 9B 28 41 D8 8E F6 3B EA 8E 21 C1 25 .h$..(A...;..!.%
>
> 00B0: 10 DB BD C6 07 5F 61 BD 73 F7 09 73 7C 64 CC 38 ....._a.s..s.d.8
>
> 00C0: EB 17 E1 8A 48 80 E2 44 C2 38 34 9D AD C6 FC 9F ....H..D.84.....
>
> 00D0: EA E6 06 96 34 4A B8 02 E4 B2 72 12 70 A1 00 04 ....4J....r.p...
>
> 00E0: DA C0 FE 99 2F E2 E7 A9 DD 27 54 2C 6E 92 12 8E ..../....'T,n...
>
> 00F0: D8 BC 27 CB 34 3D F0 F2 39 A5 8D 4E D9 8F FE DF ..'.4=..9..N....
>
> 0100: D0 2F 16 AE F4 30 DF 16 F7 5F 63 6C 1E ./...0..._cl.
>
> http-bio-8080-exec-2, WRITE: TLSv1 Handshake, length = 269
>
> [Raw write]: length = 274
>
> 0000: 16 03 01 01 0D 0B 00 00 03 00 00 00 10 00 01 02 ................
>
> 0010: 01 00 20 20 D5 AB 4E 12 10 CE 70 A9 C3 52 1E 4D .. ..N...p..R.M
>
> 0020: A9 E7 1B BC ED DD 3C 35 F6 B8 8F BF CB BE 31 8C ......<5......1.
>
> 0030: A8 E2 0F E9 79 0A 0B 58 B7 F7 D4 F8 F8 BC 01 9E ....y..X........
>
> 0040: 5A C4 9C B2 AF 16 17 EB 2E 1A 75 DF 24 D3 22 35 Z.........u.$."5
>
> 0050: 0E 47 B8 09 09 85 01 8E 7F 0B BE D4 BE F1 A0 C3 .G..............
>
> 0060: 4E EF F4 10 5C 85 D6 A0 60 99 E3 2B 88 F4 06 EA N...\...`..+....
>
> 0070: 45 2C 83 34 56 B1 36 90 BD 9B 7A 44 C8 CB 00 FF E,.4V.6...zD....
>
> 0080: 27 3B 01 CD 19 70 A5 A7 AF 7D 15 BF 5C C2 FA 7E ';...p......\...
>
> 0090: 19 53 86 52 F0 A9 CA BF 5E 17 4C AA 63 BA 7D 6E .S.R....^.L.c..n
>
> 00A0: 28 F9 2E FB C4 17 68 24 8A 9B 28 41 D8 8E F6 3B (.....h$..(A...;
>
> 00B0: EA 8E 21 C1 25 10 DB BD C6 07 5F 61 BD 73 F7 09 ..!.%....._a.s..
>
> 00C0: 73 7C 64 CC 38 EB 17 E1 8A 48 80 E2 44 C2 38 34 s.d.8....H..D.84
>
> 00D0: 9D AD C6 FC 9F EA E6 06 96 34 4A B8 02 E4 B2 72 .........4J....r
>
> 00E0: 12 70 A1 00 04 DA C0 FE 99 2F E2 E7 A9 DD 27 54 .p......./....'T
>
> 00F0: 2C 6E 92 12 8E D8 BC 27 CB 34 3D F0 F2 39 A5 8D ,n.....'.4=..9..
>
> 0100: 4E D9 8F FE DF D0 2F 16 AE F4 30 DF 16 F7 5F 63 N...../...0..._c
>
> 0110: 6C 1E l.
>
> SESSION KEYGEN:
>
> PreMaster Secret:
>
> 0000: 03 01 47 EE 92 FF 8C 4C 4E FC 58 28 FB 11 0C 98 ..G....LN.X(....
>
> 0010: F2 F5 CA 42 46 02 6E 8D 09 AB C3 C5 BD C6 CB AA ...BF.n.........
>
> 0020: 4E DB F5 62 FB 2A B8 66 E2 43 C6 B7 DB 50 07 E0 N..b.*.f.C...P..
>
> CONNECTION KEYGEN:
>
> Client Nonce:
>
> 0000: 50 F8 2B DE 26 56 50 F1 8E 81 CB F9 39 0A CE A1 P.+.&VP.....9...
>
> 0010: D7 6D 45 20 21 B2 E1 BA 12 DB FB 83 8B D0 37 85 .mE !.........7.
>
> Server Nonce:
>
> 0000: 50 F8 2B DE C6 C5 A2 14 8B F0 12 1D 64 04 C1 91 P.+.........d...
>
> 0010: 8B 16 E6 88 A3 CF 45 82 98 F6 09 1A 06 61 58 10 ......E......aX.
>
> Master Secret:
>
> 0000: 4F CE 52 E8 17 2E 62 CE 43 0A B5 92 CE BA 7F EC O.R...b.C.......
>
> 0010: F7 8F 5B 12 89 5C C2 93 2C 5B 93 D8 F4 FF 8A 41 ..[..\..,[.....A
>
> 0020: 55 4E 9A 23 3F 55 4A BE 15 D5 09 54 D3 B4 52 AC UN.#?UJ....T..R.
>
> Client MAC write Secret:
>
> 0000: A2 03 04 80 08 E7 02 73 78 16 68 4B 37 DD 9C 2B .......sx.hK7..+
>
> 0010: 4A 0D 79 25 J.y%
>
> Server MAC write Secret:
>
> 0000: 9C 85 E5 FF 7C D4 23 9B FA C8 A8 79 40 C6 E4 D1 (dot)(dot)(dot)(dot)(dot)(dot)#(dot)(dot)(dot)(dot)y(at)(dot)(dot)(dot)
>
> 0010: 77 8E 5D 90 w.].
>
> Client write key:
>
> 0000: 84 21 98 68 3D B5 C6 C5 02 72 F5 25 DA FA 26 52 .!.h=....r.%..&R
>
> Server write key:
>
> 0000: 6C 9F 46 C6 C7 28 D7 65 05 B6 88 8F CF 91 09 B5 l.F..(.e........
>
> ... no IV used for this cipher
>
> http-bio-8080-exec-2, WRITE: TLSv1 Change Cipher Spec, length = 1
>
> [Raw write]: length = 6
>
> 0000: 14 03 01 00 01 01 ......
>
> *** Finished
>
> verify_data: { 6, 123, 192, 247, 189, 254, 84, 150, 77, 120, 177, 92 }
>
> ***
>
> [write] MD5 and SHA1 hashes: len = 16
>
> 0000: 14 00 00 0C 06 7B C0 F7 BD FE 54 96 4D 78 B1 5C ..........T.Mx.\
>
> Padded plaintext before ENCRYPTION: len = 36
>
> 0000: 14 00 00 0C 06 7B C0 F7 BD FE 54 96 4D 78 B1 5C ..........T.Mx.\
>
> 0010: 4F E1 08 3B F8 8A 9A 46 5B 85 39 0C 66 01 F2 A6 O..;...F[.9.f...
>
> 0020: E4 4C B9 99 .L..
>
> http-bio-8080-exec-2, WRITE: TLSv1 Handshake, length = 36
>
> [Raw write]: length = 41
>
> 0000: 16 03 01 00 24 1C A3 2E D6 86 DE A9 5A DD 23 19 ....$.......Z.#.
>
> 0010: 2C D3 31 99 B6 D6 EF 88 8A 8C 91 E6 A7 72 A7 A8 ,.1..........r..
>
> 0020: DC F0 A7 05 69 49 37 8E 47 ....iI7.G
>
> [Raw read]: length = 5
>
> 0000: 14 03 01 00 01 .....
>
> [Raw read]: length = 1
>
> 0000: 01 .
>
> http-bio-8080-exec-2, READ: TLSv1 Change Cipher Spec, length = 1
>
> [Raw read]: length = 5
>
> 0000: 16 03 01 00 24 ....$
>
> [Raw read]: length = 36
>
> 0000: 80 90 1E 1A 2A 5B 32 58 42 4B 67 7C 2B 2E D7 02 ....*[2XBKg.+...
>
> 0010: 0B 93 9D 5D 9E FE 2B 8E A1 2F BB CA 7C 82 18 C7 ...]..+../......
>
> 0020: 78 84 81 0D x...
>
> http-bio-8080-exec-2, READ: TLSv1 Handshake, length = 36
>
> Padded plaintext after DECRYPTION: len = 36
>
> 0000: 14 00 00 0C 3E BF 40 C7 B6 62 E0 F5 38 B6 EC DD ....>(dot)(at)(dot)(dot)b(dot)(dot)8(dot)(dot)(dot)
>
> 0010: 7E D7 D0 BE DC 5B 6B 0F DD B3 CD DC 95 A6 7D 4B .....[k........K
>
> 0020: 5D C4 B7 55 ]..U
>
> *** Finished
>
> verify_data: { 62, 191, 64, 199, 182, 98, 224, 245, 56, 182, 236, 221 }
>
> ***
>
> %% Cached client session: [Session-1, SSL_RSA_WITH_RC4_128_SHA]
>
> [read] MD5 and SHA1 hashes: len = 16
>
> ***Note by paula******Here is the URL call to hibernate
> *********************
>
> 0000: 14 00 00 0C 3E BF 40 C7 B6 62 E0 F5 38 B6 EC DD ....>(dot)(at)(dot)(dot)b(dot)(dot)8(dot)(dot)(dot)
>
> Padded plaintext before ENCRYPTION: len = 119
>
> 0000: 00 00 00 63 00 03 00 00 75 73 65 72 00 70 6F 73 ...c....user.pos
>
> 0010: 74 67 72 65 73 00 64 61 74 61 62 61 73 65 00 72 tgres.database.r
>
> 0020: 75 6E 6E 65 72 73 00 63 6C 69 65 6E 74 5F 65 6E unners.client_en
>
> 0030: 63 6F 64 69 6E 67 00 55 4E 49 43 4F 44 45 00 44 coding.UNICODE.D
>
> 0040: 61 74 65 53 74 79 6C 65 00 49 53 4F 00 65 78 74 ateStyle.ISO.ext
>
> 0050: 72 61 5F 66 6C 6F 61 74 5F 64 69 67 69 74 73 00 ra_float_digits.
>
> 0060: 32 00 00 10 FC 5E CF D9 20 3E 76 EB A5 0E 01 57 2....^.. >v....W
>
> 0070: 45 99 8A 55 A1 6C F6 E..U.l.
>
> http-bio-8080-exec-2, WRITE: TLSv1 Application Data, length = 119
>
> [Raw write]: length = 124
>
> 0000: 17 03 01 00 77 E5 F7 04 85 3E D3 5B 5C 54 B5 A6 ....w....>.[\T..
>
> 0010: B1 B1 31 2B FB 09 BC 93 B4 93 7C 6E 35 FE 90 ED ..1+.......n5...
>
> 0020: 4C A7 44 0F 4B 00 C5 5C 4C 31 E5 9A D3 21 E6 93 L.D.K..\L1...!..
>
> 0030: 24 06 02 F0 04 63 6B 96 D2 57 63 C5 DE C7 62 09 $....ck..Wc...b.
>
> 0040: 43 04 83 C7 80 FD 18 57 AA C0 DF 26 14 CD B7 F9 C......W...&....
>
> 0050: 5C 1F 28 2C CF 9F 54 2F 48 4B AC F4 0E 1B FA CA \.(,..T/HK......
>
> 0060: 0C FE 0B F8 73 25 EA 4E 94 80 91 DE E6 90 1A 63 ....s%.N.......c
>
> 0070: 71 17 01 76 21 34 C8 D5 F3 A0 2C 88 q..v!4....,.
>
> [Raw read]: length = 5
>
> 0000: 17 03 01 00 7B .....
>
> [Raw read]: length = 123
>
> 0000: 3A 60 92 1E AA 94 F1 28 39 95 91 1D 44 8E E9 8B :`.....(9...D...
>
> 0010: 99 DD CA A9 21 F5 08 F9 C2 EB 35 88 51 D5 0D F1 ....!.....5.Q...
>
> 0020: DC 0F D8 5A E3 90 A2 C6 19 CA F3 2D 32 7D 78 8D ...Z.......-2.x.
>
> 0030: 5B AB 5E F1 E9 58 31 60 FF 48 34 E9 C5 9A 88 B6 [.^..X1`.H4.....
>
> 0040: DD 75 44 B8 BB 18 29 29 56 5E FB F2 11 05 D7 3C .uD...))V^.....<
>
> 0050: 60 FA 1A B1 A5 56 33 36 94 E5 BE 1F 8A F3 B7 CC `....V36........
>
> 0060: 2A 5D CC B8 99 62 2B D0 BA F8 2B B2 5A 9F 99 F6 *]...b+...+.Z...
>
> 0070: AF 8C 7F DF 4E D5 F5 4B 8F 3B F3 ....N..K.;.
>
> http-bio-8080-exec-2, READ: TLSv1 Application Data, length = 123
>
> Padded plaintext after DECRYPTION: len = 123
>
> 0000: 45 00 00 00 66 53 46 41 54 41 4C 00 43 32 38 30 E...fSFATAL.C280
>
> 0010: 30 30 00 4D 63 6F 6E 6E 65 63 74 69 6F 6E 20 72 00.Mconnection r
>
> 0020: 65 71 75 69 72 65 73 20 61 20 76 61 6C 69 64 20 equires a valid
>
> 0030: 63 6C 69 65 6E 74 20 63 65 72 74 69 66 69 63 61 client certifica
>
> 0040: 74 65 00 46 61 75 74 68 2E 63 00 4C 33 35 36 00 te.Fauth.c.L356.
>
> 0050: 52 43 6C 69 65 6E 74 41 75 74 68 65 6E 74 69 63 RClientAuthentic
>
> 0060: 61 74 69 6F 6E 00 00 A3 E8 79 7F 76 28 24 67 05 ation....y.v($g.
>
> 0070: C3 07 19 CE 31 00 31 B0 4D FA F0 ....1.1.M..
>
> http-bio-8080-exec-2, called close()
>
> http-bio-8080-exec-2, called closeInternal(true)
>
> http-bio-8080-exec-2, SEND TLSv1 ALERT: warning, description = close_notify
>
> Paula Price
>
> paula(dot)price(at)issinc(dot)com <mailto:paula(dot)price(at)issinc(dot)com>
>
> *From:* davecramer(at)gmail(dot)com [mailto:davecramer(at)gmail(dot)com] *On Behalf Of
> *Dave Cramer
> *Sent:* Wednesday, January 16, 2013 4:20 AM
> *To:* Paula Price
> *Cc:* pgsql-jdbc(at)postgresql(dot)org
> *Subject:* Re: [JDBC] Support for cert auth in JDBC
>
> Hi Paula,
>
> Can you provide us with a bit more information ? Have you talked to
> hibernate guys to see what the problem is? It would seem that SSL works
> fine with pg and java, it is when you add hibernate to the mix that
> everything goes wrong.
>
> Dave
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On Tue, Jan 15, 2013 at 11:53 AM, Paula Price <paula(dot)price(at)issinc(dot)com
> <mailto:paula(dot)price(at)issinc(dot)com>> wrote:
>
> Hello,
>
> I followed this thread to the end - Support for cert auth in JDBC. I
> have spent two weeks trying to figure out why hibernate does not work
> with my postgresql ssl.
>
> I have openssl working great and I have the java certs working with a
> simple java program. When I throw hibernate into the mix everything goes
> wrong.
>
> I am trying to get full authentication working. My certs are valid
> (proved with simple java code).
>
> Is anyone able to help me with the final steps needed to put the
> CertAuthFactory in the jdbc driver? I have not done java for a couple of
> years so I may be a little slow (I would also like to see some examples
> of using the CertAuthFactory). I think I only need it to validate one
> trust store, so I do not need to pass in the trust store – although I
> have been known to be wrong before.
>
> Any assistance is greatly appreciated.
>
> Thanks,
>
> Paula Price
>