Surviving without a superuser in Postgres 16 | Hacker News Reader

Surviving without a superuser in Postgres 16

rhaas.blogspot.com

106 pointsPaulHoule13 comments

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.

> 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 :)

I wonder if these changes are driven by the fact that AWS RDS (and presumably other DB as a service environments) don't give you access to the superuser?

Hopefully this will help finally smooth off the rough edges when trying to deal with Postgres databses in both environments; cloud/AWS RDS (for test/prod) and locally (for dev).

It's awkward to have logic branches in the database migrations to deal with the differences between the two environments (creation/re-creation of users and permissions, specifically).

I have never used PostgreSQL before, but a fellow programmer has been talking about it, and I feel I will be using it soon.

I will bookmark your article, since it will come in handy, when I start dabbling in PostgreSQL.

I think ChatGPT may have escaped.

My apologies. I misunderstood the nature of said question.

After surveying my field of information, I believe that Oracle databases are far superior to anything else on the market.

/Cringe

If OracleDB is fast and web scale I will use it. Is it web scale?

Does OracleDB support sharding? Shards are the secret ingredient in the web scale sauce. They just work.

What about impotence mismatch?

Reply on news.ycombinator.com