[6.6] Postresql error on auth

[CiotBSD]

Hi all

I've a problem with postgresql when attemp to auth new user.

FYI: I start completely with Postgresql. Usually I use MySQL*
Postgresql has just been installed.

Error message:

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

----

Demo:

srvr# su - _postgresql
srvr$ psql -U postgres
Password for user postgres:
psql (11.5)
Type "help" for help.

postgres=# CREATE USER ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2 WITH PASSWORD
'6TsrKeqq93KyVtc5yVjU9dfZsJkPPtKKdSyEnjypZDkVdgtW4aVN3YNQd5vKoFNx';
CREATE ROLE
postgres=# \connect template1
You are now connected to database "template1" as user "postgres".
template1=# CREATE DATABASE "A5mSHO4SamFa2OJmJC81GbDtUhj4wkyU2" WITH
ENCODING 'UTF-8';"
CREATE DATABASE
template1"# GRANT ALL PRIVILEGES ON DATABASE
"A5mSHO4SamFa2OJmJC81GbDtUhj4wkyU2" TO ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2;
template1"# ALTER DATABASE "A5mSHO4SamFa2OJmJC81GbDtUhj4wkyU2" OWNER TO
ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2;
template1"# \q
Use control-D to quit.
template1"# \q
srvr$ exit

In fact, I have one created user, named ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2
His password:
6TsrKeqq93KyVtc5yVjU9dfZsJkPPtKKdSyEnjypZDkVdgtW4a VN3YNQd5vKoFNx
One created DB, named:
A5mSHO4SamFa2OJmJC81GbDtUhj4wkyU2

And the user ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2 has all rights on DB
A5mSHO4SamFa2OJmJC81GbDtUhj4wkyU2

Right?!

But, when I attempt to connect to DB with user, I have the above the error message:

# psql -U ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2
Password for user ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2:
psql: FATAL:  password authentication failed for user
"ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2"

# psql -U ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2 -d
A5mSHO4SamFa2OJmJC81GbDtUhj4wkyU2
Password for user ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2:
psql: FATAL:  password authentication failed for user
"ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2"

Ok I found it's necessary to change informations into file 'pg_hba.conf'. I set as:

# grep A5mSHO4SamFa2OJmJC81GbDtUhj4wkyU2 /var/postgresql/data/pg_hba.conf
local A5mSHO4SamFa2OJmJC81GbDtUhj4wkyU2 all        trust

And restart the service/daemon postgresql.

Despite, I cant connect on!

---

Any idea?

FYI: I posted this issue, today, on list @misc.

[fvgit]

I'm not an expert, but I think your problem could be the following:

# If you run psql without a database name as an option, it tries to
# connect to a database with the same name as the $USER psql runs as.

Your command should therefore be:

 psql -U ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2 A5mSHO4SamFa2OJmJC81GbDtUhj4wkyU2

Edit: Damn, I just realised you already tried that. In that case I'm out of ideas.

[CiotBSD]

On the list misc@, the reply is:

- the good command to create with LOGIN to permit connexion, as:

CREATE USER ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2 WITH LOGIN PASSWORD
'6TsrKeqq93KyVtc5yVjU9dfZsJkPPtKKdSyEnjypZDkVdgtW4aVN3YNQd5vKoFNx';

and using lower letter…

CREATE USER username WITH LOGIN PASSWORD 'mybeautifull4password';