Reset Fly Postgres credentials / detach / attach.

In order to get multiple apps working over the past few days, I ended up using the same connection url for postgres across multiple apps.

I would love to clean up the attach credentials that are native to each “attach connection”.

What is the best way to clean all that up and get the native DATABASE_URL back in the app using the local apps credentials. Essentially detach all apps, and reattach them so they are not each sharing a single apps “attach credentials”.

The best thing to do here is connect to your database with the admin credentials we generated at setup time, and then add/remove users as necessary. I use Postico, but you can also run fly ssh console and then connect with psql.

You can see the commands we run to create/delete users here: postgres-ha/main.go at main · fly-apps/postgres-ha · GitHub

If you connect using fly ssh console and you’re running a semi-recent version of our Postgres image, you can just copy and paste the following command:

psql postgres://postgres:$OPERATOR_PASSWORD@$FLY_APP_NAME.internal:5432

If you know the password up-front, you can substitute out the environment variables and run the command from your local machine.

Hope that helps.

2 Likes

Okay wow! I just learned a TON on how fly works and what was happening behind the scenes when you run attach.

I thought I would share with the community my experience:

I had NO IDEA that attach was creating new databases and setting the app’s connection string to that database, this was 100% my issue prior to needing the ?schema=public.

First, I created my fly postgres app, connected my first app with attach and then started using the app, creating things, etc.

Next, I created the next app that is suppose to share that same database, but I was not seeing the data, I assumed there was some issues with the schema and possibly data being created elsewhere, (I was definitely confused lol)

Now it all makes sense, the second attach command was creating a new user, password, privileges, AND a new database.

I guess I naturally gravitated towards attach early on thinking this was doing some internal *“magic” * to allow networking, etc.

As it pertains to the original post, I was able to login thanks to @shaun 's suggestion above (as I actually never saved the password from the second time around when I recreated the database).

running: psql \du and psql \l was eye opening and I immediately understood how it was all working!

I wonder if attach / detach is the correct terminology? I would also be tempted to think that if this is the correct terminology, that it might be pretty common to have multiple apps connected to the same postgres server and the same database.

After digging a little deeper into the attach api - I can now see that there is a argument for --database-name :exploding_head:.

Again, just wanted to share my overall experience with postgres and multiple apps “attaching”, etc.

Hope this helps!

3 Likes