"error: pq: role "root" does not exist" when running pq with Postgres for Docker

I am setting up a local Postgres database on Docker with the postgres:14-alpine image, and running database migrations on it with golang-migrate, when I got the following error message after running the migrate tool:

error: pq: role "root" does not exist

I was running the following commands:

$ docker run --name postgres14 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=pass -d postgres:14-alpine

$ docker exec -it postgres14 createdb --user=root --owner=root demodb

$ migrate -path db/migrations -database postgresql://root:pass@localhost:5432/demodb?sslmode=disable --verbose up

These commands can also be viewed in this Makefile, and the full codebase can be found in this repository.

Here are the logs from the Postgres container:

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

waiting for server to start....2022-10-15 09:56:41.209 UTC [36] LOG:  starting PostgreSQL 14.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit
2022-10-15 09:56:41.211 UTC [36] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-10-15 09:56:41.217 UTC [37] LOG:  database system was shut down at 2022-10-15 09:56:41 UTC
2022-10-15 09:56:41.220 UTC [36] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE


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

waiting for server to shut down...2022-10-15 09:56:41.422 UTC [36] LOG:  received fast shutdown request
.2022-10-15 09:56:41.423 UTC [36] LOG:  aborting any active transactions
2022-10-15 09:56:41.423 UTC [36] LOG:  background worker "logical replication launcher" (PID 43) exited with exit code 1
2022-10-15 09:56:41.424 UTC [38] LOG:  shutting down
2022-10-15 09:56:41.434 UTC [36] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

What should I do to configure the root role correctly?

The docker image docs specify that POSTGRES_USER environment variable defaults to postgres if not set, try using that instead of root or drop the container and build it again using the correct environment variable

once you are inside the psql shell you can create a user with

CREATE USER username WITH PASSWORD 'your_password';

then to grant the user access on a specific database:

GRANT ALL PRIVILEGES ON DATABASE demodb TO username;

once that is done you can use the user in the connection string in make file

Turns out the Postgres server that was installed and setup on my OS by Hombrew was using the same port, which clashed with the requests made to the containerized database under the same port number.

This issue can be solved by either using a different port number for the containerized database, or by shutting down the database on the OS.