Postgres and Docker: Database error - role "username" does not exist

Question

I'm trying to implement configure the postgres database of this project on MacOS, so I installed postgres, docker and I tried to run docker-compose up -d on this docker file:

version: "3"

services:
  maadb-ps-db:
    image: postgres:latest
    container_name: maadb-ps-db
    ports:
      - "5433:5432"
    env_file:
      - postgres.env
    volumes:
      - database-data:/usr/local/var/postgres

volumes:
  database-data:

Here I have the environment file with only this line:

POSTGRES_HOST_AUTH_METHOD=trust

Then, I try to connect to the database through python and the following method:

def get_resources_sql():
conn = None
try:
    # read connection parameters
    params = config()

    # connect to the PostgreSQL server
    print('Connecting to the PostgreSQL database...')
    conn = psycopg2.connect(**params)

    # create a cursor
    cur = conn.cursor()

    # TODO

    # close the communication with the PostgreSQL
    cur.close()
except (Exception, psycopg2.DatabaseError) as error:
    print("Database ERROR: ", error)
finally:
    if conn is not None:
        conn.close()
        print('Database connection closed.')

Where the params are the following:

[postgresql]
host=localhost
port=5433
database=maadbsql

When I try to run the code I get the following error:

args {'host': 'localhost', 'port': '5433', 'database': 'maadbsql'}
Connecting to the PostgreSQL database...
Database ERROR:  FATAL:  role "username" does not exist

If I try to open the maadbsql database through psql maadbsql and check the permissions, this is what I get (I have only two users, "username" and "postgres"):

Am I missing something?

EDIT:

this is the log after running docker-compose up: link

this is the log after shutting down: link

Answer 1

I think you're not creating your database appropriately. I would start by deleting your containers and volumes:

docker-compose down -v

The configure your environment file so that the postgres image will create a database and user for your application:

POSTGRES_HOST_AUTH_METHOD=trust
POSTGRES_USER=maadbsql
POSTGRES_DB=maadbsql

With this change, I can run the following Python code successfully:

>>> import psycopg2
>>> conn = psycopg2.connect('dbname=maadbsql user=maadbsql host=localhost port=5433')
>>> cur = conn.cursor()
>>> cur.execute('create table example(id int, name text)')
>>> conn.commit()
>>> cur.execute('insert into example (id, name) values (%s, %s)', (1, 'alice'))
>>> conn.commit()
>>> cur.execute('select * from example')
>>> cur.fetchall()
[(1, 'alice')]