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

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(at)noojee(dot)com(dot)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
>