Skip site navigation (1) Skip section navigation (2)

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

From: "S(dot) Brett Sutton" <bsutton(at)noojee(dot)com(dot)au>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5511: Handling of case in Username and database names are inconsistant.
Date: 2010-06-21 00:34:58
Message-ID: 4C1EB3B2.4060200@noojee.com.au (view raw or flat)
Thread:
Lists: pgsql-bugs
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
>

In response to

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2010-06-21 08:05:43
Subject: Bug in PL/pgSQL FOR cursor variant
Previous:From: Tom LaneDate: 2010-06-21 00:15:28
Subject: Re: BUG #5514: no relation entry for relid X

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group