PsqlODBC with a SSL connection

I'm having difficulty connecting in SSL mode with the "PostgreSQL ANSI"
driver from a Win32 machine to a PostgreSQL 8.2 installation on a
GNU/Linux machine.

I can connect from my client w/ SSL using "psql" from the command line
fine, also connecting using plain ODBC works too. When I try to force
the SSL connection (by making changes to the servers pg_hba.conf file),
I'm told SSL is off.

Is there something in the connection string I use to tell the driver to
use SSL?

Thanks.

tgl

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: PsqlODBC with a SSL connection

Looks like I need to include Ssslmode=require in the connection string
(just Ssl=true) doesn't do it. Here's a nice list of attributes from
the Npgsql,
http://npgsql.projects.postgresql.org/docs/manual/UserManual .htm, website:

 Gets or sets the string used to connect to a PostgreSQL database.



        /// Valid values are:

        /// Server:                     Address/Name of Postgresql Server;

        /// Port:                       Port to connect to;

        /// Protocol:                   Protocol version to use, instead of automatic; Integer 2 or 3;

        /// Database:                   Database name. Defaults to user name if not specified;

        /// User Id:                    User name;

        /// Password:                   Password for clear text authentication;

        /// SSL:                        True or False. Controls whether to attempt a secure connection. Default = False;

        /// Pooling:                    True or False. Controls whether connection pooling is used. Default = True;

        /// MinPoolSize:                Min size of connection pool. Default: 1;

        /// MaxPoolSize:                Max size of connection pool. Default: 20;

        /// Encoding:                   Encoding to be used; Can be ASCII or UNICODE. Default is ASCII. Use UNICODE if you are having problems with accents.

        /// Timeout:                    Time to wait for connection open in seconds. Default is 15.

        /// CommandTimeout:             Time to wait for command to finish execution before throw an exception. In seconds. Default is 20.

        /// Sslmode:                    Mode for ssl connection control. 

        /// ConnectionLifeTime:         Time to wait before closing unused connections in the pool in seconds. Default is 15.

        /// SyncNotification:           Specifies if Npgsql should use synchronous notifications

        Encoding can be ASCII or UNICODE. If your application uses characters with accents and with default settings it doesn't work, try changing that.

        Min pool size when specified will make NpgsqlConnection pre allocates this number of connections with the server.

        Sslmode can be one of the following values: 

            Prefer - If it is possible to connect using ssl, it will be used.

            Require - If an ssl connection cannot be made, an exception is thrown.

            Allow - Not supported yet, just connects without ssl.

            Disable - No ssl connection is done.

            Default is Disable.

Re: PsqlODBC with a SSL connection

Did this work? How exactly? Could someone please post the entries in
..odbc.ini that accomplish this? I have not been able to puzzle it out. I
am using unixODBC under Linux. Under Windows it is controlled with a
drop-down in the GUI but under Linux I just edit .odbc.ini. Thanks.



Terry Lorber wrote:
>
> Looks like I need to include Ssslmode=require in the connection string
> (just Ssl=true) doesn't do it. Here's a nice list of attributes from
> the Npgsql,
> http://npgsql.projects.postgresql.org/docs/manual/UserManual .htm, website:
>
>

>  Gets or sets the string used to connect to a PostgreSQL database.

> 

>         /// Valid values are:

>         /// Server:                     Address/Name of Postgresql Server;

>         /// Port:                       Port to connect to;

>         /// Protocol:                   Protocol version to use, instead

> of automatic; Integer 2 or 3;

>         /// Database:                   Database name. Defaults to user

> name if not specified;

>         /// User Id:                    User name;

>         /// Password:                   Password for clear text

> authentication;

>         /// SSL:                        True or False. Controls whether to

> attempt a secure connection. Default = False;

>         /// Pooling:                    True or False. Controls whether

> connection pooling is used. Default = True;

>         /// MinPoolSize:                Min size of connection pool.

> Default: 1;

>         /// MaxPoolSize:                Max size of connection pool.

> Default: 20;

>         /// Encoding:                   Encoding to be used; Can be ASCII

> or UNICODE. Default is ASCII. Use UNICODE if you are having problems with

> accents.

>         /// Timeout:                    Time to wait for connection open

> in seconds. Default is 15.

>         /// CommandTimeout:             Time to wait for command to finish

> execution before throw an exception. In seconds. Default is 20.

>         /// Sslmode:                    Mode for ssl connection control. 

>         /// ConnectionLifeTime:         Time to wait before closing unused

> connections in the pool in seconds. Default is 15.

>         /// SyncNotification:           Specifies if Npgsql should use

> synchronous notifications

>         Encoding can be ASCII or UNICODE. If your application uses

> characters with accents and with default settings it doesn't work, try

> changing that.

>         Min pool size when specified will make NpgsqlConnection pre

> allocates this number of connections with the server.

>         Sslmode can be one of the following values: 

>             Prefer - If it is possible to connect using ssl, it will be

> used.

>             Require - If an ssl connection cannot be made, an exception is

> thrown.

>             Allow - Not supported yet, just connects without ssl.

>             Disable - No ssl connection is done.

>             Default is Disable.

> 

Re: PsqlODBC with a SSL connection

The best clue I could find is here:
http://psqlodbc.projects.postgresql.org/config-opt.html but I'm not sure how
to apply that information to my .odbc.ini. Thanks.


Neil Best wrote:
>
> Did this work? How exactly? Could someone please post the entries in
> .odbc.ini that accomplish this? I have not been able to puzzle it out. I
> am using unixODBC under Linux. Under Windows it is controlled with a
> drop-down in the GUI but under Linux I just edit .odbc.ini. Thanks.
>
>
>
> Terry Lorber wrote:
>>
>> Looks like I need to include Ssslmode=require in the connection string
>> (just Ssl=true) doesn't do it. Here's a nice list of attributes from
>> the Npgsql,
>> http://npgsql.projects.postgresql.org/docs/manual/UserManual .htm,
>> website:
>>
>>

>>  Gets or sets the string used to connect to a PostgreSQL database.

>> 

>>         /// Valid values are:

>>         /// Server:                     Address/Name of Postgresql

>> Server;

>>         /// Port:                       Port to connect to;

>>         /// Protocol:                   Protocol version to use, instead

>> of automatic; Integer 2 or 3;

>>         /// Database:                   Database name. Defaults to user

>> name if not specified;

>>         /// User Id:                    User name;

>>         /// Password:                   Password for clear text

>> authentication;

>>         /// SSL:                        True or False. Controls whether

>> to attempt a secure connection. Default = False;

>>         /// Pooling:                    True or False. Controls whether

>> connection pooling is used. Default = True;

>>         /// MinPoolSize:                Min size of connection pool.

>> Default: 1;

>>         /// MaxPoolSize:                Max size of connection pool.

>> Default: 20;

>>         /// Encoding:                   Encoding to be used; Can be ASCII

>> or UNICODE. Default is ASCII. Use UNICODE if you are having problems with

>> accents.

>>         /// Timeout:                    Time to wait for connection open

>> in seconds. Default is 15.

>>         /// CommandTimeout:             Time to wait for command to

>> finish execution before throw an exception. In seconds. Default is 20.

>>         /// Sslmode:                    Mode for ssl connection control. 

>>         /// ConnectionLifeTime:         Time to wait before closing

>> unused connections in the pool in seconds. Default is 15.

>>         /// SyncNotification:           Specifies if Npgsql should use

>> synchronous notifications

>>         Encoding can be ASCII or UNICODE. If your application uses

>> characters with accents and with default settings it doesn't work, try

>> changing that.

>>         Min pool size when specified will make NpgsqlConnection pre

>> allocates this number of connections with the server.

>>         Sslmode can be one of the following values: 

>>             Prefer - If it is possible to connect using ssl, it will be

>> used.

>>             Require - If an ssl connection cannot be made, an exception

>> is thrown.

>>             Allow - Not supported yet, just connects without ssl.

>>             Disable - No ssl connection is done.

>>             Default is Disable.

>> 

Re: PsqlODBC with a SSL connection

Neil Best wrote:
> Did this work? How exactly? Could someone please post the entries in
> .odbc.ini that accomplish this? I have not been able to puzzle it out. I
> am using unixODBC under Linux. Under Windows it is controlled with a
> drop-down in the GUI but under Linux I just edit .odbc.ini. Thanks.

SSLmode=disable, allow, prefer, require, verify-ca or verify-full.

regards,
Hiroshi Inoue

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: PsqlODBC with a SSL connection

Hiroshi Inoue wrote:
>
>
> SSLmode=disable, allow, prefer, require, verify-ca or verify-full.
> \

Thanks, Hiroshi. This was my best guess but when I try to connect with isql
I get the dreaded error:

[28000][unixODBC]FATAL: no pg_hba.conf entry for host . . . , SSL off

From the host I am testing I am able to connect using psql and a role that
must have an SSL connection for this particular database. I take this to
mean that my pgsqlODBC is not built with SSL. How can I verify this or
obtain version or configuration information?


--
View this message in context: http://www.nabble.com/PsqlODBC-with-a-SSL-connection-tp81846 33p25754481.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc