django - Postgres - can't acces database, no acces

Question

I am trying to access my postgres database but I get a permission denied error. I created database + table (was created in python but it is there):

sudo -u postgres psql
postgres=# create user lukasz1 with encrypted password '<password>';
postgres=# create database mydatabase owner lukasz1;

So far so good:

                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 mydatabase | lukasz1  | UTF8     | pl_PL.UTF-8 | pl_PL.UTF-8 | =Tc/lukasz1          +
            |          |          |             |             | lukasz1=CTc/lukasz1
 postgres   | postgres | UTF8     | pl_PL.UTF-8 | pl_PL.UTF-8 | 
 template0  | postgres | UTF8     | pl_PL.UTF-8 | pl_PL.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | pl_PL.UTF-8 | pl_PL.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
(4 rows)

and lukasz1 is an owner of mydatabase.

But when I try to connect it I am get "permission denied".

I can't even select from my table to view it as permission is denied:

mydatabase=> select article_id from articles;

ERROR: permission denied for table articles

I checked multiple answers to similar questions and tried checking user valid_until and got an empty values in that column:

mydatabase=> select usename, valuntil from pg_user;
 usename  | valuntil 
----------+----------
 postgres | 
 lukasz   | 
 lukasz1  | 
(3 rows)

In other question I found that pgAdmin4 can bug this and I should change valid until to infinity,which I tried but without any result as permission was denied to make this change.

mydatabase=> alter user lukasz1 valid until 'infinity';
ERROR:  permission denied

I checked privileges of the users and got this:

postgres=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          | 
 lukasz   |    16384 | f           | f        | f       | f            | ******** |          | 
 lukasz1  |    17256 | f           | f        | f       | f            | ******** |          | 
 lukasz2  |    17437 | f           | f        | f       | f            | ******** |          | 
(4 rows)

I turned out that even though lukasz1 is owner of the mydatabase that account can't do anything with databse.

Next thing I checked was default access privileges which was completly empty:

mydatabase=> ddp
         Default access privileges
 Owner | Schema | Type | Access privileges 
-------+--------+------+-------------------
(0 rows)

I tried changing password but it changed nothing.

ALTER USER lukasz1 WITH PASSWORD '<new password>';

I tried to create public schema but wasn't allowed as permission was denied:

mydatabase=> create schema public;
ERROR:  permission denied for database mydatabase

All of that resulted, that docker container with web cannot access container with db as

password authentication failed for user "lukasz1"

I cannot even view hba_file:

mydatabase=> show hba_file;
ERROR:  must be superuser or a member of pg_read_all_settings to examine "hba_file"

Can anyone please advice how can I make this work?

EDIT: I checked the logins and were able to create lukasz as superuser.

 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls 
 lukasz   |    16384 | f           | t        | f       | f           

When using his credentials I am able to create tables, insert data into it etc.

psql -d mydatabase -U lukasz -W -c "insert into articles values (1, '?ukasz', 'lukasz', 'title','tekst')"

But after creating containers I still get

django.db.utils.OperationalError: FATAL:  password authentication failed for user "lukasz"

docker-compose.yml

version: "3.9"

services:
  db:
    image: postgres:9.6
    environment:
      - POSTGRES_DB=mydatabase
      - POSTGRES_USER=lukasz
      - POSTGRES_PASSWORD=password
    networks:
      - backend

  web:
    build: .
    command: python3 manage.py runserver 127.0.0.1:8080
    volumes:
      - .:/code
    ports:
      - "8080:8080"
    depends_on:
      - db
    networks:
      - backend


networks:
  backend:
    driver: bridge
    name: backend

setting.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydatabase',
        'USER': 'lukasz',
        'PASSWORD': 'password',
        'HOST': 'db',
        'PORT': 5432,
    }
}

sqlalchemy engine:

engine = create_engine("postgresql://lukasz:password@db:5432/mydatabase")

question from:https://stackoverflow.com/questions/65836432/postgres-cant-acces-database-no-acces

Answer 1

Waitting for answers