Docker role creation error - role ___ does not exist

Question

I'm new to Docker and am trying to run it with Postgres. I then try to run python test cases which work for others but not me.

The following error tells me that Docker may be struggling to create the role foo:

E sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: role "foo" does not exist

Another error:

E psycopg2.OperationalError: FATAL: role "foo" does not exist

Postgresql version: mydb=# SELECT version();:

 PostgreSQL 9.6.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit
(1 row)

Docker Version

Version 17.06.2-ce-mac27 (19124)

Running Docker with this script:

docker run -p 5432:5432 --env POSTGRES_PASSWORD="bar" --env POSTGRES_USER="foo" --env POSTGRES_DB="mydb" postgres

Output from script: As you can see, it says CREATE ROLE

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/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
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_ctl -D /var/lib/postgresql/data -l logfile start


WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
waiting for server to start....LOG:  could not bind IPv6 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
LOG:  database system was shut down at 2017-09-26 21:28:17 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
 done
server started
CREATE DATABASE

CREATE ROLE


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

LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
waiting for server to shut down....LOG:  shutting down
LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

LOG:  database system was shut down at 2017-09-26 21:28:19 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

EDIT

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                     PORTS                    NAMES
bbab7eb98fcf        postgres            "docker-entrypoint..."   10 seconds ago      Up 11 seconds              0.0.0.0:5432->5432/tcp   hardcore_wilson
a6a1e2e313b8        postgres            "docker-entrypoint..."   16 minutes ago      Exited (0) 6 minutes ago                            nervous_banach

teppic teppic · Accepted Answer · 2017-09-27T01:13:55

Looks like psycopg2 trying a local socket connection. You'd see the following exception if it was connecting over TCP:

password authentication failed for user "foo"

And an error in the container log:

FATAL:  password authentication failed for user "foo"
DETAIL:  Role "foo" does not exist.
    Connection matched pg_hba.conf line 95: "host all all all md5"

Make sure the host connection parameter is set. If it isn't set psycopg2 will fall back to using a UNIX socket.

Edit:

It appears that psycopg2 will prefer the UNIX socket for connections when passed localhost connection parameters. If there are two instances of postgres running, one listening to the UNIX socket and one listening on TCP port 5432, psycopg2 will connect through the UNIX domain socket when passed a url like postgresql://localhost:5432.

Docker role creation error - role ___ does not exist

1 Answers