0

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 ...)?
IARI
  • 1,217
  • 1
  • 18
  • 35

1 Answers1

1

Tutorial should work. I use it myself :)

I believe you skipped this part of docs:

hlozancic
  • 1,489
  • 18
  • 31
  • Thanks for pointing this out, and sorry for not mentioning this explicitly, but no: I did not skip it. my .env contains the correct values `PG_USER=adonis`, `PG_PASSWORD=adonis` and the `PG_DB_NAME` (I changed that to my own name, but in all spots in the .sql and the .env as well) – IARI Oct 20 '22 at 21:47
  • I might have found my Problem: I deviated from the tutorial in the I used the `postgres:15.0-alpine` image instead of `postgres:13`. I just found: https://www.postgresql.org/docs/current/release-15.html stating 'Remove PUBLIC creation permission on the public schema (Noah Misch)' It might be worth adding a hint to this in the tutorial for other foolhearted devs that blindly try the latest postgres version. – IARI Oct 20 '22 at 22:44