BUG #5511: Handling of case in Username and database names are inconsistant. - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5511
Logged by:          Brett Sutton
Email address:      bsutton@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: