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 ().getData source(JND IName, 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;
}
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
if (username == null || (username != null && "".equals(username))) {
//Retrieve the connection with the DS configured user/passowrd
conn = ds.getConnection();
}
else {
conn = ds.getConnection(username,
}
}
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;
}
you should add user, and password where you are mentioning jdbc driver details (driver name, driver url)
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.
conn = ds.getConnection(username,
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?
> 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
> 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
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?
ASKER
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.
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
> invalid username or invalid password is supplied
are you sure? It can either throw an exception or return a value, NOT both
ASKER
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.PSQLEx ception: Connection rejected: FATAL: password authentication failed for user "andy".
at org.postgresql.core.v3.Con nectionFac toryImpl.d oAuthentic ation(Conn ectionFact oryImpl.ja va:275)
at org.postgresql.core.v3.Con nectionFac toryImpl.o penConnect ionImpl(Co nnectionFa ctoryImpl. java:94)
at org.postgresql.core.Connec tionFactor y.openConn ection(Con nectionFac tory.java: 65)
at org.postgresql.jdbc2.Abstr actJdbc2Co nnection.< init>(Abst ractJdbc2C onnection. java:117)
at org.postgresql.jdbc3.Abstr actJdbc3Co nnection.< init>(Abst ractJdbc3C onnection. java:30)
at org.postgresql.jdbc3g.Jdbc 3gConnecti on.<init>( Jdbc3gConn ection.jav a:24)
at org.postgresql.Driver.conn ect(Driver .java:235)
at org.jboss.resource.adapter .jdbc.loca l.LocalMan agedConnec tionFactor y.createMa nagedConne ction(Loca lManagedCo nnectionFa ctory.java :151)
conn = ds.getConnection(username,
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.PSQLEx
at org.postgresql.core.v3.Con
at org.postgresql.core.v3.Con
at org.postgresql.core.Connec
at org.postgresql.jdbc2.Abstr
at org.postgresql.jdbc3.Abstr
at org.postgresql.jdbc3g.Jdbc
at org.postgresql.Driver.conn
at org.jboss.resource.adapter
ASKER
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 ;)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> 1) Decrypt the stored password and compare it with the user entered password
You can't decrypt MD5
You can't decrypt MD5
ASKER
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.get Bytes());
String passwordText = ... // Password entered by user.
MessageDigest md = MessageDigest.getInstance(
byte[] token = md.digest(passwordText.get
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 ().getData source(JND IName, 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("blahbl a..." + JNDIName, se);
}
return conn;
}
public static Connection getConnection(String JNDIName, String username, String password) throws FrameworkException {
Connection conn = null;
try {
DataSource ds = ServiceLocator.getInstance
if (username == null || (username != null && "".equals(username))) {
//Retrieve the connection with the DS configured user/passowrd
conn = ds.getConnection();
}
else {
conn = ds.getConnection(username,
}
}
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("blahbl
}
return conn;
}
ASKER
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.
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?