Thread: Cannot log in as newly created user
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
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
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: > 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> <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 > > > > >
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