Re: Support for cert auth in JDBC

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