Обсуждение: Password authentication failure

Hi all,

I'm currently running 7.1.2 on OpenBSD 2.9-current. I am attempting to
connect to a database over IP, so I have the following 'start' command
in my /etc/rc.local:

su postgres -c '/usr/local/pgsql/bin/pg_ctl -o "-i" -D /web/databases -l
\
/web/databases/logfile.log start > /dev/null'

Also, I have the following in my /web/databases/pg_hba.conf
('/web/databases' is set up as $PGDATA):

host         all         xxx.xxx.xx.0  255.255.255.0       crypt passwd

(obviously, the 'xxx.xxx.xx.0' is my host set.)

I have also created the file /web/databases/passwd which contains the
username/password pair I wish to use to connect to the database.

So, after all this, I have a jdbc connection URL of:

jdbc:postgresql://[my host]/[my db]

(as per the spec)

I also pass in the username and password that I set up using pg_passwd.


Of course, if it worked, I wouldn't have just written all of that :)

The JDBC driver claims "Password authentication failed for user 'XXXX'"

So, does anyone have any ideas?

Many Thanks,

Paul Hart

Okay, a little more digging later, and the plot thickens some:

I'm throwing an exception at line 170 in Connection.java, which suggests
that the response from the server is 'E'. That in and of itself is not
too interesting. What *is* interesting is that I have been denied access
even before the server asks for a password (the code to deal with that
is in lines 200-215 of the same method). This, to me, suggests that
something isn't happening right with regards the protocol matching in
the JDBC client. Or that I'm stupid, I'm willing to accept either
answer.

However, based on that, I would guess that the crypt() problem isn't it.

As for the postmaster log file, there is nothing special in there:

verify_password: password mismatch for 'XXXX'.
Password authentication failed for user 'XXXX'

The stack trace is below. Under that is some commentary on the initial
response I received (thank you!)

java.sql.SQLException: Password authentication failed for user 'XXXX'
     at org.postgresql.Connection.openConnection(Connection.java:170)
     at org.postgresql.Driver.connect(Driver.java:122)
     at java.sql.DriverManager.getConnection(DriverManager.java:517)
     at java.sql.DriverManager.getConnection(DriverManager.java:177)
[...]

Peter Eisentraut writes:
> > The JDBC driver claims "Password authentication failed for
> > user 'XXXX'"
>
> AFAIK, OpenBSD uses MD5 as the default crypt() algorithm,
> whereas the JDBC
> driver assumes the traditional DES-like method.  I suppose you have to
> hack either one to do otherwise.

Uhm... would that then mean that if I was on an OpenBSD machine as a
client (i.e. just running psql), I would be unable (at least without
massaging some code) to access a postgres instance on, say, a Solaris
box? That sounds *very* fishy to me.

Any chance we can have a standardized hashing function built into the
code? If you can't guarantee implementation at the OS level (or you make
false assumptions), there seems to be no other truly suitable
alternative.

Thanks,

Paul Hart

"Paul Hart" <paulhart@io.com> writes:
> The JDBC driver claims "Password authentication failed for user 'XXXX'"

There might be more info in the postmaster's logfile --- did you look?

            regards, tom lane

Paul Hart writes:

> The JDBC driver claims "Password authentication failed for user 'XXXX'"

AFAIK, OpenBSD uses MD5 as the default crypt() algorithm, whereas the JDBC
driver assumes the traditional DES-like method.  I suppose you have to
hack either one to do otherwise.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

"Paul Hart" <paulhart@io.com> writes:
> However, based on that, I would guess that the crypt() problem isn't it.
> As for the postmaster log file, there is nothing special in there:
> verify_password: password mismatch for 'XXXX'.

No, that tells us quite a lot: checking in the source code for that
message, I can see that the backend has received your username and
password, and has found the username in an external password file,
and has attempted to match your password against what was in the
password file.  And that match didn't work.  I think that crypt()
is exactly where your problem is.  Did you build the external
password file with pg_passwd, or manually?

> Uhm... would that then mean that if I was on an OpenBSD machine as a
> client (i.e. just running psql), I would be unable (at least without
> massaging some code) to access a postgres instance on, say, a Solaris
> box? That sounds *very* fishy to me.

If you use the crypt auth method (crypt on client side and send
that across the wire) then yes, the crypt algorithms offered by
client and server C libraries had better match.  However, AFAICT
you are not doing that.  In the regular passwd method what we are
assuming is that the crypt library routine linked into the
postmaster is the same one linked into pg_passwd, or whatever
program you use to maintain the flat password file.  Since these
are on the same machine it's not such a big assumption.

> Any chance we can have a standardized hashing function built into the
> code?

Are you volunteering?  There have been a couple of long discussions
on pghackers about a better password challenge protocol.  IIRC we
came up with a good-looking paper design, but there was a notable
lack of effort on actually making it happen.

            regards, tom lane