Solved

postgres user authentication

Posted on 2006-11-07
18
803 Views
Last Modified: 2008-01-09
Hi experts,

I am trying to authenticate a user with postgres DB. I have created a postgres user and when I use the method below, it seems to work fine for a database installation on windows - i.e it returns a null Connection object when the authentication fails, however for a postgres installation on unix, it returns a non null Connection object even when the authentication fails. I am relying on this method to authenticate a user based on if the connection object is null or not.
I am using the jdbc driver from  postgresql.jdbc.jar with Java 5.0

Am I missing some thing trivial here. I cant understand why its behaving differently in the above cases. Any suggestions regarding this or any alternate ways of authenticating a postgres user from java code like a third party library would be appreciated.

public static Connection getConnection(String JNDIName, String username, String password) throws FrameworkException {

    Connection conn = null;
    try {
      DataSource ds = ServiceLocator.getInstance().getDatasource(JNDIName, true);

      if (username == null || (username != null && "".equals(username))) {
        //Retrieve the connection with the DS configured user/passowrd
        conn = ds.getConnection();
      }
      else {
        conn = ds.getConnection(username, password);
      }
    }
    catch (SQLException se) {
      throw new FrameworkException("Error retrieving connection for: " + JNDIName, se);
    }
    catch (ServiceLocatorException sle) {
      throw new FrameworkException("Could not retrieve a datasource for " + JNDIName, sle);
    }
    return conn;
  }
0
Comment
Question by:nexchanger
  • 7
  • 6
  • 4
18 Comments
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 17887989
you should add user, and password where you are mentioning jdbc driver details (driver name, driver url)
0
 

Author Comment

by:nexchanger
ID: 17888031
I am sorry I don't get you. I am getting the DataSource using a JNDI name and then trying to get a connection object for a valid username/password combination with
conn = ds.getConnection(username, password);

My problem is it works if I run the same code and connect to a database on windows (of course by changing the url in the properties) and it doesnt work the same way when I try and connect to a database on unix.
On unix, it just connects even if the password supplied is wrong or empty, however it doesnt connect if the username supplied doesnt exist - in this case it throws a SQLException.
0
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 17888169
> On unix, it just connects even if the password supplied is wrong or
> empty, however it doesnt connect if the username supplied doesnt exist
> - in this case it throws a SQLException.

this should be happening on windows as well?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 17888173
> I am sorry I don't get you. I am getting the DataSource using a JNDI
> name and then trying to get a connection object for a valid username/password
> combination with
my point is to keep user name and password at the place where you are mentioning jdbc url, probably on the server where jndi is bound
0
 
LVL 35

Expert Comment

by:girionis
ID: 17888187
It *should* throw an exception if there is something wrong, this method throws an SQLException. This should work the same regardless if it's WIndows or linux. Are you suer Windows do not throw exception and just return a null object?
0
 

Author Comment

by:nexchanger
ID: 17888282
Windows:
Returns a null connection object and throws SQLException if either an invalid username or invalid password is supplied

Unix:
Returns a non-null connection object and doesnt throw any exception if a valid username is supplied with an invalid password.
However if an invalid username is supplied - doesnt matter if password is empty or invalid - returns a null connection object and it throws a SQLException.
It somehow seems to disregard the password.

0
 
LVL 35

Expert Comment

by:girionis
ID: 17888305
Weird... Not sure if this is a "feature" or a bug. Are you trying it with two databases, one on Windows and one on Linux? It might be a database issue.
0
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 17888442
> Returns a null connection object and throws SQLException if either an
> invalid username or invalid password is supplied


are you sure? It can either throw an exception or return a value, NOT both
0
 

Author Comment

by:nexchanger
ID: 17888507
Yes, after executing the following line:
conn = ds.getConnection(username, password);
The conn is null and I get this exception below. (However this is only on Windows DB,  on Unix its as mentioned above)

Caused by: org.postgresql.util.PSQLException: Connection rejected: FATAL: password authentication failed for user "andy".
      at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:275)
      at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:94)
      at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:65)
      at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:117)
      at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
      at org.postgresql.jdbc3g.Jdbc3gConnection.<init>(Jdbc3gConnection.java:24)
      at org.postgresql.Driver.connect(Driver.java:235)
      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:151)
0
 

Author Comment

by:nexchanger
ID: 17888526
I guess I would have to change my implementation now:
i.e I can extract the username and password from pg_roles table.
I guess postgres uses MD5 hash for password encryption. So my question is which of the below is possible:
1) Decrypt the stored password and compare it with the user entered password
2) Better still encrypt the password entered by the user exactly the same way as postgres does and compare it with the stored encrypted password.

If any of you could help me with either of the above two scenarios (prefereably the second), I would appreciate the help.
The points are still valid for this one ;)

0
 
LVL 23

Accepted Solution

by:
Ajay-Singh earned 200 total points
ID: 17888545
may be it doesn't have to do anything with os. In postgres database, you can define ACL based on IP address. Looks to me that the ip of windows box has password set and unix box doesn't have
0
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 17888554
> 1) Decrypt the stored password and compare it with the user entered password
You can't decrypt MD5
0
 

Author Comment

by:nexchanger
ID: 17888567
You bet! But I dont connect to the DB directly from where the client is running, I always go through a server and that server has the required entry in the pg_hba.conf
0
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 17888582
for #2, you can do something like this:

String passwordText = ... // Password entered by user.
MessageDigest md = MessageDigest.getInstance("MD5");
byte[] token = md.digest(passwordText.getBytes());
0
 
LVL 35

Expert Comment

by:girionis
ID: 17888599
The best thing you can do is to throw another exception is the conn is null

public static Connection getConnection(String JNDIName, String username, String password) throws FrameworkException {

    Connection conn = null;
    try {
      DataSource ds = ServiceLocator.getInstance().getDatasource(JNDIName, true);

      if (username == null || (username != null && "".equals(username))) {
        //Retrieve the connection with the DS configured user/passowrd
        conn = ds.getConnection();
      }
      else {
        conn = ds.getConnection(username, password);
      }
    }
    catch (SQLException se) {
      throw new FrameworkException("Error retrieving connection for: " + JNDIName, se);
    }
    catch (ServiceLocatorException sle) {
      throw new FrameworkException("Could not retrieve a datasource for " + JNDIName, sle);
    }
   
    if (conn == null)
    {
        throw new FrameWorkException("blahbla..." + JNDIName, se);
    }
    return conn;
  }
0
 

Author Comment

by:nexchanger
ID: 17888678
It is not a problem if the connection object returned is null. Actually that is what I want if the authentication fails.
Now, windows DB behaves like I expect it to - returns a null connection object if auth fails.
So, I really dont see any benefit of throwing a new exception if its null. On the other hand, if the conn object is not null (for unix) there is no way I am able to differentiate if it succeeded or not.
0
 
LVL 35

Expert Comment

by:girionis
ID: 17888731
It is weird though why it returns a non-null connectionobject if you supply an invalid password. Can you use this object normally as you would do with a "proper" connection?
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question