problem with Configuring a Connection to a Database using Visual Explain

problem with Configuring a Connection to a Database using Visual Explain

[Wei Tjioe]

Another observation with Visual Explain is on how it treats PASSWORD
authentication.

For example: in pg_hba.conf

local   all                                     md5
host    all     127.0.0.1     255.255.255.255   md5

This causes Visual Explain to refuse connection based on Password
(although connection request through psql, both local and -h, succeeded).

Test 1: Driver
PASSED

Test 2: Connection
FAILED
Unable to connect to database.
Reason: Something ....Please report this exception: Exception:
java.sql.SQLException: FATAL 1:
Password authentication failed for use "me"


When authentication is switched to clear text:

local   all                                     md5
host    all     127.0.0.1     255.255.255.255   password

[root@darwin data]# pg_ctl reload
postmaster successfully signaled
...

Visual Explain connects to database successfully.

Testing Database Connection: testdb
Test1: Driver
PASSED

Test2: Connection
PASSED


If there's a safer alternative, please post.


Wei

Re: problem with Configuring a Connection to a Database using Visual Explain

[Fernando Nasser]

Wei,

The client side (in this case JDBC) is not aware of how the server stores it's 
passwords.  They are sent clear text with either 'password' or 'md5' -- they are 
stored encrypted in the server.  If you need more security and don't want clear 
text passwords on the network make the connection using SSL.  The JDBC driver 
supports SSL connection for some time now.

W.r.t. your connection problem, the message may be misleading.  The reason that 
cause the password authentication to fail may have been a communication problem.
Check if you have the line:

tcpip_socket = true

in your postgresql.conf file (in the PGDATA directory).

Java (and JDBC) cannot connect using Unix sockets, so you must be accepting 
TCP/IP connections.

Regards,
Fernando


Wei Tjioe wrote:> Another observation with Visual Explain is on how it treats 
PASSWORD
> authentication.
> 
> For example: in pg_hba.conf
> 
> local   all                                     md5
> host    all     127.0.0.1     255.255.255.255   md5
> 
> This causes Visual Explain to refuse connection based on Password
> (although connection request through psql, both local and -h, succeeded).
> 
> Test 1: Driver
> PASSED
> 
> Test 2: Connection
> FAILED
> Unable to connect to database.
> Reason: Something ....Please report this exception: Exception:
> java.sql.SQLException: FATAL 1:
> Password authentication failed for use "me"
> 
> 
> When authentication is switched to clear text:
> 
> local   all                                     md5
> host    all     127.0.0.1     255.255.255.255   password
> 
> [root@darwin data]# pg_ctl reload
> postmaster successfully signaled
> ...
> 
> Visual Explain connects to database successfully.
> 
> Testing Database Connection: testdb
> Test1: Driver
> PASSED
> 
> Test2: Connection
> PASSED
> 
> 
> If there's a safer alternative, please post.
> 
> 
> Wei
> 
> 



-- 
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Re: problem with Configuring a Connection to a Database using Visual Explain

[Wei Tjioe]

Thanks. I will try SSL.

tcpip_socket was "true" in my postgresql.conf

Also, I noticed this morning that PostgreSQL Access was able to connect
successfully using md5 in pg_hba.conf (while Visual Explain triggered an SQLException).


Wei


On Thu, 2 Oct 2003, Fernando Nasser wrote:

> Wei,
>
> The client side (in this case JDBC) is not aware of how the server stores it's
> passwords.  They are sent clear text with either 'password' or 'md5' -- they are
> stored encrypted in the server.  If you need more security and don't want clear
> text passwords on the network make the connection using SSL.  The JDBC driver
> supports SSL connection for some time now.
>
> W.r.t. your connection problem, the message may be misleading.  The reason that
> cause the password authentication to fail may have been a communication problem.
> Check if you have the line:
>
> tcpip_socket = true
>
> in your postgresql.conf file (in the PGDATA directory).
>
> Java (and JDBC) cannot connect using Unix sockets, so you must be accepting
> TCP/IP connections.
>
> Regards,
> Fernando
>
>
> Wei Tjioe wrote:> Another observation with Visual Explain is on how it treats
> PASSWORD
> > authentication.
> >
> > For example: in pg_hba.conf
> >
> > local   all                                     md5
> > host    all     127.0.0.1     255.255.255.255   md5
> >
> > This causes Visual Explain to refuse connection based on Password
> > (although connection request through psql, both local and -h, succeeded).
> >
> > Test 1: Driver
> > PASSED
> >
> > Test 2: Connection
> > FAILED
> > Unable to connect to database.
> > Reason: Something ....Please report this exception: Exception:
> > java.sql.SQLException: FATAL 1:
> > Password authentication failed for use "me"
> >
> >
> > When authentication is switched to clear text:
> >
> > local   all                                     md5
> > host    all     127.0.0.1     255.255.255.255   password
> >
> > [root@darwin data]# pg_ctl reload
> > postmaster successfully signaled
> > ...
> >
> > Visual Explain connects to database successfully.
> >
> > Testing Database Connection: testdb
> > Test1: Driver
> > PASSED
> >
> > Test2: Connection
> > PASSED
> >
> >
> > If there's a safer alternative, please post.
> >
> >
> > Wei
> >
> >
>
>
>
> --
> Fernando Nasser
> Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
> 2323 Yonge Street, Suite #300
> Toronto, Ontario   M4P 2C9
>
>

Re: problem with Configuring a Connection to a Database using Visual Explain

[Fernando Nasser]

Wei Tjioe wrote:
> Thanks. I will try SSL.
> 
> tcpip_socket was "true" in my postgresql.conf
> 

And were you connecting from the same machine?

> Also, I noticed this morning that PostgreSQL Access was able to connect
> successfully using md5 in pg_hba.conf (while Visual Explain triggered an SQLException).

This uses Unix sockets, so it is no good for comparison.

Regards,
Fernando


-- 
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Re: problem with Configuring a Connection to a Database using Visual Explain

[Wei Tjioe]

Thanks for the response. I just want to report these observations in case
someone experience the same connection failure.

Here's the environment where the said behavior of pgaccess and Visual
Explain in my last posting were observed:
(note:  same applies with *local* commented out in pg_hba.conf)

in postgresql.conf, we have:
----------------------------------------------------
#       Connection Parameters
#
#tcpip_socket = false
tcpip_socket = true
#ssl = false
ssl = true

#max_connections = 32

#port = 5432
#hostname_lookup = false
#show_source_port = false

#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777

_________________________________________________________

in pg_hba.conf, we have:
------------------------------------------------------
# host
# ----
#
# This record identifies networked hosts that are permitted to connect
# via IP connections.
#
# Format:
#
#   host  DBNAME  IP_ADDRESS  ADDRESS_MASK  AUTH_TYPE  [AUTH_ARGUMENT]
#...
#local  all                                     md5
host    all     127.0.0.1     255.255.255.255   md5

_______________________________________________________________

> And were you connecting from the same machine?
The Visual Explain (and pgaccess) client is running in the same machine as
the postmaster server. I assumed tcpip sockets, rather than unix sockets,
would be used when configured as above.

>
>
> This uses Unix sockets, so it is no good for comparison.

Does this mean Unix Sockets were used even when 'Host' is explicitly
specified in Visual Explain -> configure ..., and when no local connection
is configured in pg_hba.conf?

I am also under the impression that pgaccess uses tpcip connection when a
host is explicitly specified (left non-blank) during a connection request.
Visual Explain and pgaccess are reading the exact same configuration
files, and using the exact same connection parameters:
	Database:
	Host:
	Port:
	User Name:
	Password:



Wei

Re: problem with Configuring a Connection to a Database using Visual Explain

[Fernando Nasser]

Fernando Nasser wrote:
> Wei,
> 
> The client side (in this case JDBC) is not aware of how the server 
> stores it's passwords.  They are sent clear text with either 'password' 
> or 'md5' -- they are stored encrypted in the server.  If you need more 
      ^^^^crypt
> security and don't want clear text passwords on the network make the 
> connection using SSL.  The JDBC driver supports SSL connection for some 
> time now.
> 

Sorry, I was thinking of crypt.  I forgot that we now have support for 
md5 and, of course, this means that the passwords may be encrypted 
before being sent over the wire.  In which case you don't need SSL (if 
just for that).

But the detection if the JDBC driver has to send md5 or clear text 
passwords (for password _and_ crypt) is done automatically.  The server 
tells the client how it wants the password.  The client program always 
pass it to JDBC as clear text.

Please note that JDBC drivers before 7.3 had a bug in md5 password 
encription.  I think it would only affect people with a different 
encoding in their locale, but to be in the safe side you should use 
drivers 7.3 on when using md5.

Your pg_hba file seems to be in a very old format (7.1.x perhaps).  What 
versions of PostgreSQL and of the JDBC driver are you using?

Regards,
Fernando

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Re: problem with Configuring a Connection to a Database using Visual Explain

[Wei Tjioe]

>
> What versions of PostgreSQL and of the JDBC driver are you using?

[wei@darwin wei]$ postmaster --version
postmaster (PostgreSQL) 7.2.3-RH

[wei@darwin ext]$ pwd
/opt/j2sdk_nb/j2sdk/jre/lib/ext

[wei@darwin ext]$ ls
dnsns.jar  ldapsec.jar  localedata.jar  rh-pgsql-jdbc2.jar  sunjce_provider.jar






Regards, Wei

Re: problem with Configuring a Connection to a Database using Visual Explain

[Fernando Nasser]

Wei Tjioe wrote:
> 
>>What versions of PostgreSQL and of the JDBC driver are you using?
> 
> 
> [wei@darwin wei]$ postmaster --version
> postmaster (PostgreSQL) 7.2.3-RH
> 

The JDBC driver that was distributed with this version was the one with 
the md5 bug I've mentioned.  You must get a JDBC driver at least 7.3 
(the 7.3 J2EE one was reported to work with md5) or newer.




> [wei@darwin ext]$ pwd
> /opt/j2sdk_nb/j2sdk/jre/lib/ext
> 
> [wei@darwin ext]$ ls
> dnsns.jar  ldapsec.jar  localedata.jar  rh-pgsql-jdbc2.jar  sunjce_provider.jar
> 



-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Re: problem with Configuring a Connection to a Database using Visual Explain

[Wei Tjioe]

Thanks.




On Thu, 2 Oct 2003, Fernando Nasser wrote:

> Wei Tjioe wrote:
> >
> >>What versions of PostgreSQL and of the JDBC driver are you using?
> >
> >
> > [wei@darwin wei]$ postmaster --version
> > postmaster (PostgreSQL) 7.2.3-RH
> >
>
> The JDBC driver that was distributed with this version was the one with
> the md5 bug I've mentioned.  You must get a JDBC driver at least 7.3
> (the 7.3 J2EE one was reported to work with md5) or newer.
>
>
>
>
> > [wei@darwin ext]$ pwd
> > /opt/j2sdk_nb/j2sdk/jre/lib/ext
> >
> > [wei@darwin ext]$ ls
> > dnsns.jar  ldapsec.jar  localedata.jar  rh-pgsql-jdbc2.jar  sunjce_provider.jar
> >
>
>
>
> --
> Fernando Nasser
> Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
> 2323 Yonge Street, Suite #300
> Toronto, Ontario   M4P 2C9
>
>

problem with Configuring a Connection to a Database using Visual Explain

[huwang wang]

[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #1: Type: text/plain; charset=gb2312, Size: 990 bytes --]

Hi,

After,modifying the content of  my pg_hba.conf file 
to local all trust,I can log into the graphical tool
Adminstrator using username ccm which had been created
by myself using command line and then I  created a
database named ccm61 using the graphical tool.

Then I turned to Visual Explain.When I configured a
connection to the database ccm61,I had used the
following parameter:
database:ccm61
Host:localhost
port:5432
username: ccm
password:ccm_xyz

After I clicked  the Test button,the system response
is:

Test 1: Driver
FAILED
Unable to load JDBC driver.

Test 2: Connection
FAILED
Unable to connect to database.
Reason: No suitable driver

Can you help me out?
 



_________________________________________________________
Do You Yahoo!? 
ÆôÓõçÓÊÕʺţ¬Áì»áÑÅ»¢Í¨[ÉíÁÙÆä¾³ÁĵçÓ°]µÄ¶¯¸Ð÷ÈÁ¦£¬»¹ÓÐÍøÂçÉãÏñÍ·+ÑÅ»¢Í¨ÊÕÒô»úµÈÄãÀ´ÄÃ
http://cn.rd.yahoo.com/mail_cn/tag/?http://cn.messenger.yahoo.com

Re: problem with Configuring a Connection to a Database using Visual Explain

[Patrick Macdonald]

huwang wang wrote:

> After I clicked  the Test button,the system response
> is:
> 
> Test 1: Driver
> FAILED
> Unable to load JDBC driver.
> 
> Test 2: Connection
> FAILED
> Unable to connect to database.
> Reason: No suitable driver
> 
> Can you help me out?

The error message returned should give you a clue.  Visual
Explain can't find the JDBC driver.  Make sure the JDBC is
installed in the proper location.  Please have a look at:
http://www.redhat.com/docs/manuals/database/RHDB-2.1-Manual/guitools/x100.html
under the "After Installation" section (at the bottom).

Cheers,
Patrick