In postgresql 9.5.1, I created a user called GuardDog_02. I gave it a password. And I gave it permissions to create db and user:
CREATE USER GuardDog_02 WITH PASSWORD 'apple100' CREATEDB CREATEUSER;
Then under the GuardDog_02 system user (Mac OSX):
$ psql -d template1
Password:
psql: FATAL: password authentication failed for user "GuardDog_02"
I updated password just to make sure I typed it right:
template1=# ALTER USER GuardDog_02 WITH PASSWORD 'apple100';
Still it says password authentication fails. I checked the pg_hba.conf file too:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres md5
#host replication postgres 127.0.0.1/32 md5
#host replication postgres ::1/128 md5
What am I doing wrong?
Best Answer
When issuing
CREATE USER GuardDog_02...
the exact user name created isguardgog_02
because of SQL case folding. You may check that with\du
in psql.But according to that error message:
the username you're trying to log in with is
GuardDog_02
(G,D in capital letters), which does not exist with this capitalizationTo create that exact name, the identifier must be enclosed in double quotes, as in:
Or, alternatively, if you don't want to recreate the user, log in with
psql -U guarddog_02
.