[HN Gopher] PostgreSQL 15: default permissions for everyone is n...
       ___________________________________________________________________
        
       PostgreSQL 15: default permissions for everyone is now restricted
       to db owner
        
       Author : mattashii
       Score  : 110 points
       Date   : 2021-09-15 11:46 UTC (11 hours ago)
        
 (HTM) web link (www.depesz.com)
 (TXT) w3m dump (www.depesz.com)
        
       | artembugara wrote:
       | It reminds me how Elasticsearch changed their default index
       | shards number form 15 to 5.
       | 
       | Overall, I feel like decent software that is used by millions of
       | devs can afford being "a bit hard" in the quick start/first
       | interaction stages.
       | 
       | Look at how many articles there're about intro to something, and
       | how many about real/production cases. People leave default
       | configs, so I find it wise to make those restricted.
       | 
       | Can't even imagine how many products are running with software on
       | the default settings that are not supposed to be used in real
       | life setups
        
         | chousuke wrote:
         | I feel the same way. Default setting should be secure,
         | unsurprising and suitable for at least small-time production,
         | and good software further guides the user to use it such that
         | they aren't likely to shoot themselves in the foot.
         | 
         | Unfortunately, it seems quite often software defaults get
         | optimized for developer convenience and may do insecure things
         | like installing development-time dependencies by default...
         | 
         | When someone goes and uses that software in production, they
         | are very likely to end up using the bad defaults. That is _not_
         | the user 's fault.
         | 
         | If you can't provide at least secure defaults, the software
         | should not run at all until the user has made the necessary
         | choices.
        
       | terom wrote:
       | This is a good change. The default CREATE privileges on the
       | `public` schema are very surprising.
        
         | megous wrote:
         | What's a bit more annoying is the superuser ownership of the
         | public schema in the newly created DB.
         | 
         | Nothing that can't be fixed by removing the public schema from
         | the template DB, though...
        
           | chromatin wrote:
           | For a typical simple monolithic web application which is
           | likely to be the only client accessing the database, is there
           | any value in using a dedicated schema that is not the default
           | public?
        
             | tuatoru wrote:
             | Put your tables in table space, and limit things in public
             | to be read-only or execute-only.
        
             | dspillett wrote:
             | As already pointed out, some use schema as a namespace
             | feature.
             | 
             | Also within a very large monolithic app you might still
             | want some separation of access given to different modules,
             | so a nasty bug that allows injection or inspection attacks
             | has a more limited scope for causing DoS or exfiltration.
             | You can control access on a per-table basis or even per
             | column in some DBs, but the schema can be a convenient
             | place to configure that over a larger surface in one go.
             | 
             | How useful any of this seems to you is going to be a
             | subjective thing.
        
             | ptrwis wrote:
             | In example pgcrypto extension has a lot of functions, if
             | you have more stored functions it might be worth to install
             | pgcrypto in dedicated schema.
        
               | chromatin wrote:
               | Thank goodness with Postgres 13 the uuidv4 function got
               | moved into core (not requiring pgcrypto installation)
        
             | nicoburns wrote:
             | Schemas are very handy for grouping related tables under a
             | single namespace.
        
             | leetrout wrote:
             | IMO, no. Public is just what is there by default and you
             | could replace that with anything at the cost of having to
             | configure the search path. Not worth it unless you are
             | going to use multiple schemas anyway.
        
       | spapas82 wrote:
       | Wow I didn't know that nor I even expected it. Can you recommend
       | me a way to achive this behavior (i.e not allow users without
       | permissions to create tables in the public schema) in Postgresql
       | 13?
        
         | mattashii wrote:
         | you can change the permissions for the default schema after
         | databas ecreation, or change the permissions of the default
         | schema in the template database that is used when you create a
         | database
        
           | [deleted]
        
       ___________________________________________________________________
       (page generated 2021-09-15 23:02 UTC)