Django Postgres docker-compose connection error mainly failed: FATAL: password authentication failed for user foobar ERROR: 2

I'm trying to make a django microservice with postgres database. and I have a problem which I cant solve it for few days. this question contains serveral errors. so you may ignore them and answer last error which is about access psql shell (port 5432 failed: FATAL: password authentication failed for user "postgres" Error section) or ignore all and answer the main question of how to have a docker-compose with django and postgres containers.

the docker-compose.yml looks like:

version: "3.9"
services:
  # Redis
  redis:
    image: redis:7.0.4-alpine
    container_name: redis
  # rabbit
  rabbit:
    hostname: rabbit
    image: "rabbitmq:3.10.7-alpine"
    environment:
      - RABBITMQ_DEFAULT_USER=admin
      - RABBITMQ_DEFAULT_PASS=mypass
    ports:
      - "15672:15672"
      - "5672:5672"
  mongodb_container:
    image: mongo:5.0.10
    ports:
      - "27017:27017"
    depends_on:
      - redis
  # Main Database Postgres
  main_postgres_ser:
    image: postgres:14.4-alpine
    volumes:
      - ./data/db:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB= postgres # NAME
      - POSTGRES_USER= postgres # USER
      - POSTGRES_PASSWORD= postgrespass # PASSWORD
    container_name: postgres_container
    restart: always
    ports:
      # - 8000:8000 # HTTP port
      - 5432:5432 # DB port
    networks:
      - djangonetwork
    depends_on:
      - rabbit
  # Main Django Application
  main_django_ser:
    build:
      context: . #/main_ms
      dockerfile: Dockerfile_main_ms
    container_name: main_django
    command: "python manage.py runserver 0.0.0.0:8000"

    environment:
      PYTHONUNBUFFERED: 1
    ports:
      - 8000:8000
    volumes:
      - .:/main_ms
    networks:
      - djangonetwork
    depends_on:
      - main_postgres_ser
      - rabbit
    links:
      - main_postgres_ser:main_postgres_ser
networks:
  djangonetwork:
    driver: bridge
volumes:
  main_postgres_ser:
    driver: local

the Dockerfile for django service looks like:

FROM python:3.10.6-buster
ENV PYTHONUNBUFFERED=1

RUN apt-get update -y
RUN apt-get update && \
      apt-get -y install sudo
WORKDIR /main_ms
COPY requirements.txt ./main_ms/requirements.txt
RUN pip3 install -r ./main_ms/requirements.txt

and in settings.py in DATABASES I have

DATABASES = {
    'default': {
        # 'default':'psql://postgres:postgrespass@postgres:5432/postgres',
        # 'default':'postgres://postgres:postgrespass@postgres:5432/postgres',
        # 'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'ENGINE': 'django.db.backends.postgresql',
        'NAME' : 'postgres',
        'USER' : 'postgres',
        'PASSWORD' : 'postgrespass',
        # HOST should be as postgres service name
        'HOST' : 'main_postgres_ser',
        'PORT' : '5432',
         }}

as I know the HOST should be as postgres service name(here main_postgres_ser) because DNS name will be the same.

password authentication failed for user Error

so I built docker-compose then initialized the django project, ran docker-compose up and inside the django container made migrations but when I do python manage.py migrate I get django.db.utils.OperationalError: FATAL: password authentication failed for user "postgres"

so with just keeping the default in DATABASES in settings.py ('default':'postgres://postgres:postgrespass@main_postgres_ser:5432/postgres',) with pattern of 'default':'postgres://postgres:pass@host:port/dbname',.

thus I got everything commented in DATABASES (except default and ENGINE) and got rid of last error. note that I had to keep 'ENGINE': 'django.db.backends.postgresql', (not to get another error) in DATABASES.

Please supply the NAME or OPTIONS['service'] Error

but then again I received django.core.exceptions.ImproperlyConfigured: settings.DATABASES is improperly configured. Please supply the NAME or OPTIONS['service'] value. note I also can access http://localhost:8000/ on browser and there I had django's yellow debug page.

connections on Unix domain socket PGSQL.5432 Error

but when I uncomment 'NAME' : 'postgres', in DATABASES I got connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? error I have not access to http://localhost:8000/ in the browser again.

no password supplied Error

so I uncommented HOST in DATABASES and got django.db.utils.OperationalError: fe_sendauth: no password supplied

again password authentication failed for user Error

so I uncommented PASSWORD and got django.db.utils.OperationalError: FATAL: password authentication failed for user "root" which is same as django.db.utils.OperationalError: FATAL: password authentication failed for user "postgres" (in the case of uncommenting 'USER'='postgres') so I'm again at the first step!!!

inside the django container I tried to CREATE USER with CREATE USER postgres WITH PASSWORD 'postgrespass'; or postgres=# CREATE USER postgres WITH PASSWORD 'postgrespass'; but I got CREATE: not foundI again tried it in postgres container and got same result.

I also tried last solution by adding local all postgres peer to pg_hba.conf still didnt work.

unrecognized service Error

sudo -u root postgresql psql resulted sudo: postgresql: command not found or sudo service postgresql start resulted postgresql: unrecognized service

psql: error: connection PGSQL.5432 Error

then I tried to access to psql shell with docker exec -it -u postgres postgres_container psql which I received the psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "postgres" does not exist

port 5432 failed: FATAL: password authentication failed for user "postgres" Error

in order to try CREATE USER user WITH PASSWORD 'pass'; I want to access psql shell with docker-compose run --rm main_postgres_ser psql -h main_postgres_ser -U postgres -d postgres things seems to fine and working because it pops up enter password input but when I enter the postgrespass which is my password for postgres user I get port 5432 failed: FATAL: password authentication failed for user "postgres" ERROR: 2 Error in the postgres container and in the terminal that docker-compose is up I get DETAIL: Role "postgres" does not exist..

I also specified user: postgres in postgres docker-compose like this suggestion and didnt change any thing.

questions

so why I get error even I put my password for postgres user?

or how can I make a docker-compose for django and postgres??