Unable to connect to postgres inside a docker container

Question

I've started a new postgres:12 docker instance as:

$ docker run -it -d --name newdb \
    -p 5444:5432 \
    -e POSTGRES_PASSWORD=postgres \
    -e POSTGRES_USER=postgres \
    --volumes-from dbstore \
    -v /mnt/data/dbbackup:/backup postgres:12 bash \
    -c "cd /dbtestdata && tar xvf /backup/20210206_backup.tar --strip 1 && /bin/bash"

Then I attached to it:

$ docker exec -it newdb /bin/bash

Created a cluster:

# pg_createcluster 12 main

Which led to:

Creating new PostgreSQL cluster 12/main ...
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/12/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 12 main start

Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

Started the service:

# service postgresql restart
[ ok ] Restarting PostgreSQL 12 database server: main.

Tried both ways to connect, but none was successful:

# psql -U postgres
psql: error: FATAL:  Peer authentication failed for user "postgres"

# psql -U postgres -W
Password: ******* <<-- postgres
psql: error: FATAL:  Peer authentication failed for user "postgres"

What could I have done wrong?

Answer 1

postgres peer authentication depends on the linux/unix user that runs psql being the same as user in the -U postgres parameter. As you are running on root locally this isn't true.

Having a postgres user outside the container won't necessary map to the postgres user inside the container.

Recommend changing authentication mechanisms

Answer 2

I have a similar setup where I wanted to run psql on a postgres docker. To do it I ran the following command:

docker exec -t --user <db_user> <container_name> psql -U <db_user>
e.g.
docker exec -t --user postgres postgres_db_1 psql -U postgres

This gave me interactive psql in my terminal.

I found that to fix the psql: error: FATAL: Peer authentication failed for user "postgres" error I had to specify the postgres user for my docker container