Postgres - Authentication Error

[sathyajackson]

While trying to use the migrate.py getting the following error. "django.db.utils.OperationalError: connection to server at "db" (fd01::2), port 5432 failed: FATAL: password authentication failed for user "postgres""

Have already set all the env file and the same password was passed on the config file.

Sathya

[gregcowell]

Have you removed any inverted commas around the password in both of the .env files ?

[sathyajackson]

yes < >

[sathyajackson]

I just followed this link and created all the files as mentioned

https://www.galojix.com/case-studies/roster/roster-wizard-demonstration-evaluation-environment/

[gregcowell]

Maybe check there are no spaces hiding at the end of the password in .env_demo_db

[sathyajackson]

I verified and tried, still the same error.

Here is my db file

POSTGRES_DB=postgres
POSTGRES_USER=postgres
POSTGRES_PASSWORD=temporary

Here is my web file..

DEBUG=True
TOOLBAR=False
SECRET_KEY=tryingnow
DATABASE_URL=postgresql://postgres:temporary@db/postgres
ALLOWED_HOSTS=0.0.0.0,127.0.0.1
LOGLEVEL=INFO
LOGFORMAT=simple
LOGTOFILE=False
SECURE=False
CELERY_BROKER_URL=pyamqp://guest@rabbitmq//
CELERY_RESULT_BACKEND=redis://redis:6379/0
CSRF_TRUSTED_ORIGINS=http://localhost:8000,http://localhost:3000
CORS_ALLOWED_ORIGINS=http://localhost:8000,http://localhost:3000

executed docker compose exec web python manage.py migrate

here is the error :

roster % docker compose exec web python manage.py migrate
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 275, in ensure_connection
    self.connect()
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 256, in connect
    self.connection = self.get_new_connection(conn_params)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/base.py", line 275, in get_new_connection
    connection = self.Database.connect(**conn_params)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.OperationalError: connection to server at "db" (fd01::2), port 5432 failed: FATAL:  password authentication failed for user "postgres"

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

Traceback (most recent call last):
  File "/roster/manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.11/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/migrate.py", line 100, in handle
    self.check(databases=[database])
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 485, in check
    all_issues = checks.run_checks(
                 ^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/core/checks/registry.py", line 88, in run_checks
    new_errors = check(app_configs=app_configs, databases=databases)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/core/checks/model_checks.py", line 36, in check_all_models
    errors.extend(model.check(**kwargs))
                  ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/base.py", line 1602, in check
    *cls._check_constraints(databases),
     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/base.py", line 2450, in _check_constraints
    connection.features.supports_nulls_distinct_unique_constraints
  File "/usr/local/lib/python3.11/site-packages/django/utils/functional.py", line 47, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
                                         ^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/features.py", line 141, in is_postgresql_15
    return self.connection.pg_version >= 150000
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/functional.py", line 47, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
                                         ^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/base.py", line 436, in pg_version
    with self.temporary_connection():
  File "/usr/local/lib/python3.11/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 691, in temporary_connection
    with self.cursor() as cursor:
         ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 316, in cursor
    return self._cursor()
           ^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 292, in _cursor
    self.ensure_connection()
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 274, in ensure_connection
    with self.wrap_database_errors:
  File "/usr/local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 275, in ensure_connection
    self.connect()
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 256, in connect
    self.connection = self.get_new_connection(conn_params)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/base.py", line 275, in get_new_connection
    connection = self.Database.connect(**conn_params)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.OperationalError: connection to server at "db" (fd01::2), port 5432 failed: FATAL:  password authentication failed for user "postgres"

I am trying the entire setup on my local Mac laptop by runing docker desktop.

Please help ......

[gregcowell]

Not sure if this is causing the problem but DATABASE_URL should be set as follows:

DATABASE_URL=postgresql://postgres:temporary@db:5432/postgres

Also is it possible you changed the password in .env_demo_db after running “docker compose up -d” for the first time?

[sathyajackson]

Tried, still the same issue.

Here is my updated config file ....

DEBUG=True
TOOLBAR=False
SECRET_KEY=tryingnow
DATABASE_URL=postgresql://postgres:temporary@db:5432/postgres
ALLOWED_HOSTS=0.0.0.0,127.0.0.1
LOGLEVEL=INFO
LOGFORMAT=simple
LOGTOFILE=False
SECURE=False
CELERY_BROKER_URL=pyamqp://guest@rabbitmq//
CELERY_RESULT_BACKEND=redis://redis:6379/0
CSRF_TRUSTED_ORIGINS=http://localhost:8000,http://localhost:3000
CORS_ALLOWED_ORIGINS=http://localhost:8000,http://localhost:3000

same error..

django.db.utils.OperationalError: connection to server at "db" (fd01::2), port 5432 failed: FATAL:  password authentication failed for user "postgres"

I actually removed everything and did a fresh installation of the container, still the same issue

[sathyajackson]

Adding to the above, here is the container log file

2023-12-28 17:11:12 2023-12-28 11:41:12.311 UTC [16] LOG:  database system was shut down at 2023-12-28 11:36:59 UTC
2023-12-28 17:11:12 2023-12-28 11:41:12.320 UTC [1] LOG:  database system is ready to accept connections
2023-12-28 17:11:15 2023-12-28 11:41:15.246 UTC [20] FATAL:  password authentication failed for user "postgres"
2023-12-28 17:11:15 2023-12-28 11:41:15.246 UTC [20] DETAIL:  Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2023-12-28 17:11:27 2023-12-28 11:41:27.632 UTC [21] FATAL:  password authentication failed for user "postgres"
2023-12-28 17:11:27 2023-12-28 11:41:27.632 UTC [21] DETAIL:  Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2023-12-28 17:16:12 2023-12-28 11:46:12.355 UTC [14] LOG:  checkpoint starting: time
2023-12-28 17:16:12 2023-12-28 11:46:12.366 UTC [14] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.003 s, sync=0.001 s, total=0.011 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB
2023-12-28 17:35:42 2023-12-28 12:05:42.065 UTC [70] FATAL:  password authentication failed for user "postgres"
2023-12-28 17:35:42 2023-12-28 12:05:42.065 UTC [70] DETAIL:  Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2023-12-28 17:48:04 2023-12-28 12:18:04.656 UTC [80] FATAL:  password authentication failed for user "postgres"
2023-12-28 17:48:04 2023-12-28 12:18:04.656 UTC [80] DETAIL:  Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"

[gregcowell]

Okay, you can set your postgres password on the actual database running in the db container using the following commands:

docker compose exec db bash
psql -U postgres
\passwd postgres
\q
exit

This runs a bash shell in the container and then uses the postgres client to change the password for the "postgres" user.

[sathyajackson]

Thats what i did and now the migrate is working fine however when i try to access the UI using local host, i am getting page cannot be displayed ...

Trying to figure out this now.

Here is my docker PS output:

docker ps 
CONTAINER ID   IMAGE                             COMMAND                  CREATED          STATUS          PORTS                                                 NAMES
c6e98e07eac1   gregcowell/roster-wizard:latest   "celery -A roster_pr…"   45 minutes ago   Up 45 minutes                                                         roster-celery-1
17a16a2d6582   gregcowell/roster-wizard:latest   "python /roster/mana…"   45 minutes ago   Up 45 minutes   0.0.0.0:8000->8000/tcp, :::8000->8000/tcp             roster-web-1
7544209d55e8   redis:latest                      "docker-entrypoint.s…"   45 minutes ago   Up 45 minutes   6379/tcp                                              roster-redis-1
4ddb16efb330   rabbitmq:latest                   "docker-entrypoint.s…"   45 minutes ago   Up 45 minutes   4369/tcp, 5671-5672/tcp, 15691-15692/tcp, 25672/tcp   roster-rabbitmq-1
50c7ad5a7002   postgres:15                       "docker-entrypoint.s…"   45 minutes ago   Up 45 minutes   5432/tcp                                              roster-db-1

I think for some reason the configration files where not picked up the application, any specific reason for that ?

I am using docker-desktop on my mac to test this.

Note: I have created the superuser admin using the migrate.py script for the django console, however the console is not accessiable, locally.

[sathyajackson]

Ok i restarted the container and now i can see the actual issue seem like the localhost:8000 is not in the ALLOWED_HOSTS on settings.py. Any guess as to why this should have happened, since its been mentioned on the env_demo_web file.

[sathyajackson]

I think this is not picking up ALLOWED_HOSTS = tuple(env.list("ALLOWED_HOSTS", default=[]))

[gregcowell]

The instructions specify this URL: http://127.0.0.1:8000/

[sathyajackson]

yes correct, but i am just think why if i add localhost on the ALLOWED_HOST file, why its not pick up that variable,

http://127.0.0.1:8000/ works

[sathyajackson]

I believe I can find a solution, but your assistance means a lot to me. I'm excited to test this application and see if it meets my requirements. Thank you so much for your timely help—you're amazing!

[gregcowell]

Thanks!

Try pinging "localhost" on your laptop and see if it resolves to 127.0.0.1.

Make sure you follow the user guide: https://www.galojix.com/case-studies/roster/roster-wizard-user-guide/

Hope your testing goes well.

[sathyajackson]

Thanks :)

[gregcowell]

Resolved