Adonis and PostgreSQL: create table "adonis_schema"... fails with 'permission denied for schema public'

Question

I have been following the dockerizing-adonis tutorial which uses PostgreSQL as database.

I am new to Postgres and do not completely understand its user/role/schema concepts yet.

In the tutorial pretty early on I'm instructed to create dockerConfig/postgres-dev-init.sql with this content:

CREATE USER adonis with encrypted password 'adonis';
CREATE DATABASE adonis_app;
GRANT ALL PRIVILEGES ON DATABASE adonis_app TO adonis;

Then further down in the docker-compse.yml there is

environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres

I followed the tutorial and also updated the .env file - the tutorial works - but does not use the database. So I created a model with a migration: node ace make:model User -m
now I want to run that migration to create the User table.
Running node ace migration:run gets me the error

[ error ] create table "adonis_schema" ("id" serial primary key, "name" varchar(255) not null, "batch" integer not null, "migration_time" timestamptz default CURRENT_TIMESTAMP) - permission denied for schema public

I want this setup with 2 users (postgres as the root user, adonis to be used by adonis) to work the way its suggested in the tutorial and understand postgre better (later i want to add a grafana user with just read-access).

• Why is the permission denied? Does the init .sql file need more instructions? Shouldn't the user adonis by default already have the required create rights on the public schema, or am I misunderstanding that concept of the public schema in postgres?
• Am I somehow completely on the wrong track, or should the tutorial somehow make this work and misses something (In which case I would create an issue for the adonis doc ...)?

Answer 1

Tutorial should work. I use it myself :)

I believe you skipped this part of docs: