Link to home
Start Free TrialLog in
Avatar of nexchanger
nexchanger

asked on

postgres user authentication

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;
  }
Avatar of Ajay-Singh
Ajay-Singh

you should add user, and password where you are mentioning jdbc driver details (driver name, driver url)
Avatar of nexchanger

ASKER

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.
> 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?
> 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
Avatar of girionis
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?
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.

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.
> 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
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)
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 ;)

ASKER CERTIFIED SOLUTION
Avatar of Ajay-Singh
Ajay-Singh

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
> 1) Decrypt the stored password and compare it with the user entered password
You can't decrypt MD5
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
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());
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;
  }
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.
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?