Thread: BUG #1485: upper case user names / creatuser

The following bug has been logged online:

Bug reference:      1485
Logged by:          Hannes Erven
Email address:      h.e@gmx.at
PostgreSQL version: 7.4.7
Operating system:   2.6.10-gentoo-r6
Description:        upper case user names / creatuser
Details:

I tried creating a new user the way mentioned in the Docs, but (postgres
owning user removed from the output):


$ createuser
Enter name of user to add: Hannes
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER


$ psql template1
Welcome to psql 7.4.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# alter user Hannes  password '***';
ERROR:  user "hannes" does not exist


template1=# select * from pg_catalog.pg_user
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  |
valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+------
----+-----------
 Hannes   |      100 | f           | f        | f         | ******** |
   |
(2 rows)



I then created another user, 'hannes', with createuser, and then I was able
to alter the passwords, altough I now had two users in pg_user: hannes and
Hannes.


template1=# drop user Hannes ;
DROP USER
template1=# select * from pg_user ;
 Hannes   |      100 | f           | f        | f         | ******** |
   |

template1=# drop user Hannes ;
ERROR:  user "hannes" does not exist



I had expected either createuser create a 'hannes' or the alter/drop user
commands to respect case in user names.


with best regards,
-hannes

Hannes Erven wrote:
> The following bug has been logged online:
>
> Bug reference:      1485
> Logged by:          Hannes Erven
> Email address:      h.e@gmx.at
> PostgreSQL version: 7.4.7
> Operating system:   2.6.10-gentoo-r6
> Description:        upper case user names / creatuser
> Details:

> template1=# alter user Hannes  password '***';
> ERROR:  user "hannes" does not exist

> I then created another user, 'hannes', with createuser, and then I was able
> to alter the passwords, altough I now had two users in pg_user: hannes and
> Hannes.

The rules for SQL are: unquoted names get folded to lower case,
double-quoted names are left alone. The error message shows this - it
says "hannes" doesn't exist.

You could argue that the command-line tools should fold to lower-case
too, but that's not standard behaviour for command-line tools.

So - all you need to do is issue SQL like:
   ALTER USER "Hannes" ...

Personally, I keep all objects and users lower-case only, it makes my
life easier.
--
   Richard Huxton
   Archonet Ltd