Can't Run `sqlx migrate run` "permission denied"

[currenthandle]

5.4.6 One Last Push (zero2prod_20240903.pdf)

>  DATABASE_URL=MY-DIGITAL-OCEAN-DB-CONNECTION-STRING sqlx migrate run

error: while executing migrations: error returned from database: permission denied for schema public

My spec.yaml, essentially the same as the book and your repo.

It seems that the newsletter db user doesn't have the permissions to run the run a migration.

I'm also considered connecting to the db as doadmin or root but I don't have a password for doadmin or see anyway to (re)set it on Digital Ocean.

Any ideas why the newletter db user doesn't have the permissions to run a migration and how to resolve? Thanks!

[maciejSzcz]

I faced the same issue while completing this chapter, and what worked for me is downgrading the database to postgresql 14 (I had to destroy the old database on DigitalOceans platform before reapplying the spec.json)

[currenthandle]

@maciejSzcz thanks for your reply. Yeah I tried the same thing after referencing @LukeMathWalker 's spec.yaml in the root-chapter-5 branch.

spec.json? Do you mean spec.yaml? Would you mind linking me to your repo?

[maciejSzcz]

@currenthandle You are obviously right, js brain took over. https://github.com/maciejSzcz/rs-mailer here is my repo, but your spec seems to be the same

[currenthandle]

Thanks! The only difference I see you and and the book have region: fra I have region: sfo3. Hard to believe this could be the issue, but maybe I should give Frankfurt a try :)

[currenthandle]

Ok I resolved the issue. I destroyed the project and started over. First with fra and then again switched back to sfo3.

This time I didn't see this error when I ran the migration, for whatever reason:

>  DATABASE_URL=MY-DIGITAL-OCEAN-DB-CONNECTION-STRING sqlx migrate run

error: while executing migrations: error returned from database: permission denied for schema public

Instead is just hung forever. Same when I did psql MY-DIGITAL-OCEAN-DB-CONNECTION-STRING.

Per this footnote

I unchecked this box in the Component > Database ("newsletter") settings and the migration worked. Then I rechecked the box to reenable the Trusted Sources protection.

[4ry1337]

For anyone stuck here, please read this doc carefully. Briefly, you can not migrate to dev database, you need to upgrade it to managed database. And use doadmin connection string. I hope this helped.