SQLAlchemy not recognising role “postgres” in my docker Image

Question

I'm trying to connect to my docker postgresql image but SQLAlchemy in python is refusing the connection.

I've created a docker instance using the following command:

docker run  -d --name postgres-m2m -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=pass postgres

And I can successfully connect to my instance using Pycharm's Database right side panel. check the screen capture

So I'd tried to connect it with my python application.

First I did create the settings/config.py file :

class dbCredentials:
    DB_USER = 'postgres'
    DB_PASS  = 'pass'
    DB_NAME = 'postgres'
    DB_HOST = '0.0.0.0'
    DB_PORT = '5432'
    DB_SCHEMA = 'telecom'
    def __int__(self):
        pass

And then my model/base.py

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from settings.config import dbCredentials

credentials = dbCredentials()

DB_USER = credentials.DB_USER
DB_PASS = credentials.DB_PASS 


DB_HOST = credentials.DB_HOST
DB_PORT = credentials.DB_PORT
DB_NAME = credentials.DB_NAME
DB_SCHEMA = credentials.DB_SCHEMA

engine = create_engine(f'postgresql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}')
Session = sessionmaker(bind=engine)

Base = declarative_base(metadata=MetaData(schema=DB_SCHEMA))

After that I did create the table model/tables/status.py

from sqlalchemy import Column, SmallInteger, String
from src.models.base import Base

class Status(Base):
    __tablename__ = 'status'
    id = Column('id', SmallInteger, primary_key=True, autoincrement=True, nullable=False)
    STATUS = Column('STATUS', String, nullable=False)

    def __init__(self, STATUS):
        self.STATUS = STATUS

And finally my main.py file

from src.models.base import Base, Session, engine
from src.models.tables.status import Status
Base.metadata.create_all(engine, checkfirst=True)
session = Session()

At this point I'm getting the following error:

[...] conn = _connect(dsn, connection_factory=connection_factory, **kwasync) sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: role "postgres" does not exist (Background on this error at: http://sqlalche.me/e/e3q8)

Can anyone help me to solve this problem?

Thanks!

Possible duplicate of Connecting to Postgresql in a docker container from outside — J.J. Hakala
psql should be used first to test that connection to the database works. sqlalchemy tag might be irrelevant here, and 0.0.0.0 is never approriate destination IP address when connecting a client program to some server instance. — J.J. Hakala

Manish Dash Manish Dash · Accepted Answer · 2019-02-26T09:36:28

In the shared screenshot the database name is postgres but in the config.py file you have set it to stone. Is this intentional?
You should not set the host as '0.0.0.0' as it listens on every available interface on your system. Try running the database on a specific interface like localhost or your <IP address> and update the config file accordingly.

SQLAlchemy not recognising role “postgres” in my docker Image

1 Answers