Re: Support for cert auth in JDBC

Again, I would not have posted this to this forum except for the fact that I found the initial thread and the last message on the thread said that the CertAuthFactory was going to be added to the jdbc code. So, I thought I would give it a try and see if it fixed my problem. I did not mean to bother anyone, I just wanted to know why the CertAuthFactory code never made it into the jdbc jar file and a small example of how to use it. Please forgive me for any aggravation I have caused, I had run into a wall and was not making progress and I know postgres a lot better than I know hibernate.

Thank you for your time,
Paula Price
paula(dot)price(at)issinc(dot)com

-----Original Message-----
From: dmp [mailto:danap(at)ttc-cmc(dot)net]
Sent: Thursday, January 17, 2013 10:45 AM
To: Paula Price; PostgreSQL JDBC
Subject: Re: [JDBC] 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
>