Cannot log in as newly created user

Hi, I'd like to add another error example to my original query, please.
This is a simpler example of another method I have tried which does not
work for me (and which I'm sure people will advise me to try):

postgres-# CREATE USER EmmaChwan WITH ENCRYPTED PASSWORD 'password';

CREATE ROLE

postgres=# \q

C:\Users\EmmaChwan>psql

Password:
psql: FATAL: password authentication failed for user "EmmaChwan"

Thanks! Emma

-------- Forwarded Message --------
Subject: Cannot log in as newly created user
Date: Sat, 19 Dec 2015 19:14:09 +0800
From: Emma Saurus <emmasaurus@westnet.com.au>
To: pgsql-bugs@postgresql.org

Hello, I hope that you may be able to help me with this very basic
problem. I have read many fora posts as well as your documentation, and
have been unable to resolve it myself.

I am running PostgreSQL 9.3.10. I can log in as the default postgres
user. I follow instructions to create a new role with a password. I log
out and try to log in as the new user, but am not permitted. I've tried
several variations of code without success. Latest, most
out-of-the-manual code as follows:

---
postgres=# CREATE ROLE EmmaChwan CREATEDB LOGIN PASSWORD 'password';

CREATE ROLE

postgres=# \q

C:\Users\EmmaChwan>psql

Password:
psql: FATAL: password authentication failed for user "EmmaChwan"

---

I don't know if it's related, but just for the record: I had
installation issues, too.

I tried to install postgres 9.4 several times with the EnterpriseDB
Windows package (also re-downloaded the package a couple of times). I
was never given the option of entering a password, and the generic
password 'postgres' did not allow me to log in. I tried installing
without any antivirus/firewall software active; still no success.

When I installed version 9.3, I was given the password creation screen
and could log in via command line with the password I set. Success! Kinda...

I'm running Windows 8 x64 on an Intel processor, if that information is
of any use at all. I've seen a number of users post with the same 'no
password screen' problem, and each was told, "Nuh-uh, you did too get a
password screen." I can cast one vote in favor of those posters not
being crazy / premature 'Next'-button clickers.

Any help you can offer would be greatly appreciated. It's very
discouraging to get stuck on something so basic!
Thanks, Emma

Emma Saurus <emmasaurus@westnet.com.au> writes:

postgres=# CREATE ROLE EmmaChwan CREATEDB LOGIN PASSWORD 'password';

CREATE ROLE

postgres=# \q

C:\Users\EmmaChwan>psql

Password:
psql: FATAL: password authentication failed for user "EmmaChwan"

Your problem here is case sensitivity, or lack of it. The unquoted
identifier EmmaChwan is smashed to lower case when seen in a SQL
command, so the actually created user name is "emmachwan". But when
you submit a user (or database) name in a connection request, that's
not SQL language so no quote-stripping or case-folding is done.

You can make it work with

CREATE ROLE "EmmaChwan" ...

but bear in mind you'll have to double-quote that user name *every*
time you refer to it in SQL. The same goes for any other mixed-case
name that you want to be really truly mixed-case and not case-insensitive.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Try:

CRATE USER "EmmaChwan"

See the last paragraph in 4.1.1 for why:

http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html

On Sun, Dec 20, 2015 at 10:47 PM, Emma Saurus <emmasaurus@westnet.com.au>
wrote:

On Sun, Dec 20, 2015 at 11:47 PM, Emma Saurus <emmasaurus@westnet.com.au> wrote:

This is a simpler example of another method I have tried which
does not work for me

postgres-# CREATE USER EmmaChwan WITH ENCRYPTED PASSWORD 'password';
CREATE ROLE

That created a user named "emmachwan" with that password.

psql: FATAL: password authentication failed for user "EmmaChwan"

This is an attempt to log in as a completely different user: "EmmaChwan".

See the previous responses.

Kevin Grittner

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs