BUG #5511: Handling of case in Username and database names are inconsistant.

The following bug has been logged online:

Bug reference: 5511
Logged by: Brett Sutton
Email address: bsutton(at)noojee(dot)com(dot)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: