[HN Gopher] Surviving without a superuser in Postgres 16
       ___________________________________________________________________
        
       Surviving without a superuser in Postgres 16
        
       Author : PaulHoule
       Score  : 96 points
       Date   : 2023-01-26 02:43 UTC (20 hours ago)
        
 (HTM) web link (rhaas.blogspot.com)
 (TXT) w3m dump (rhaas.blogspot.com)
        
       | idorosen wrote:
       | [flagged]
        
       | jph wrote:
       | 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.
        
         | chungy wrote:
         | > 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 wrote:
           | 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 wrote:
             | 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 wrote:
             | 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.
        
         | Someone wrote:
         | 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 wrote:
           | Schemas are underneath databases, but same idea :)
        
       | elijahmoses wrote:
       | 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.
        
         | tstrimple wrote:
         | I think ChatGPT may have escaped.
        
           | tarotuser wrote:
           | 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
        
       ___________________________________________________________________
       (page generated 2023-01-26 23:01 UTC)