Re: [6.6] PostgreSQL server: fail to auth

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>bsd@stephane-huc.net</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