Re: [6.6] PostgreSQL server: fail to auth

Hi, and thanks.

With your reply and lower letter is better.
it seems not necessary to set at 'trust', just 'md5' into file
pg_hba.conf. (gitea need 'md5')

:D

Le 30/10/2019 à 21:25, chuck@qatland.com a écrit :
>> Hi,
>>
>> On my OpenBSD server, run with 6.6, I installed Postgresql server.
>>
>> I have a problem with auth. solene@ is informed of this problem; but
>> I'll tell you about it. Perhaps you have a solution?
>>
>> FYI: I start completely with Postgresql. Usually I use MySQL*
>> Postgresql has just been installed.
>>
>> ----
>>
>> The error message:
>>
>> psql: FATAL: password authentication failed for user "***"
>>
>> ----
>>
>> Demo:
>>
>> # su - _postgresql
>> arrakiss$ 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
>> arrakiss$ exit
>>
>> In fact, I have one created user, named ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2
>> His password:
>> 6TsrKeqq93KyVtc5yVjU9dfZsJkPPtKKdSyEnjypZDkVdgtW4aVN3YNQd5vKoFNx
>> 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, please?!
>>
>> --
>> ~ " Fully Basic System Distinguish Life! " ~ " Libre as a BSD " +=<<<
>> ----
>> <me>Stephane HUC as PengouinBSD or CIOTBSD</me>
>> <mail>bsd@stephane-huc.net</mail>
>>
>>
>
> The CREATE USER command is wrong, you forgot to add the LOGIN right.
>
> The below should work for you.
>
> CREATE USER ghiMDQawgogUfFRTikPoWsUFN1xX8bgz2 WITH LOGIN PASSWORD
> '6TsrKeqq93KyVtc5yVjU9dfZsJkPPtKKdSyEnjypZDkVdgtW4aVN3YNQd5vKoFNx';
>
> Also depending on the version of Postgresql the capital letters in the
> username will be lowercase. This will affect the login ability.
>
> Have Fun!
> Chuck Hall
>

--
~ " Fully Basic System Distinguish Life! " ~ " Libre as a BSD " +=<<<
----
<me>Stephane HUC as PengouinBSD or CIOTBSD</me>
<mail>bsd@stephane-huc.net</mail>