Thread: Password authentication working with psql, but fails with jdbc for me

I have setup a system with postgres 7.3.4-5 on Debian and am attempting to
access a database from Java.  When I use trust permissions, e.g. I have the
following in my pg_hba.conf file:
    host all <theuser> <ip-range> <ip-mask> trust
everything works fine (with values in angled brackets replaced as
appropriate of course).  I can access the database both through psql and
with Java.  If I modify pg_hba.conf to use any kind of password
authentication (other than Kerberos, which isn't supported in the JDBC
drivers), e.g. by using any one of the following lines
    host all <theuser> <ip-range> <ip-mask> password
    host all <theuser> <ip-range> <ip-mask> crypt
    host all <theuser> <ip-range> <ip-mask> md5
then psql asks for a password and works properly, but I get the following
error for the JDBC drivers:
    Password authentication failed for user "<theuser>"
    at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection
AbstractJdbc1Connection.java:202)
    at org.postgresql.Driver.connect(Driver.java:122)

I get the same results from multiple machine with different JVMs and by
trying out pg73jdbc1.jar, pg73jdbc2.jar, and pg73jdbc3.jar (by adding them,
one at a time, to the classpath).  In my postgresql.conf file, I do have
"tcpip_socket=true".

Most of the FAQs and discussions I've found on the topic point to classpath
issues.  As far as I can tell though, I'm using the classpath correctly
since everything works fine in trust mode.

Any ideas what I could be doing incorrectly?

Thanks in advance.

--Gerald Dalley
  dalleyg@mit.edu

On 18/09/2003 17:15 Gerald Dalley wrote:
> I have setup a system with postgres 7.3.4-5 on Debian and am attempting
> to
> access a database from Java.  When I use trust permissions, e.g. I have
> the
> following in my pg_hba.conf file:
>     host all <theuser> <ip-range> <ip-mask> trust
> everything works fine (with values in angled brackets replaced as
> appropriate of course).  I can access the database both through psql and
> with Java.  If I modify pg_hba.conf to use any kind of password
> authentication (other than Kerberos, which isn't supported in the JDBC
> drivers), e.g. by using any one of the following lines
>     host all <theuser> <ip-range> <ip-mask> password
>     host all <theuser> <ip-range> <ip-mask> crypt
>     host all <theuser> <ip-range> <ip-mask> md5
> then psql asks for a password and works properly, but I get the following
> error for the JDBC drivers:
>     Password authentication failed for user "<theuser>"
>     at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection
> AbstractJdbc1Connection.java:202)
>     at org.postgresql.Driver.connect(Driver.java:122)
>
> I get the same results from multiple machine with different JVMs and by
> trying out pg73jdbc1.jar, pg73jdbc2.jar, and pg73jdbc3.jar (by adding
> them,
> one at a time, to the classpath).  In my postgresql.conf file, I do have
> "tcpip_socket=true".
>
> Most of the FAQs and discussions I've found on the topic point to
> classpath
> issues.  As far as I can tell though, I'm using the classpath correctly
> since everything works fine in trust mode.
>
> Any ideas what I could be doing incorrectly?

First, don't thrown by "AbstractJdbc1Connection". The JDBC2 and JDBC3
classes mostly extend the JDBC1 classes. How did you create the db user?
Maybe you got the password wrong or possibly no password at all. Are your
psql connections from the same machine because if they IRC, psql will
connect using unix sockets instead of tcp/ip so the host lines will not be
used.

I'm sure it must just some simple configuration error as I've been
successfully connecting with userid/password since version 7.1.

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Gerald-

Try connecting using psql with the -h option to specify the host (even if
you're on the same machine) this is a better test, since it will force psql
to connect using an IP socket in exactly the same fashion that jdbc does.
This will allow you to more definitely sort your problem into either "pg_hba
problem" or "java problem" groups.

-NF

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Gerald Dalley
> Sent: Thursday, September 18, 2003 11:16 AM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] Password authentication working with psql, but fails
> with jdbc for me
>
>
> I have setup a system with postgres 7.3.4-5 on Debian and am attempting to
> access a database from Java.  When I use trust permissions, e.g.
> I have the
> following in my pg_hba.conf file:
>     host all <theuser> <ip-range> <ip-mask> trust
> everything works fine (with values in angled brackets replaced as
> appropriate of course).  I can access the database both through psql and
> with Java.  If I modify pg_hba.conf to use any kind of password
> authentication (other than Kerberos, which isn't supported in the JDBC
> drivers), e.g. by using any one of the following lines
>     host all <theuser> <ip-range> <ip-mask> password
>     host all <theuser> <ip-range> <ip-mask> crypt
>     host all <theuser> <ip-range> <ip-mask> md5
> then psql asks for a password and works properly, but I get the following
> error for the JDBC drivers:
>     Password authentication failed for user "<theuser>"
>     at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection
> AbstractJdbc1Connection.java:202)
>     at org.postgresql.Driver.connect(Driver.java:122)
>
> I get the same results from multiple machine with different JVMs and by
> trying out pg73jdbc1.jar, pg73jdbc2.jar, and pg73jdbc3.jar (by
> adding them,
> one at a time, to the classpath).  In my postgresql.conf file, I do have
> "tcpip_socket=true".
>
> Most of the FAQs and discussions I've found on the topic point to
> classpath
> issues.  As far as I can tell though, I'm using the classpath correctly
> since everything works fine in trust mode.
>
> Any ideas what I could be doing incorrectly?
>
> Thanks in advance.
>
> --Gerald Dalley
>   dalleyg@mit.edu
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Paul Thomas
> Sent: Thursday, 18 September 2003 1:46 PM
> To: pgsql-jdbc @ postgresql . org
> Subject: Re: [JDBC] Password authentication working with psql, but fails
> with jdbc for me
>
>
>
> On 18/09/2003 17:15 Gerald Dalley wrote:
> > I have setup a system with postgres 7.3.4-5 on Debian and am attempting
> > to
> > access a database from Java.  When I use trust permissions, e.g. I have
> > the
> > following in my pg_hba.conf file:
> >     host all <theuser> <ip-range> <ip-mask> trust
> > everything works fine (with values in angled brackets replaced as
> > appropriate of course).  I can access the database both through psql and
> > with Java.  If I modify pg_hba.conf to use any kind of password
> > authentication (other than Kerberos, which isn't supported in the JDBC
> > drivers), e.g. by using any one of the following lines
> >     host all <theuser> <ip-range> <ip-mask> password
> >     host all <theuser> <ip-range> <ip-mask> crypt
> >     host all <theuser> <ip-range> <ip-mask> md5
> > then psql asks for a password and works properly, but I get the
> following
> > error for the JDBC drivers:
> >     Password authentication failed for user "<theuser>"
> >     at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection
> > AbstractJdbc1Connection.java:202)
> >     at org.postgresql.Driver.connect(Driver.java:122)
> >
> > I get the same results from multiple machine with different JVMs and by
> > trying out pg73jdbc1.jar, pg73jdbc2.jar, and pg73jdbc3.jar (by adding
> > them,
> > one at a time, to the classpath).  In my postgresql.conf file, I do have
> > "tcpip_socket=true".
> >
> > Most of the FAQs and discussions I've found on the topic point to
> > classpath
> > issues.  As far as I can tell though, I'm using the classpath correctly
> > since everything works fine in trust mode.
> >
> > Any ideas what I could be doing incorrectly?
>
> First, don't thrown by "AbstractJdbc1Connection". The JDBC2 and JDBC3
> classes mostly extend the JDBC1 classes.

Yep, I noticed that after decompiling the classes.  I included the error
message mostly for the "password authentication..." part

> How did you create the db user?

createuser -A -D -E -P <theuser>
...type in password as prompted...

> Maybe you got the password wrong or possibly no password at all.

I don't think so, since I have to type in the password when I use psql.
Just for fun, I tried using a blank password, which fails even in psql.

> Are your
> psql connections from the same machine because if they IRC, psql will
> connect using unix sockets instead of tcp/ip so the host lines
> will not be used.

I'm running psql and the jdbc program from the same machine, which is
different from the server.  When psql is remote, I'm assuming that it uses
tcp/ip sockets instead of unix sockets.

> I'm sure it must just some simple configuration error as I've been
> successfully connecting with userid/password since version 7.1.

Agreed.  I'm just having trouble thinking of what else to try.

--Gerald Dalley
  dalleyg@mit.edu

On 18/09/2003 21:33 Gerald Dalley wrote:
> [snip]
>
> > How did you create the db user?
>
> createuser -A -D -E -P <theuser>
> ...type in password as prompted...
>
> > Maybe you got the password wrong or possibly no password at all.
>
> I don't think so, since I have to type in the password when I use psql.
> Just for fun, I tried using a blank password, which fails even in psql.
>
> > Are your
> > psql connections from the same machine because if they IRC, psql will
> > connect using unix sockets instead of tcp/ip so the host lines
> > will not be used.
>
> I'm running psql and the jdbc program from the same machine, which is
> different from the server.  When psql is remote, I'm assuming that it
> uses
> tcp/ip sockets instead of unix sockets.
>
> > I'm sure it must just some simple configuration error as I've been
> > successfully connecting with userid/password since version 7.1.
>
> Agreed.  I'm just having trouble thinking of what else to try.

Maybe use something like tcpdump to see exactly what is being sent across?

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Thanks for the suggestions so far that people have made.

Here's the current status...
1) psql works (and always was working) from both Windows and Linux remote
machines
2) Using the Perl PgPP module works from both Windows and Linux remote
machines
3) Using devpgjdbc1.jar works on Linux from a remote machine (on Linux, I'm
stuck with JDK 1.1.8)
4) Using any of the current .jar files at jdbc.postgresql.org fails for
Windows

Is JNI or something similar being used that might cause the JDBC drivers to
fail on Windows?

--Gerald Dalley
  dalleyg@mit.edu

> -----Original Message-----
> From: Nick Fankhauser [mailto:nickf@ontko.com]
> Sent: Thursday, 18 September 2003 2:36 PM
> To: Gerald Dalley; pgsql-jdbc@postgresql.org
> Subject: RE: [JDBC] Password authentication working with psql, but fails
> with jdbc for me
>
>
> Gerald-
>
> Try connecting using psql with the -h option to specify the host (even if
> you're on the same machine) this is a better test, since it will
> force psql
> to connect using an IP socket in exactly the same fashion that jdbc does.
> This will allow you to more definitely sort your problem into
> either "pg_hba
> problem" or "java problem" groups.
>
> -NF
>
> > -----Original Message-----
> > From: pgsql-jdbc-owner@postgresql.org
> > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Gerald Dalley
> > Sent: Thursday, September 18, 2003 11:16 AM
> > To: pgsql-jdbc@postgresql.org
> > Subject: [JDBC] Password authentication working with psql, but fails
> > with jdbc for me
> >
> >
> > I have setup a system with postgres 7.3.4-5 on Debian and am
> attempting to
> > access a database from Java.  When I use trust permissions, e.g.
> > I have the
> > following in my pg_hba.conf file:
> >     host all <theuser> <ip-range> <ip-mask> trust
> > everything works fine (with values in angled brackets replaced as
> > appropriate of course).  I can access the database both through psql and
> > with Java.  If I modify pg_hba.conf to use any kind of password
> > authentication (other than Kerberos, which isn't supported in the JDBC
> > drivers), e.g. by using any one of the following lines
> >     host all <theuser> <ip-range> <ip-mask> password
> >     host all <theuser> <ip-range> <ip-mask> crypt
> >     host all <theuser> <ip-range> <ip-mask> md5
> > then psql asks for a password and works properly, but I get the
> following
> > error for the JDBC drivers:
> >     Password authentication failed for user "<theuser>"
> >     at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection
> > AbstractJdbc1Connection.java:202)
> >     at org.postgresql.Driver.connect(Driver.java:122)
> >
> > I get the same results from multiple machine with different JVMs and by
> > trying out pg73jdbc1.jar, pg73jdbc2.jar, and pg73jdbc3.jar (by
> > adding them,
> > one at a time, to the classpath).  In my postgresql.conf file, I do have
> > "tcpip_socket=true".
> >
> > Most of the FAQs and discussions I've found on the topic point to
> > classpath
> > issues.  As far as I can tell though, I'm using the classpath correctly
> > since everything works fine in trust mode.
> >
> > Any ideas what I could be doing incorrectly?
> >
> > Thanks in advance.
> >
> > --Gerald Dalley
> >   dalleyg@mit.edu
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
>

Paul Thomas <paul@tmsl.demon.co.uk> writes:
> On 18/09/2003 21:33 Gerald Dalley wrote:
>>> I'm sure it must just some simple configuration error as I've been
>>> successfully connecting with userid/password since version 7.1.
>>
>> Agreed.  I'm just having trouble thinking of what else to try.

> Maybe use something like tcpdump to see exactly what is being sent across?

Good thought.  Also, try looking in the server log (postmaster's stderr
or syslog output).  In some cases more details about an authorization
failure appear in the log than are told to the client.

            regards, tom lane