[6.6] PostgreSQL server: fail to auth

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[6.6] PostgreSQL server: fail to auth

Stephane HUC "PengouinBSD"
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>[hidden email]</mail>

Reply | Threaded
Open this post in threaded view
|

Re: [6.6] PostgreSQL server: fail to auth

chuck-2
> 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>[hidden email]</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

Reply | Threaded
Open this post in threaded view
|

Re: [6.6] PostgreSQL server: fail to auth

Stephane HUC "PengouinBSD"
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, [hidden email] 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>[hidden email]</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>[hidden email]</mail>

Reply | Threaded
Open this post in threaded view
|

Re: [6.6] PostgreSQL server: fail to auth

Mike Larkin-2
In reply to this post by Stephane HUC "PengouinBSD"
On Wed, Oct 30, 2019 at 05:45:15PM +0100, Stéphane HUC "PengouinBSD" wrote:

> 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>[hidden email]</mail>
>

I think the problem might be that the user tC1xQOo7KSLdnc5J1VmGFChhodnH
doesn't have the proper password of 47znNtnBlhViIzSm9GZFSQirlb8HIQcSl,
which according to your PG installation, is required for accessing the
database 'dqXijG7C1GGkuu98ZirPFwu2XTMo3V0QlF4'.

Of course, it also might be because the user ZYo43MnD5aW5P4wqqeSE9aInA5SL9HURvpmpOZ
doesn't have access to database NfyhoNhUQWZiZKOTtl0K7mR4yPEsMy8sJC
to begin with.

Seriously, did you expect someone to take the time to decipher what you
wrote there?

-ml