2

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
Improve this question
4
  • might be worth including your settings.py too. Especially the DATABASES dict. Commented Apr 26, 2021 at 12:12
  • Yes. Sorry I forgot to include. Commented Apr 27, 2021 at 0:30
  • I think most likely your .env file is not being loaded into the environ object. I can't see anywhere where you do that. You can do a little test by changing the DATABASE values from os.environ.get to their actual values from the .env file and see if things run smoothly. I recommend using a package like python-dotenv to load environment variables from an environment file. Commented Apr 27, 2021 at 11:25
  • @Danoram I tried changing the DATABASES to use the values directly and got the same error. Previously I loaded the .env file in docker-compose and it seemed to work when I checked with import os and os.environ.get in python shell Commented Apr 28, 2021 at 0:57

3 Answers 3

Reset to default
15

Sometimes when setting postgres up in a docker container, you can set the password incorrectly and these incorrect settings will persist. When I get this error, the first thing I always try is to destroy the volume and recreate it.

Use the -v to do this, like so:

docker-compose down -v

Then bring it up again.

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

Thanks for your suggestion. Before asking, I tried removing the containers and rebuilding them and it did not work.
Any chance using 'postgres' as the user and database name are causing a conflict with the pre-existing defaults? Try setting SQL_HOST and POSTGRES_USER to something else, and also POSTGRES_DB and SQL_DATABASE to something else.
Tried following and no luck:( ``` SQL_ENGINE=django.db.backends.postgresql SQL_DATABASE=postgresdb SQL_USER=useruser SQL_PASSWORD=password123 SQL_HOST=pgdb SQL_PORT=5432 ``` ``` pgdb: image: postgres:13.2-alpine restart: always volumes: - postgres_data:/var/lib/postgresql/data/ ports: - 5432:5432 environment: - POSTGRES_USER=useruser - POSTGRES_PASSWORD=password123 - POSTGRES_DB=postgresdb ``` jango.db.utils.OperationalError: FATAL: password authentication failed for user "useruser"
Don't change SQL_HOST to pgdb, it has to be db, unless you also changed this in your docker-compose. Other than that I don't know, but I'm happy to look at the code if you want to post a github link.
I did change that in docker-compose to match. Really appreciate your help! Here's the link github.com/Niqnil/blog
|
1

This directly relates with docker restart policy. As per your configuration, restart: always, always restart the container if it stops. This may create a distortion on the correct order of your containers. I suggest to remove this particular line(It worked for my case).

Improve this answer

1 Comment

That makes sense but I can't test it because I no longer have access to my previous computer with windows os. There's no problem running this repo on my new computer with ubuntu 21.04, even with restart: always. Nevertheless, thanks for trying to help:)
-1

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:)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.