Tangible Bytes

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,
     "name" varchar(255),
      "time" timestamp) 

- 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;
\c mydb
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.

As for GRANT ALL - I realise this is a missed opportunity for better security - but for now it gets me up and running.