I am working on a postgresql database for a strapi CMS
I’ve taken over the project and when I try and start it I see this error.
create table "public"."strapi_migrations"
("id" serial primary key,
- permission denied for schema public
It seems the project was created for an old version of postgres and I guess nobody tested the upgrade from an empty database.
Postgres 15 introduced new restrictions on the public schema and it took me a while to figure out how to add the permission.
On my init.sql file I needed
CREATE USER myuser WITH ENCRYPTED PASSWORD 'secret';
CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
GRANT ALL ON SCHEMA public TO myuser;
It was the
\c mydb that I couldn’t figure out.
What this does is changes the database to mydb so that the
GRANT ALL ON SCHEMA applied to the mydb database.
GRANT ALL - I realise this is a missed opportunity for better security - but for now it gets me up and running.