Thread: BUG #5431: CREATE USER is not case sensitive, but psql command line arguments are

The following bug has been logged online:

Bug reference:      5431
Logged by:          Evan Nelson
Email address:      ean5533@gmail.com
PostgreSQL version: 8.4
Operating system:   Ubuntu 9.10
Description:        CREATE USER is not case sensitive, but psql command line
arguments are
Details:

When creating a user (via CREATE USER/ROLE), the username you choose is not
case sensitive (e.g. "nEWuSer" is saved as "newuser"). However, the command
line arguments for psql ARE case sensitive. This means that if I create a
user like "newUser", then try to log into postgres via psql as "newUser",
I'll be told the the role doesn't exist. Then, when I log into postgres as
an existing user and try to re-create the role "newUser", postgres will tell
me that the role already exists -- a frustrating cycle. See below for a
quick example of what I mean:


----------------------------------

::~$ psql -d MyDatabase
psql (8.4.3)
Type "help" for help.

MyDatabase=# create user newUser;
CREATE ROLE
MyDatabase=# \q
::~$ psql -d MyDatabase -U newUser;
psql: FATAL:  role "newUser" does not exist
::~$ psql -d MyDatabase;
psql (8.4.3)
Type "help" for help.

MyDatabase=# create user newUser;
ERROR:  role "newuser" already exists
MyDatabase=# \q
::~$ psql -d MyDatabase -U newuser;
psql (8.4.3)
Type "help" for help.

MyDatabase=>

----------------------------------


I'm not sure what the proper solution for this problem is. Perhaps psql
should automatically convert typed usernames to lower case? Maybe warn about
case sensitivity when attempting to log in with a non-lowercase username? Or
maybe even issue a warning when CREATE USER is typed with a non-lowercase
username? Something else?

Evan Nelson escribió:
>
> The following bug has been logged online:
>
> Bug reference:      5431
> Logged by:          Evan Nelson
> Email address:      ean5533@gmail.com
> PostgreSQL version: 8.4
> Operating system:   Ubuntu 9.10
> Description:        CREATE USER is not case sensitive, but psql command line
> arguments are
> Details:
>
> When creating a user (via CREATE USER/ROLE), the username you choose is not
> case sensitive (e.g. "nEWuSer" is saved as "newuser"). However, the command
> line arguments for psql ARE case sensitive. This means that if I create a
> user like "newUser", then try to log into postgres via psql as "newUser",
> I'll be told the the role doesn't exist. Then, when I log into postgres as
> an existing user and try to re-create the role "newUser", postgres will tell
> me that the role already exists -- a frustrating cycle.

Right.  This is because of a mixture of trying to adhere to the SQL
standard (which mandates that unquoted identifiers are case-folded) and
convenience of usafe of the shell command line.  The strict solution would
be to case-fold unquoted identifiers passed as arguments to psql, so
you'd be required to type something like
psql \"nEWuSer\"
to be able to log in as such a user -- otherwise the quotes would be
stripped by the shell.  However, this is so inconvenient that it has
been dumped in favor of not doing case-folding of idenfiers in shell
command line arguments, regardless of quoting.

In short, not a bug ...

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> wrote:

> you'd be required to type something like
> psql \"nEWuSer\"

Although we do that with some command-line arguments, like the
pg_dump -t switch.  Not arguing for any particular course here, just
noting the inconsistency.

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Alvaro Herrera <alvherre@commandprompt.com> wrote:
>> you'd be required to type something like
>> psql \"nEWuSer\"

> Although we do that with some command-line arguments, like the
> pg_dump -t switch.  Not arguing for any particular course here, just
> noting the inconsistency.

Right.  -t is like that because it requires parsing anyway: you can type
either -t tablename or -t schema.tablename, and so there has to be some
quoting convention or you couldn't deal with names with embedded dots
(not to mention the possibility of wanting to quote a wildcard
character).  In places where a command-line argument is just a name,
though, we've chosen to interpret it literally rather than introduce
quoting issues.

Short answer is that the inconsistencies in this area are intentional.
Ideally it'd be better not to have them, but in practice things are more
convenient with them, because of the conflicts between SQL and shell
quoting conventions.

            regards, tom lane