The following bug has been logged online:
Bug reference: 5511
Logged by: Brett Sutton
Email address: bsut...@noojee.com.au
PostgreSQL version: 8.4.4
Operating system: Ubuntu 10.04
Description: Handling of case in Username and database names are
inconsistant.
Details:
When using jdbc and a username or database is created using mixed case you
cannot then access either with mixed case.
Essentially if you peform:
create user Abc;
Postgres creates a user abc (as expected).
The problem is that you cannot the use mixed case name in a jdbc url.import
java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class PostgresCaseBug
{
static public void main(String args[])
{
String adminUsername = "postgres";
// NOTE: change this password to match your local db.
String adminPassword = "adminPasswordGoesHere";
// Assumes that you have postgres running on localhost.
String server = "localhost";
String databaseName = "testdb";
String username = "testUser"; // Note the username is mixed
case.
String password = "password";
String adminURL = "jdbc:postgresql://" + server +
"/postgres?user=" +
adminUsername //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
+ "&password=" + adminPassword;//$NON-NLS-1$
Connection con = null;
PreparedStatement stmt = null;
try
{
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(adminURL);
String sql = "create user " + username + " with
password '" + password +
"'"; //$NON-NLS-1$ //$NON-NLS-2$//$NON-NLS-3$
stmt = con.prepareStatement(sql);
stmt.execute();
stmt.close();
System.out.println("User " + username + " created");
//$NON-NLS-1$
//$NON-NLS-2$
// Now create the database and make the new user the
owner.
stmt = con.prepareStatement("create database " +
databaseName + " with
owner " + username); //$NON-NLS-1$ //$NON-NLS-2$
stmt.execute();
System.out.println("Database " + databaseName + "
created");
//$NON-NLS-1$//$NON-NLS-2$
con.close();
// First prove we can connect if we artificially force
the username to
// all lower case
String url = "jdbc:postgresql://" + server + "/" +
databaseName +
"?user=" + username.toLowerCase() //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
+ "&password=" + password;//$NON-NLS-1$
// we can connect without a problem.
con = DriverManager.getConnection(url);
System.out.println("Connected with url=" + url);
//$NON-NLS-1$
con.close();
// Now attempt to connect with the user we just created
without force the
username
// to lower case.
url = "jdbc:postgresql://" + server + "/" +
databaseName + "?user=" +
username //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
+ "&password=" + password;//$NON-NLS-1$
// Throws exception: password authentication failed for
user "testUser"
// Even though we just created the user. If we attempt
the connection
// using an all lower case version of the account then
the authentication
succeeds.
con = DriverManager.getConnection(url); // throws an
exception even
though we just created the user.
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
finally
{
try
{
if (stmt != null && stmt.isClosed() == false)
stmt.close();
if (con != null && con.isClosed() == false)
con.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// Now we do it all again to prove that the same problem exists
for the
database name.
databaseName = "testDB2"; //Note the mixed case. This will
cause problems.
//$NON-NLS-1$
username = "testuser2"; //$NON-NLS-1$
try
{
Class.forName("org.postgresql.Driver"); //$NON-NLS-1$
con = DriverManager.getConnection(adminURL);
String sql = "create user " + username + " with
password '" + password +
"'"; //$NON-NLS-1$ //$NON-NLS-2$//$NON-NLS-3$
stmt = con.prepareStatement(sql);
stmt.execute();
stmt.close();
System.out.println("User " + username + " created");
//$NON-NLS-1$
//$NON-NLS-2$
// Now create the database and make the new user the
owner.
stmt = con.prepareStatement("create database " +
databaseName + " with
owner " + username); //$NON-NLS-1$ //$NON-NLS-2$
stmt.execute();
System.out.println("Database " + databaseName + "
created");
//$NON-NLS-1$//$NON-NLS-2$
con.close();
// First prove we can connect if we artificially force
the database name
to
// all lower casewe just created without force the
username
// to lower case.
String url = "jdbc:postgresql://" + server + "/" +
databaseName.toLowerCase() + "?user=" + username //$NON-NLS-1$ //$NON-NLS-2$
//$NON-NLS-3$
+ "&password=" + password;//$NON-NLS-1$
// we can connect without a problem.
con = DriverManager.getConnection(url);
System.out.println("Connected with url=" + url);
//$NON-NLS-1$
con.close();
// Now attempt to connect with the database using its
original camel
case.
url = "jdbc:postgresql://" + server + "/" +
databaseName + "?user=" +
username //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
+ "&password=" + password;//$NON-NLS-1$
// Throws exception: password authentication failed for
user "testUser"
// Even though we just created the user. If we attempt
the connection
// using an all lower case version of the account then
the authentication
succeeds.
con = DriverManager.getConnection(url); // throws an
exception even
though we just created the user.
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
finally
{
try
{
if (stmt != null && stmt.isClosed() == false)
stmt.close();
if (con != null && con.isClosed() == false)
con.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
e.g.
jdbc:posgresql://localhost/database?user=Abc&password=xx
will fail with a message 'password authentication failed for user "Abc"
The same problem exist when creating a database and then attempting to
connect to it via a url using mixed case.
The following java program reproduces both issues:
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
