[FIXED] Newbie needs help understanding Docker Postgres django.db.utils.OperationalError: FATAL: password authentication failed for user "postgres"

Issue

I am a newbie trying to follow this tutorial. https://testdriven.io/blog/dockerizing-django-with-postgres-gunicorn-and-nginx/#postgres

I succeeded in building the docker containers but got this error django.db.utils.OperationalError: FATAL: password authentication failed for user "postgres".

My question is why has authentication failed when the python and postgres containers env shows the correct user and password? And how can I solve the problem?

I also tried poking around to find pg_hba.conf (because previous answers suggested editing it) but /etc/postgresql does not seem to exist for me.

This is from my python container.

# python
Python 3.8.2 (default, Apr 23 2020, 14:32:57)
[GCC 8.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import os
>>> os.environ.get("SQL_ENGINE")
'django.db.backends.postgresql'
>>> os.environ.get("SQL_DATABASE")
'postgres'
>>> os.environ.get("SQL_USER")
'postgres'
>>> os.environ.get("SQL_PASSWORD")
'password123'
>>> os.environ.get("SQL_HOST")
'db'
>>> os.environ.get("SQL_PORT")
'5432'

This is from my postgres container.

/ # env
HOSTNAME=6715b7624eba
SHLVL=1
HOME=/root
PG_VERSION=13.2
TERM=xterm
POSTGRES_PASSWORD=password123
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
POSTGRES_USER=postgres
LANG=en_US.utf8
PG_MAJOR=13
PG_SHA256=5fd7fcd08db86f5b2aed28fcfaf9ae0aca8e9428561ac547764c2a2b0f41adfc
PWD=/
POSTGRES_DB=postgres
PGDATA=/var/lib/postgresql/data
/ # ls
bin                         lib                         root                        tmp
dev                         media                       run                         usr
docker-entrypoint-initdb.d  mnt                         sbin                        var
etc                         opt                         srv
home                        proc                        sys
/ # cd etc
/etc # ls
alpine-release  fstab           hosts           issue           modules-load.d  opt             periodic        resolv.conf     shadow-         sysctl.d
apk             group           init.d          logrotate.d     motd            os-release      profile         securetty       shells          terminfo
conf.d          group-          inittab         modprobe.d      mtab            passwd          profile.d       services        ssl             udhcpd.conf
crontabs        hostname        inputrc         modules         network         passwd-         protocols       shadow          sysctl.conf
/etc #

Traceback

Watching for file changes with StatReloader

Performing system checks...


System check identified no issues (0 silenced).

Exception in thread django-main-thread:

Traceback (most recent call last):

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection

self.connect()

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 200, in connect

self.connection = self.get_new_connection(conn_params)

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/postgresql/base.py", line 187, in get_new_connection

connection = Database.connect(**conn_params)

File "/usr/local/lib/python3.8/site-packages/psycopg2/__init__.py", line 127, in connect

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

psycopg2.OperationalError: FATAL: password authentication failed for user "postgres"



The above exception was the direct cause of the following exception:


Traceback (most recent call last):

File "/usr/local/lib/python3.8/threading.py", line 932, in _bootstrap_inner

self.run()

File "/usr/local/lib/python3.8/threading.py", line 870, in run

self._target(*self._args, **self._kwargs)

File "/usr/local/lib/python3.8/site-packages/django/utils/autoreload.py", line 53, in wrapper

fn(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/core/management/commands/runserver.py", line 121, in inner_run

self.check_migrations()

File "/usr/local/lib/python3.8/site-packages/django/core/management/base.py", line 459, in check_migrations

executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS])

File "/usr/local/lib/python3.8/site-packages/django/db/migrations/executor.py", line 18, in __init__

self.loader = MigrationLoader(self.connection)

File "/usr/local/lib/python3.8/site-packages/django/db/migrations/loader.py", line 53, in __init__

self.build_graph()

File "/usr/local/lib/python3.8/site-packages/django/db/migrations/loader.py", line 216, in build_graph

self.applied_migrations = recorder.applied_migrations()

File "/usr/local/lib/python3.8/site-packages/django/db/migrations/recorder.py", line 77, in applied_migrations

if self.has_table():

File "/usr/local/lib/python3.8/site-packages/django/db/migrations/recorder.py", line 55, in has_table

with self.connection.cursor() as cursor:

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 259, in cursor

return self._cursor()

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 235, in _cursor

self.ensure_connection()

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection

self.connect()

File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__

raise dj_exc_value.with_traceback(traceback) from exc_value

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection

self.connect()

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 200, in connect

self.connection = self.get_new_connection(conn_params)

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/postgresql/base.py", line 187, in get_new_connection

connection = Database.connect(**conn_params)

File "/usr/local/lib/python3.8/site-packages/psycopg2/__init__.py", line 127, in connect

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

django.db.utils.OperationalError: FATAL: password authentication failed for user "postgres"


Watching for file changes with StatReloader

Performing system checks...


System check identified no issues (0 silenced).

Exception in thread django-main-thread:

Traceback (most recent call last):

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection

self.connect()

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 200, in connect

self.connection = self.get_new_connection(conn_params)

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/postgresql/base.py", line 187, in get_new_connection

connection = Database.connect(**conn_params)

File "/usr/local/lib/python3.8/site-packages/psycopg2/__init__.py", line 127, in connect

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

psycopg2.OperationalError: FATAL: password authentication failed for user "postgres"



The above exception was the direct cause of the following exception:


Traceback (most recent call last):

File "/usr/local/lib/python3.8/threading.py", line 932, in _bootstrap_inner

self.run()

File "/usr/local/lib/python3.8/threading.py", line 870, in run

self._target(*self._args, **self._kwargs)

File "/usr/local/lib/python3.8/site-packages/django/utils/autoreload.py", line 53, in wrapper

fn(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/core/management/commands/runserver.py", line 121, in inner_run

self.check_migrations()

File "/usr/local/lib/python3.8/site-packages/django/core/management/base.py", line 459, in check_migrations

executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS])

File "/usr/local/lib/python3.8/site-packages/django/db/migrations/executor.py", line 18, in __init__

self.loader = MigrationLoader(self.connection)

File "/usr/local/lib/python3.8/site-packages/django/db/migrations/loader.py", line 53, in __init__

self.build_graph()

File "/usr/local/lib/python3.8/site-packages/django/db/migrations/loader.py", line 216, in build_graph

self.applied_migrations = recorder.applied_migrations()

File "/usr/local/lib/python3.8/site-packages/django/db/migrations/recorder.py", line 77, in applied_migrations

if self.has_table():

File "/usr/local/lib/python3.8/site-packages/django/db/migrations/recorder.py", line 55, in has_table

with self.connection.cursor() as cursor:

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 259, in cursor

return self._cursor()

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 235, in _cursor

self.ensure_connection()

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection

self.connect()

File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__

raise dj_exc_value.with_traceback(traceback) from exc_value

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection

self.connect()

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 200, in connect

self.connection = self.get_new_connection(conn_params)

File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner

return func(*args, **kwargs)

File "/usr/local/lib/python3.8/site-packages/django/db/backends/postgresql/base.py", line 187, in get_new_connection

connection = Database.connect(**conn_params)

File "/usr/local/lib/python3.8/site-packages/psycopg2/__init__.py", line 127, in connect

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

django.db.utils.OperationalError: FATAL: password authentication failed for user "postgres"

Dockerfile

FROM python:3.8.2-slim-buster

# Set environment variables
ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

# Set work directory
WORKDIR /code

# Install dependencies
RUN apt-get update --yes --quiet && apt-get install --yes --quiet --no-install-recommends \
    build-essential \
    libpq-dev \
    libmariadbclient-dev \
    libjpeg62-turbo-dev \
    zlib1g-dev \
    libwebp-dev \
    postgresql-server-dev-all gcc python3-dev musl-dev \
 && rm -rf /var/lib/apt/lists/*
COPY Pipfile Pipfile.lock /code/
RUN pip install pipenv && pipenv install --system

# Copy project
COPY . /code/

docker-compose.yml

version: "3.8"

services:
  web:
    build: .
    command: python /code/manage.py runserver 0.0.0.0:8000 #--settings=blog.settings.dev
    volumes:
      - .:/code
    ports:
      - 8000:8000
    env_file:
      - .env/.dev
    depends_on:
      - db

  db:
    image: postgres:13.2-alpine
    restart: always
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password123
      - POSTGRES_DB=postgres


volumes:
  postgres_data:

# settings.py
import os
from pathlib import Path

# Build paths inside the project like this: BASE_DIR / 'subdir'.
BASE_DIR = Path(__file__).resolve().parent.parent


# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/3.1/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = os.environ.get("SECRET_KEY")

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = os.environ.get('DEBUG', default=False)

ALLOWED_HOSTS = os.environ.get("DJANGO_ALLOWED_HOSTS").split(" ")


# Application definition

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
]

MIDDLEWARE = [
    'django.middleware.security.SecurityMiddleware',
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.common.CommonMiddleware',
    'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
    'django.middleware.clickjacking.XFrameOptionsMiddleware',
]

ROOT_URLCONF = 'blog.urls'

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [],
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]

WSGI_APPLICATION = 'blog.wsgi.application'


# Database
# https://docs.djangoproject.com/en/3.1/ref/settings/#databases

DATABASES = {
    "default": {
        "ENGINE": os.environ.get("SQL_ENGINE"),
        "NAME": os.environ.get("SQL_DATABASE"),
        "USER": os.environ.get("SQL_USER"),
        "PASSWORD": os.environ.get("SQL_PASSWORD"),
        "HOST": os.environ.get("SQL_HOST"),
        "PORT": os.environ.get("SQL_PORT"),
    }
}


# Password validation
# https://docs.djangoproject.com/en/3.1/ref/settings/#auth-password-validators

AUTH_PASSWORD_VALIDATORS = [
    {
        'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',
    },
]


# Internationalization
# https://docs.djangoproject.com/en/3.1/topics/i18n/

LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'UTC'

USE_I18N = True

USE_L10N = True

USE_TZ = True


# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/3.1/howto/static-files/

STATIC_URL = '/static/'

postgres container log

PostgreSQL Database directory appears to contain a database; Skipping initialization


2021-04-27 00:26:40.132 UTC [1] LOG: starting PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit

2021-04-27 00:26:40.132 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432

2021-04-27 00:26:40.132 UTC [1] LOG: listening on IPv6 address "::", port 5432

2021-04-27 00:26:40.143 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

2021-04-27 00:26:40.153 UTC [21] LOG: database system was shut down at 2021-04-27 00:26:19 UTC

2021-04-27 00:26:40.158 UTC [1] LOG: database system is ready to accept connections

2021-04-27 00:26:43.454 UTC [28] FATAL: password authentication failed for user "postgres"

2021-04-27 00:26:43.454 UTC [28] DETAIL: Role "postgres" does not exist.

Connection matched pg_hba.conf line 99: "host all all all md5"

.env/.dev

DEBUG=True
SECRET_KEY=7k(&%q+lz33$*0g2i8zc5pn7k4iu7kc+635yz-%f=-=3%kqi$c
DJANGO_ALLOWED_HOSTS=localhost 127.0.0.1 [::1]

SQL_ENGINE=django.db.backends.postgresql
SQL_DATABASE=postgres
SQL_USER=postgres
SQL_PASSWORD=password123
SQL_HOST=db
SQL_PORT=5432

D:\blog>docker version
Client: Docker Engine - Community
 Cloud integration: 1.0.12
 Version:           20.10.5
 API version:       1.41
 Go version:        go1.13.15
 Git commit:        55c4c88
 Built:             Tue Mar  2 20:14:53 2021
 OS/Arch:           windows/amd64
 Context:           default
 Experimental:      true

Server: Docker Engine - Community
 Engine:
  Version:          20.10.5
  API version:      1.41 (minimum version 1.12)
  Go version:       go1.13.15
  Git commit:       363e9a8
  Built:            Tue Mar  2 20:15:47 2021
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.4.4
  GitCommit:        05f951a3781f4f2c1911b05e61c160e9c30eaa8e
 runc:
  Version:          1.0.0-rc93
  GitCommit:        12644e614e25b05da6fd08a38ffa0cfe1903fdec
 docker-init:
  Version:          0.19.0
  GitCommit:        de40ad0

Solution

I am still not sure why this happened but there is no problem when I run this on my new computer with ubuntu 21.04. I cannot access my previous computer with windows installed so I am going to mark this as closed. Thanks to everyone who tried to help:)

Answered By - Niq_Lin