Hacker News Reader

Comment by jph | Hacker News Reader

Thank you so much for working on the role security-- it's a major pain point with Postgres, such as for teams of teams that each need to create their own databases yet should be isolated from interfering with other teams' databases.

And I will gladly donate money to help toward a new feature that can scope by role name, such as a role "foo" that can create/delete/manage databases that start with foo_, such as foo_dev, foo_test, foo_prod, etc.

View on HN

> it's a major pain point with Postgres, such as for teams of teams that each need to create their own databases yet should be isolated from interfering with other teams' databases.

I'm curious: Why not set up multiple clusters for this?

Because sometimes you have multiple, discrete applications which should be isolated, but need high performance (40gbe, optane, huge amount of CPU concurrency, whatever) and don't want to slice up the server for virtualization or run it in the cloud. Or you won't want to manage multiple pgpool/pgbouncer configs. Or you want BGP-based site failover and it's more complex with more clusters, or replicating massive data warehouses/ML data/preprod is substantially faster if it can be a job on the same server instead of replication, or...

I wonder if something like Neon can help here. The pitch is that it refactors the dataplane out so that you can scale individual databases from 0. If it's suitable for scaling multitenant workloads, then it should be sufficient for multiple teams, I assume?

That said, it's a fork of Postgres, and I bet there's plenty of limitations. But I still find it interesting nonetheless.

If we're talking production, it's still trivially easy to have multiple clusters on the same OS without resorting to virtualization or cloud.

As the original post sounded though, it struck me more as a development thing (because why would production allow such access at all?)

Can’t you do effectively the same with schemas? Something like

  create schema foo;
  grant all on all tables in schema foo to foo;

would allow role “foo” to “create/delete/manage databases in schema foo, such as foo.foo_dev, foo.foo_test, foo.foo_prod, etc.

Or do you want a second level (possibly a hierarchy) of schemas?

Schemas are underneath databases, but same idea :)

Reply on news.ycombinator.com