Обсуждение: 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@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:

"Brett Sutton" <bsutton@noojee.com.au> writes:
> Essentially if you peform:
> create user Abc;
> Postgres creates a user abc (as expected).

Yeah, because *in the context of SQL* the standard mandates
case-folding.  But note that the actual user name here is "abc".
Not "Abc".

> The problem is that you cannot the use mixed case name in a jdbc url.import

I'm not sure that JDBC would be doing you any favors to try to support
that.  If it were to fold case for usernames in connection URLs, then
you'd have a problem logging into users that actually *were* mixed case.
The next step would be to invent a quoting convention for usernames in
URLs, and then you just have a mess.

We went around on this many years ago in the context of what psql should
do with user and database names supplied on the command line.  The
eventual conclusion, which has worked well since then, was that such
names should be taken as-is and not folded.  Now that was partially
forced by the fact that the shell would interfere with any plausible
quoting convention, but I think it's still a good precedent for handling
user and database names in other non-SQL contexts such as URLs.

            regards, tom lane

Tom,
thanks for the response.

It seems to me that whatever jdbc/psql does it needs to do it consistently.

The current system appears to be inconsistent and will certainly cause
problems for anyone creating/using an new user account via a java
application.

Our use case is as follows:

Person enters the name of a new psql database and a new psql username.
They enter the username in mixed case.
We create the database and username by passing in the mixed case
username which psql then folds.
We then attempt to authenticate with the new username which psql does
not fold so authentication fails.

I wouldn't have thought that it would not be up to jdbc to do the
folding but rather the psql server.
The psql server is clearly folding the username when it is created so
why wouldn't it also be folding the username when authenticating.

I agreed that the whole quoted identifier issue is a can of worms, but I
don't think it is necessary.
Providing that psql always folds consistently then we get a result that
makes sense.
It also sounds like always folding the username is more inline with the
sql standards.

Thoughts?

Brett






On 19/06/10 00:18, Tom Lane wrote:
> "Brett Sutton"<bsutton@noojee.com.au>  writes:
>
>> Essentially if you peform:
>> create user Abc;
>> Postgres creates a user abc (as expected).
>>
> Yeah, because *in the context of SQL* the standard mandates
> case-folding.  But note that the actual user name here is "abc".
> Not "Abc".
>
>
>> The problem is that you cannot the use mixed case name in a jdbc url.import
>>
> I'm not sure that JDBC would be doing you any favors to try to support
> that.  If it were to fold case for usernames in connection URLs, then
> you'd have a problem logging into users that actually *were* mixed case.
> The next step would be to invent a quoting convention for usernames in
> URLs, and then you just have a mess.
>
> We went around on this many years ago in the context of what psql should
> do with user and database names supplied on the command line.  The
> eventual conclusion, which has worked well since then, was that such
> names should be taken as-is and not folded.  Now that was partially
> forced by the fact that the shell would interfere with any plausible
> quoting convention, but I think it's still a good precedent for handling
> user and database names in other non-SQL contexts such as URLs.
>
>             regards, tom lane
>