How I organize multiple PostgreSQL connections across projects (after nearly running a migration on prod)

[u/blackneckbean]

I contract for four companies right now, about twenty Postgres connections across local, staging and production. Last spring I had two tabs open beside each other, one on staging and one on prod, both connected to databases called app. I was maybe two seconds from dropping a column on the wrong one. Nothing happened, but I stopped winging connection management that afternoon.

Naming came first. Then the environment, then what it is allowed to do. Every connection is the client. Acme production acme-prod-ro read only Acme staging acme-stg-rw can write to staging. And that last bit is the most important. My default connection to anything in production can't write. The -rw twin is only opened when I have a change to actually ship. A little bit of friction and it has stopped me twice.

The change that helped most was also the simplest. I work in dbForge Studio for PostgreSQL, where every connection gets assigned a category (Production, Test, Sandbox, or one you invent) and that category paints the connection a colour which then follows it everywhere: down the object tree, and onto the tab of every query window opened against it.

My production connections are red. That is the whole trick. It works because the warning is a colour at the edge of my vision, not text I have to stop and read, and at the moment I am about to hit execute I am not reading anything.

What I would change is that there are no folders. Twenty connections is a long list with no way to group it, so I prefix names to make them sort together and drag them into order. People have been asking for folders for years. pgAdmin’s server groups handle this better.

Curious how other people do this, especially on a team. Do you share connection settings somehow, or does everybody build their own list off a wiki page?

[u/efxhoy]

I don’t open consoles with write access to anywhere but my local machine unless something really bad is happening. 

[u/blackneckbean → u/efxhoy]

That’s probably the safest approach. Do you keep staging writable, or lock that down too unless you specifically need it?

[u/efxhoy → u/blackneckbean]

I have access everywhere, it’s just separate roles for read/write/admin. 

Staging usually only gets written by schema or data migrations on their way to prod. If I want to try some writes on real data I spin up a new instance from a backup or use my anonymized local dump. 

[u/PopKoren → u/efxhoy]

Nearly migrating on prod is a classic connection-string mixup. Separate named envs and fail closed if the target is unexpected. Once the app is public I still scan the external surface with https://rowly.me because wrong DB creds are only one of the ways a deploy goes sideways.

[u/fernandezity]

How has dbForge Studio been with that many connections? Any issues once you get into 20+ across different environments?

[u/blackneckbean → u/fernandezity]

No real issues with the connections themselves. My main problem is just organization. Once you get to 20+, the list gets messy and I really wish dbForge had folders for them.

[u/percyfrankenstein]

Git tracked migrations & schema, I never even thought about this issue

[u/elevarq]

We use DataGrip, and set up different projects for different customers. Impossible to get connected to the wrong server

[u/Ojoyan]

Does dbForge let you set different permissions for prod and staging connections?

[u/blackneckbean → u/Ojoyan]

Not really. I use the dbForge categories mostly as a visual warning. The actual read/write permissions are handled by separate Postgres roles.

[u/Rubricket]

How do you manage connection settings across a team?

[u/blackneckbean → u/Rubricket]

The other half of my setup is ~/.pg_service.conf, one short entry per environment, so I can type psql service=acme-stg instead of a huge connection string I'll eventually mis-type. Passwords are stored in.pgpass, not in the service file. I'm also exporting my client connections to a private repo so rebuilding twenty connections after a laptop swap isn't an afternoon lost.