Solved

postgres user authentication

Posted on 2006-11-07
18
792 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now