I’m trying to use neondb within Heroku. I’ve setup DATABASE_URL env var by getting the connection string from the dashboard page. But my app fail to migrate the database with "permission denied for schema public ".
What did i do wrong?
Hey @Paulo_Brito! Apologies for the super late response 
@TheBlackMan Do you mind sharing more details about what you are trying to achieve?
Managing databases via SQL is not currently possible, so the migration will fail if it includes a step where you create/delete a database. In the meantime, you will need to create the database manually, then apply the migration.
@Mahmoud I have created a database and a role, when I run migrations with the main role it works, but when I run with new created role I get the error message permission denied for schema public.
I think this is a case of the permissions system working in a way that you didn’t expect:
Under the hood, the users you create in the Neon UI are propagated as normal PostgreSQL users into PostgreSQL’s user/role system. These users are provided with not much more than connection privileges, the pg_read_all_data and pg_write_all_data roles, and ownership of databases (where applicable).
While pg_read_all_data and pg_write_all_data grant the user access to all schemas (PostgreSQL: Documentation: 14: 22.5. Predefined Roles), these roles do not allow the user to create new objects in that schema, which appears to be the error. So, to run DDL in the database (such as creating tables and indexes or adding types), you’ll need to use a role that can create new objects in that schema. You might be able to GRANT these permissions to your new user; alternatively, you may need to use your original user instead.
2 Likes