Digital Ocean sqlx migrate error - Chapter 5

[mitchr1598]

I think it's possible Digital Ocean have changed how they do things, or I just goofed up.

When trying to run the migration on page 157 (last of chapter 5), I get an error that I don't have permission on public schema.

I used the psql command with the same connection string to confirm the string was correct, psql also confirmed the "newsletter" user can connect to the DB, but doesn't have permissions on public.

I either goofed up, or Digital Ocean changed things.

If anyone else has this issue, this is the workaround I found:
Digital Ocean doesn't give us a connection string for an admin user to the db, however we can get it through their API

curl -X GET
-H "Content-Type: application/json"
-H "Authorization: Bearer $DIGITALOCEAN_TOKEN"
"https://api.digitalocean.com/v2/databases/<DB_ID>/users"

The DB_ID is in the databases settings on the console

This should return two users, and their passwords. The "newsletter" user, and a "doadmin" user. Connect to the DB with the "doadmin" login using psql, and update the permissions on the schema. You may need to set default permissions on tables in the schema as well.

[EncodedBird]

In the settings area for your app, look for the "Trusted Sources" segement. That is our culprit here as by default only the app itself is allowed to access the database.