Hey all. It seems that MPG doesn’t permit management of users or roles outside of the basics provided by flyctl and the admin UI. This has turned out to be awkward! It means that as soon as I have two applications that need to use Postgres, they have at minimum full read access to all data in the cluster, and more realistically they likely have full write access too.
The solution can’t be “spin up another MPG” because down that path leads madness. Creating a whole cluster just to segment access to some tables is pretty wasteful and expensive, so for those reasons and more besides I think it’s ruled out.
The solution I’ve had to adopt is running my own pgbouncer to enforce user mapping to specific databases, as well as moving apps to their own private networks to further segment things. Even then, a fairly basic set of three microservices that require two private tables and one shared table is impossible to model on Fly today unless you use legacy Postgres or run your own Postgres on Fly or elsewhere.
Maybe I’m holding it wrong, but as a Postgres user since the time of the dinosaurs, I can’t think of why MPG needs to trade off security for any of the features it offers, based on the reading I’ve done of how MPG was put together (Percona?)
Anyway, I don’t want to presume there are not good reasons why the limitation exists. But, as someone who has merrily gone 100% all-in on Fly for everything, the limitation is very real and generating some technical debt I would dearly love to do without. I’d love to know if this is on the roadmap!
In a previous system I helped design, we were able to use hardware backed certificates to attest to workload identity, which then governed everything the app could do from L3 up to L7, including Postgres auth. Yeah it was a huge lift, and people like to hate on certs and mTLS, but removing not just the need but the ability for app owners to manage any kind of authentication or authorization ends up being enormously liberating! I can’t help but imagine a Fly that goes that far someday in the future too, it would be very cool to see.