Обсуждение: ssl connection and webstart

I have a postgresql database up and running will ssl support in, this works fine.

I have a certificate that I have added via the command line to my jvm. once this is added my applications connects via ssl to my database perfectly.

However, what i really want to do is to deploy my application via webstart. In order to do this i would have though that the certificate needs to be added to the clients pc via the certificate manager which is part of the Java Plug/Webstart tool that exists in the control panel. I have tried repeated times to add the certificate this way, and although it seems to import fine, the application always fails and reports the same error as if there was no certificate. If I then manually add the cert to the vm using the command line tools it works!!!

How can I add the cert via webstart so a "user" can do this?

Anyone else tried ssl and webstart?

I have a problem:
SELECT * FROM mytable WHERE c1 like 'a' UNION ALL SELECT * FROM mytable
WHERE c2 like 'a' || '%' AND c2 not like 'a' UNION ALL SELECT * FROM
mytable WHERE c2 like '%' || 'a' || '%' AND c2 not like 'a' || '%' AND
c2 not like 'a' LIMIT 101 OFFSET 0

Where 'a' is a variable.
When I run this query from Statement. There is no problem. The result is
ordered by: First SELECT, second SELECT third SELECT.
When I run this query from PreparedStatement ('a' values replaced by ?).
The result is ordered by in alphabets.Why?

Regards,
    Ferenc

yoursoft wrote:
> I have a problem:
> SELECT * FROM mytable WHERE c1 like 'a' UNION ALL SELECT * FROM mytable
> WHERE c2 like 'a' || '%' AND c2 not like 'a' UNION ALL SELECT * FROM
> mytable WHERE c2 like '%' || 'a' || '%' AND c2 not like 'a' || '%' AND
> c2 not like 'a' LIMIT 101 OFFSET 0
>
> Where 'a' is a variable.
> When I run this query from Statement. There is no problem. The result is
> ordered by: First SELECT, second SELECT third SELECT.
> When I run this query from PreparedStatement ('a' values replaced by ?).
> The result is ordered by in alphabets.Why?

You don't have an ORDER BY so the result ordering is undefined. The
planner happens to pick different plans in the two cases which result in
different orderings.

-O

yoursoft wrote:
> Ok, the planner uses different way. I think the planner use the UNION in
> PreparedStatement and not the UNION ALL?
> How to rewrite the SQL to use the UNION ALL in preparedstatement?

You need an ORDER BY or the order is undefined and unpredictable. See
http://www.postgresql.org/docs/8.2/static/queries-order.html

There's no simple way to say "give me rows from this table first" other
than running a query on each table separately.

-O

Michael Andreasen wrote:
> I have a postgresql database up and running will ssl support
> in, this works fine.
>
> I have a certificate that I have added via the command line
> to my jvm. once this is added my applications connects via
> ssl to my database perfectly.
>
> However, what i really want to do is to deploy my application
> via webstart. In order to do this i would have though that
> the certificate needs to be added to the clients pc via the
> certificate manager which is part of the Java Plug/Webstart
> tool that exists in the control panel. I have tried repeated
> times to add the certificate this way, and although it seems
> to import fine, the application always fails and reports the
> same error as if there was no certificate. If I then manually
> add the cert to the vm using the command line tools it works!!!
>
> How can I add the cert via webstart so a "user" can do this?
>
> Anyone else tried ssl and webstart?

This is not a direct answer to your question, but do you really
want client certificates or are you just looking for some way
to connect with JDBC using SSL?

If it is the latter, you could save the effort of distributing
client certificates by using

sslfactory=org.postgresql.ssl.NonValidatingFactor

in the JDBC URL.

Yours,
Laurenz Albe

>How can I add the cert via webstart so a "user" can do this?

Are you using any standard certificates from the default certificate key
store?

If the answer is no, then one idea is to create your own certificate key
store in your software and add your certificate with java code.
You do this as part of creating your SSLSocketFactory, see attached example.
And then when your application starts up, the first thing you do before
making any connections to postgresql database is:
java.security.Security.setProperty("ssl.SocketFactory.provider",
"MyOrganisation.MyClientApp.ssl.MySSLSocketFactory");

Hope that helps.
Regards
Donald Fraser

>How can I add the cert via webstart so a "user" can do this?

Are you using any standard certificates from the default certificate key
store?

If the answer is no, then one idea is to create your own certificate key
store in your software and add your certificate with java code.
You do this as part of creating your SSLSocketFactory, see attached example.
And then when your application starts up, the first thing you do before
making any connections to postgresql database is:
java.security.Security.setProperty("ssl.SocketFactory.provider",
"MyOrganisation.MyClientApp.ssl.MySSLSocketFactory");

Hope that helps.
Regards
Donald Fraser

Oliver Jowett wrote:
> yoursoft wrote:
>> Ok, the planner uses different way. I think the planner use the UNION
>> in PreparedStatement and not the UNION ALL?
>> How to rewrite the SQL to use the UNION ALL in preparedstatement?
>
> You need an ORDER BY or the order is undefined and unpredictable. See
> http://www.postgresql.org/docs/8.2/static/queries-order.html
>
> There's no simple way to say "give me rows from this table first"
> other than running a query on each table separately.

Actually the simple way is
select c1,c2,c3,...,cn from (select 1, table1.* from table1 union all
select 2, table2.* from table2 union all ...) a(ordercolumn, c1, c2, c3,
.., cn) order by 1