Unable to connect to a local PostgreSQL database running on docker with TypeORM + pgAdmin4

Viewed 2102

I have a postgres database hosted in docker running on localhost with the default postgres port 5432 but I am unable to connect to the database at all via either pgAdmin4 or TypeORM. Both throw the same error saying invalid password.

I am using the following docker-compose.yml file to create the database:

version: '3.8'
services: 
    db:
        image: postgres
        volumes:
            - ./pgdata:/var/lib/postgresql/data
        ports: 
            - '5432:5432'
        environment: 
            POSTGRES_DB: maindb
            POSTGRES_USER: admin
            POSTGRES_PASSWORD: admin

I would therefore expect to be able to connect to the database via pgAdmin4 by entering the default port 5432 on localhost ( also tried localhost ip 127.0.0.1 ) however when I try to login with the following credentials ( you have to trust me that the password is correct typed and retyped 10 times and copy pasted ) postgres database login page When I then press login I get the following error:

Unable to connect to server:

FATAL: password authentication failed for user "admin"

This is really strange as I see no reason as to why it would fail. I am 100% sure the environment variables got passed through as on docker desktop I can see that they are the same.

I also tried to connect to the database via TypeORM in the following manner:

createConnection({
    type: 'postgres',
    host: 'localhost',
    port: 5432,
    username: 'admin',
    password: 'admin',
    database: 'maindb',
    entities: [App],
    synchronize: true,
    logging: false,
})
    .then(connection => console.log('Logged in'))
    .catch(error => console.log(error));

and I get this error message:

error: password authentication failed for user "admin"
REMOVED PATHS FOR CONCISENESS
 {
  length: 97,
  severity: 'FATAL',
  code: '28P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'auth.c',
  line: '285',
  routine: 'auth_failed'
}

I also see no reason why this should fail. I wonder if it is a PostgreSQL problem or a docker problem? Potentially it is easier for me to switch DB provider?

Edit 1

Here are the logs from the start up:

Attaching to backend_db_1

db_1 | The files belonging to this database system will be owned by user "postgres".

db_1 | This user must also own the server process.

db_1 |

db_1 | The database cluster will be initialized with locale "en_US.utf8".

db_1 | The default database encoding has accordingly been set to "UTF8".

db_1 | The default text search configuration will be set to "english".

db_1 |

db_1 | Data page checksums are disabled.

db_1 |

db_1 | fixing permissions on existing directory /var/lib/postgresql/data ... ok

db_1 | creating subdirectories ... ok

db_1 | selecting dynamic shared memory implementation ... posix

db_1 | selecting default max_connections ... 100

db_1 | selecting default shared_buffers ... 128MB

db_1 | selecting default time zone ... Etc/UTC

db_1 | creating configuration files ... ok

db_1 | running bootstrap script ... ok

db_1 | performing post-bootstrap initialization ... ok

db_1 | initdb: warning: enabling "trust" authentication for local connections

db_1 | You can change this by editing pg_hba.conf or using the option -A, or

db_1 | --auth-local and --auth-host, the next time you run initdb.

db_1 | syncing data to disk ... ok

db_1 |

db_1 |

db_1 | Success. You can now start the database server using:

db_1 |

db_1 | pg_ctl -D /var/lib/postgresql/data -l logfile start

db_1 |

db_1 | waiting for server to start....2020-08-12 09:26:47.682 UTC [48] LOG: starting PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

db_1 | 2020-08-12 09:26:47.685 UTC [48] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

db_1 | 2020-08-12 09:26:47.744 UTC [49] LOG: database system was shut down at 2020-08-12 09:26:44 UTC

db_1 | 2020-08-12 09:26:47.770 UTC [48] LOG: database system is ready to accept connections

db_1 | done

db_1 | server started

db_1 | CREATE DATABASE

db_1 |

db_1 |

db_1 | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

db_1 |

db_1 | 2020-08-12 09:26:50.836 UTC [48] LOG: received fast shutdown request

db_1 | waiting for server to shut down....2020-08-12 09:26:50.847 UTC [48] LOG: aborting any active transactions

db_1 | 2020-08-12 09:26:50.849 UTC [48] LOG: background worker "logical replication launcher" (PID 55) exited with exit code 1

db_1 | 2020-08-12 09:26:50.850 UTC [50] LOG: shutting down

db_1 | 2020-08-12 09:26:50.929 UTC [48] LOG: database system is shut down

db_1 | done

db_1 | server stopped

db_1 |

db_1 | PostgreSQL init process complete; ready for start up.

db_1 |

db_1 | 2020-08-12 09:26:50.971 UTC [1] LOG: starting PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

db_1 | 2020-08-12 09:26:50.984 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432

db_1 | 2020-08-12 09:26:50.984 UTC [1] LOG: listening on IPv6 address "::", port 5432

db_1 | 2020-08-12 09:26:50.997 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

db_1 | 2020-08-12 09:26:51.051 UTC [66] LOG: database system was shut down at 2020-08-12 09:26:50 UTC

db_1 | 2020-08-12 09:26:51.080 UTC [1] LOG: database system is ready to accept connections
1 Answers

The user and password are being set in the initialization script.

If this is not the first time you are running the compose-up on that file, it is very likely that you already have data on the volume that is being mounted in the container. In this case, the initialization script is skipped and you should see this log on the docker-compose up output:

db_1  | PostgreSQL Database directory appears to contain a database; Skipping initialization

Any of these options should solve your problem:

  • remember what password you set the first time when you composed-up and use that one to connect

OR

  • empty that volume or delete it completely (@pacuna already explained how to to this in a comment - this will wipe out the data and reinitialize everything from scratch!)
docker-compose down -v

OR

  • reset the admin password:
// Open a console in the container
docker exec -ti <your-psql-container-id> sh

// Connect to your maindb using the admin user
psql -d maindb -U admin

// Change the admin user password
ALTER USER admin WITH ENCRYPTED PASSWORD 'admin-new-pass';

...then try again to connect using admin-new-pass


Edit after troubleshooting:
The problem was caused by a miss-configuration of the pgAdmin4 client. After containerizing the pgAdmin4 client, in the attempt to reproduce, the problem was gone

Related