Connection to postgres from a client outside docker has a fatal password authentication for user postgres

Question

My goal is to connect a client DB (DBeaver, Aginity Pro) to a PostgreSQL running on a docker container.

From inside the container, the connection with Postgres is successful. I used Adminer and also PSQL and the connection works. I could create databases, tables, load data. However, using the same connection string from a client DB I got the message: FATAL: password authentication failed for user "postgres"

I'd appreciate if someone has a working connection with PostgresqlDocker and DBeaver on Win10 x64 Pro, please share the environment and what it was done. Thank you.

Here the steps:

1. Download Docker for Windows.
2. Create a YML file.
3. Run Docker.
4. Check connection to Postgres using Adminer and PSQL. Success.
5. Check connection to Postgres using DBeaver and Aginity Pro. Failed.

File YML:

version: '3.6'
services:
  db:
    image: postgres:9.6-alpine
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - 5432:5432
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

Run docker:

docker-compose up -d

Check the connection using Adminer and PSQL:

1. Adminer

http://localhost:8080/?pgsql=db&username=postgres&db=postgres&ns=

It displayed the administration console and I could see the tables, and create my own data structures.

2. PSQL

Here I created tables.

docker exec -it postgres_db_1 bash

bash-5.1#

bash-5.1# su postgres
/ $

/ $ psql
psql (9.6.20)
Type "help" for help.

postgres=#
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

Check connection with DBeaver

DB connection settings

What I did to try to connect Postgres with DBeaver and Aginity Pro:

1. Change the IMAGE. I used additionally three different images:

image: postgres

image: alpine

image: postgres:9-alpine

2. Edit the pg_hba.conf. I added in the section "local":

local   all         postgres                          ident

I changed for ident for trust also. I added at the end:

host   all         all          all               md5

host   all         all          *               trust

I tried using just md5 alone, and trust alone, as well.

3. I changed the user's password inside PSQL using:

ALTER USER postgres PASSWORD 'postgres';

I checked the listening ports with portqryui. It was LISTENING.

=============================================

 Starting portqry.exe -n 127.0.0.1 -e 5432 -p TCP ...


Querying target system called:

 127.0.0.1

Attempting to resolve IP address to a name...


IP address resolved to kubernetes.docker.internal

querying...

TCP port 5432 (unknown service): LISTENING
portqry.exe -n 127.0.0.1 -e 5432 -p TCP exits with return code 0x00000000.

Environment:

• SO: Windows 10 Pro x64
• Docker Desktop version: 3.1.0(51484). Engine: 20.10.2. Compose: 1.27.1. Kubernetes: v1.19.3.
• DBeaver version: Community Edition 7.3.3
• Aginity Pro version: 0.32.836

Highlighted links that I used:

• Connecting to Postgres Docker server - authentication failed

• https://reachmnadeem.wordpress.com/2020/06/02/running-postgresql-database-in-docker-and-connecting-from-host-outside-container/

• Postgresql: password authentication failed for user "postgres"

Answer 1

Check to see if you have a locally installed PostgreSQL server running on your Windows machine.

If so, stop the service and retest the DBeaver connection. If this works then change ports for the local server or docker container's host port.