Re: Support for cert auth in JDBC

Paula,

You don't need to apologize I don't want you to not post here, just realize
that we don't always have all the answers.

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Thu, Jan 17, 2013 at 1:06 PM, Paula Price <paula(dot)price(at)issinc(dot)com> wrote:

> 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
> >
>
>
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>