We help IT Professionals succeed at work.

postgres user authentication

nexchanger
nexchanger asked
on
Medium Priority
858 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;
  }
Comment
Watch Question

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

Author

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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?

Author

Commented:
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.

CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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

Author

Commented:
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)

Author

Commented:
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 ;)

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
> 1) Decrypt the stored password and compare it with the user entered password
You can't decrypt MD5

Author

Commented:
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());
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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;
  }

Author

Commented:
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.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.