4 ms·

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 databa

by jph 4y ago

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.

Someone 4y ago
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?
- chungy 4y ago
  Schemas are underneath databases, but same idea :)
chungy 4y ago
> 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?
- evol262 4y ago
  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...
  - chungy 4y ago
    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?)
  - jchw 4y ago
    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.

by jph 4y ago

Someone 4y ago

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?

chungy 4y ago
Schemas are underneath databases, but same idea :)

chungy 4y ago

> 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?

evol262 4y ago
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...
- chungy 4y ago
  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?)
- jchw 4y ago
  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.