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?