0

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?

ofekatr
  • 1
  • 2

2 Answers2

0

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

M-Raw
  • 779
  • 4
  • 10
  • 1
    As you can see in the first command, I am using the `POSTGRES_USER` environment variable: `$ docker run --name postgres14 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=pass -d postgres:14-alpine`. I also inspected the container upon creation and can confirm that the `POSTGRES_USER` variable was set correctly with `root`. I also tried using the `postgres` user instead: `$ docker run --name postgres14 -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=pass -d postgres:14-alpine` – ofekatr Oct 15 '22 at 11:15
  • Following the image creation command above, I ran: `$ docker exec -it postgres14 createdb --user=postgres --owner=postgres demodb`, and then the migrate command: `$ migrate -path db/migrations -database postgresql://postgres:pass@localhost:5432/demodb?sslmode=disable --verbose up`, which resulted in this error: `error: pq: database "demodb" does not exist` – ofekatr Oct 15 '22 at 11:16
  • I also tried running the first two commands for `root`, and then run the `GRANT ALL PRIVILEGES ON DATABASE demodb TO root;` command before running the `migrate` command, but the error about the non-existing role `root` is still the same. – ofekatr Oct 15 '22 at 11:30
  • before you migrate,can you connect to the postgres container and use the shell? for the second comment you were only missing the database you only needed to create it, if the user does not exist you can check who are the current users available by issuing the command `\du` and see who have what privileges – M-Raw Oct 15 '22 at 13:14
0

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.

ofekatr
  • 1
  • 2