2

I have a PostgreSQL container set up that I can successfully connect to with Adminer but I'm getting an authentication error when trying to connect via something like DBeaver using the same credentials.

I have tried exposing port 5432 in the Dockerfile and can see on Windows for docker the port being correctly binded. I'm guessing that because it is an authentication error that the issue isn't that the server can not be seen but with the username or password?

Docker Compose file and Dockerfile look like this.

version: "3.7"

services:

  db:
    build: ./postgresql
    image: postgresql
    container_name: postgresql
    restart: always
    environment:
      - POSTGRES_DB=trac
      - POSTGRES_USER=user
      - POSTGRES_PASSWORD=1234
    ports:
      - 5432:5432

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

  nginx:
    build: ./nginx
    image: nginx_db
    container_name: nginx_db
    restart: always
    ports:
      - "8004:8004"
      - "8005:8005"

Dockerfile: (Dockerfile will later be used to copy ssl certs and keys)

FROM postgres:9.6

EXPOSE 5432

Wondering if there is something else I should be doing to enable this to work via some other utility?

Any help would be great.

Thanks in advance.

Update:

Tried accessing the database through the IP of the postgresql container 172.28.0.3 but the connection times out which suggests that PostgreSQL is correctly listening on 0.0.0.0:5432 and for some reason the user and password are not usable outside of Docker even from the host machine using localhost.

demonLaMagra
  • 389
  • 6
  • 22
  • Look in the log file to see why authentication is failing. "I'm guessing that because it is an authentication error that the issue isn't that the server can not be seen but with the username or password?" correct. It doesn't even know what the username and password will be at the time the socket connects. – jjanes Apr 09 '20 at 16:01
  • @jjanes How would I go about viewing the log files? – demonLaMagra Apr 09 '20 at 16:11
  • 1
    You don't even need `Dockerfile` for that. Try to remove `build: ./postgresql` and replace `image: postgresql` by `image: postgres:9.6` – veben Apr 14 '20 at 11:39
  • @veben the Dockerfile is there so I can expand on the image in the future as I will be adding SSL and my own certificates. This issue is that I can not log in to the database outside of docker world. – demonLaMagra Apr 14 '20 at 11:46
  • 2
    without your custom Dockerfile you'll be able to identify whether the issue comes from your Dockerfile of somewhere (thus reducing the scope where you need to look for a problem), it's worth giving it a try I think – Pierre B. Apr 14 '20 at 12:26
  • @veben I have tried the above and removed the Dockerfile. I can still not access the database outside of docker e.g. by some means like DBeaver. Again, I can access through Adminer from the other container. Again, same error authentication error for User : user – demonLaMagra Apr 14 '20 at 13:08
  • 1
    So your problem is not the container itself, but how to connect to it. This article may help you: https://medium.com/better-programming/connect-from-local-machine-to-postgresql-docker-container-f785f00461a7 You may need to create the admin database – veben Apr 14 '20 at 13:45
  • @veben Thank you for the link. I have tried following what guide has stated and again, when trying to connect via the psql (cli) I still get an authentication error but was able to connect via Adminer and from within the container bash. Very frustrating. – demonLaMagra Apr 14 '20 at 13:59

3 Answers3

3

Check your pg_hba.conf file in the Postgres data folder. The default configuration is that you can only login from localhost (which I assume Adminer is doing) but not from external IPs.

In order to allow access from all external addresses vi password authentication, add the following line to your pg_hba.conf:

host    all             all            *            md5

Then you can connect to your postgres DB running in the docker container from outside, given you expose the Port (5432)

drunken_monkey
  • 1,760
  • 1
  • 12
  • 14
  • I'm trying to connect via localhost using DBeaver and not Adminer and it fails because DBeaver lives outside of docker. I'm not trying to connect from an external IP at this moment in time. I have updated my question to reflect. – demonLaMagra Apr 15 '20 at 10:29
  • Yes, but if you connect from outside of the docker-container, you do not connect via localhost, but at least via the docker-bridge. Opening up postgres in pg_hba.conf to accept logins from outside allows you to do that. – drunken_monkey Apr 15 '20 at 11:21
  • okay brilliant. I will give it a go. Any idea how you would update or add that line to the pg_hba.conf from the Dockerfile? – demonLaMagra Apr 16 '20 at 08:13
  • I assume that you want to keep your postgres data when updating your docker-instance. Therefore I would reccomend mounting the postgres data-directory as a named volume. Since the pg_hba.conf-File is in the data directory, you can simply edit it after the first setup and it will stay when you upgrade. Otherwise you will have to pipe the line via echo into the pg_hba.conf file during image creation. example: RUN echo "host all all * md5" >> /var/lib/postgresql/data/pg_hba.conf – drunken_monkey Apr 16 '20 at 09:41
  • is this possible using for docker for windows?? – demonLaMagra Apr 16 '20 at 09:52
0

Use the command docker container inspect ${container_number}, this will tell you which IPaddress:ports are exposed external to the container.

The command 'docker container ls' will help identify the 'container number'

32cupo
  • 850
  • 5
  • 18
  • 36
Colin Browne
  • 1
  • 2
  • I can see using inspect that the postgres is correctly binded to localhost and to port 5432 and I get the authentication error but fine through Adminer – demonLaMagra Apr 15 '20 at 10:32
0

After updating my default db_name, I also had to update the docker-compose myself by explicitly exposing the ports as the OP did

 db:
    image: postgres:13-alpine
    volumes:
      - dev-db-data:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=devdb
      - POSTGRES_USER=user
      - POSTGRES_PASSWORD=1234
    ports:
      - 5432:5432

But the key here was restarting the server! DBeaver has connected to localhost:5432 :)

alar
  • 1