python - How to solve AWS RDS issue: psycopg2.OperationalError: FATAL: password authentication failed for user "root"

Question

Im using terraform and have built the infrastructure below:

• VPC with Public subnets
• ECS Fargate and ECR
• Public RDS instance in the public subnets

I am using django as the backend framework.

Everything seem fine (docker compose logs report is fine and I can access AWS RDS via psql and RDS endpoint in the terminal) until the task of ECS start then stop immediately with the logs message:

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

here the logs from docker-compose logs

db          | The files belonging to this database system will be owned by user "postgres".
db          | This user must also own the server process.
db          | 
db          | The database cluster will be initialized with locale "en_US.utf8".
db          | The default database encoding has accordingly been set to "UTF8".
db          | The default text search configuration will be set to "english".
db          | 
db          | Data page checksums are disabled.
db          | 
db          | fixing permissions on existing directory /var/lib/postgresql/data ... ok
db          | creating subdirectories ... ok
db          | selecting dynamic shared memory implementation ... posix
db          | selecting default max_connections ... 100
db          | selecting default shared_buffers ... 128MB
db          | selecting default time zone ... Etc/UTC
db          | creating configuration files ... ok
db          | running bootstrap script ... ok
db          | performing post-bootstrap initialization ... ok
db          | syncing data to disk ... ok
db          | 
db          | 
db          | Success. You can now start the database server using:
db          | 
db          |     pg_ctl -D /var/lib/postgresql/data -l logfile start
db          | 
db          | initdb: warning: enabling "trust" authentication for local connections
db          | You can change this by editing pg_hba.conf or using the option -A, or
db          | --auth-local and --auth-host, the next time you run initdb.
db          | waiting for server to start....2021-08-08 16:34:32.374 UTC [48] LOG:  starting PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
db          | 2021-08-08 16:34:32.375 UTC [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db          | 2021-08-08 16:34:32.382 UTC [49] LOG:  database system was shut down at 2021-08-08 16:34:31 UTC
db          | 2021-08-08 16:34:32.388 UTC [48] LOG:  database system is ready to accept connections
db          |  done
db          | server started
db          | CREATE DATABASE
db          | 
db          | 
db          | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
db          | 
db          | 2021-08-08 16:34:32.842 UTC [48] LOG:  received fast shutdown request
db          | waiting for server to shut down....2021-08-08 16:34:32.844 UTC [48] LOG:  aborting any active transactions
db          | 2021-08-08 16:34:32.850 UTC [48] LOG:  background worker "logical replication launcher" (PID 55) exited with exit code 1
db          | 2021-08-08 16:34:32.851 UTC [50] LOG:  shutting down
db          | 2021-08-08 16:34:32.886 UTC [48] LOG:  database system is shut down
db          |  done
db          | server stopped
db          | 
db          | PostgreSQL init process complete; ready for start up.
db          | 
db          | 2021-08-08 16:34:33.008 UTC [1] LOG:  starting PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
db          | 2021-08-08 16:34:33.008 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
db          | 2021-08-08 16:34:33.008 UTC [1] LOG:  listening on IPv6 address "::", port 5432
db          | 2021-08-08 16:34:33.012 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db          | 2021-08-08 16:34:33.017 UTC [76] LOG:  database system was shut down at 2021-08-08 16:34:32 UTC
db          | 2021-08-08 16:34:33.024 UTC [1] LOG:  database system is ready to accept connections

logs from RDS

2021-08-06 16:56:12 UTC:10.0.2.174(39934):root@testdb:[5710]:DETAIL:  Role "root" does not exist.
    Connection matched pg_hba.conf line 13: "host   all             all         all         md5"

Dockerfile

FROM python:3.8

ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

WORKDIR /backend

COPY  requirements.txt /backend/

RUN pip install -r requirements.txt && 
    pip install --upgrade pip


COPY . /backend/


COPY ./entrypoint.sh /

ENTRYPOINT ["sh", "/entrypoint.sh"]

entrypoint.sh

#!/bin/sh

set -e

python manage.py migrate --no-input

python manage.py collectstatic --no-input

gunicorn backend.wsgi:application --bind 0.0.0.0:8000

I am not sure why this is happening.

Can someone please help me understand because locally PostgreSQL do not have any issue.

Answer 1

You can't use the root database account with RDS databases. That is a reserved account that only Amazon has access to. You need to examine your RDS database configuration and figure out what your admin account name is.