5 ms·

PostgreSQL 15: default permissions for everyone is now restricted to db owner

120 points by mattashii 5y ago

terom 5y ago
This is a good change. The default CREATE privileges on the `public` schema are very surprising.
- megous 5y ago
  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 5y ago
    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?
    - leetrout 5y ago
      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.
    - nicoburns 5y ago
      Schemas are very handy for grouping related tables under a single namespace.
    - dspillett 5y ago
      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 5y ago
      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 5y ago
        Thank goodness with Postgres 13 the uuidv4 function got moved into core (not requiring pgcrypto installation)
        leetrout 5y ago
        Maybe you are thinking of uuid-ossp? I'm not aware of pgcrypto being required for UUIDs.
        chromatin 5y ago
        > If you only need randomly-generated (version 4) UUIDs, consider using the gen_random_uuid() function from the pgcrypto module instead. https://www.postgresql.org/docs/12/uuid-ossp.html https://www.postgresql.org/docs/12/uuid-ossp.html
        leetrout 5y ago
        Ah! Well TIL. Thank you
      - tragomaskhalos 5y ago
        Yeah in general any extension should go in its own schema.
    - tuatoru 5y ago
      Put your tables in table space, and limit things in public to be read-only or execute-only.
    - megous 5y ago
      That's quite some constrained use case. It's useful to have multiple schemas during development, and to allow the database owner to manipulate them, and not require superuser for these tasks. It's great that this will now be the default. It will make some of my DB initialization scripts easier. Currently database owner can't even remove the public schema in a new database, because it's owned by postgres role. I regularly make differently named copies of schemas and switch between them just by altering the search path in the app, just to try out some modifications or to run testsuites. You can drop and recreate an entire schema in a single transaction without the app having to close connections to the database at all, etc. It's useful for so many things, where grouping some DB objects together has benefits.
spapas82 5y ago
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 5y ago
  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 5y ago
    [deleted]
artembugara 5y ago
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 5y ago
  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.

120 points by mattashii 5y ago

terom 5y ago

This is a good change. The default CREATE privileges on the `public` schema are very surprising.

megous 5y ago
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 5y ago
  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?
  - leetrout 5y ago
    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.
  - nicoburns 5y ago
    Schemas are very handy for grouping related tables under a single namespace.
  - dspillett 5y ago
    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 5y ago
    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 5y ago
      Thank goodness with Postgres 13 the uuidv4 function got moved into core (not requiring pgcrypto installation)
      - leetrout 5y ago
        Maybe you are thinking of uuid-ossp? I'm not aware of pgcrypto being required for UUIDs.
        chromatin 5y ago
        > If you only need randomly-generated (version 4) UUIDs, consider using the gen_random_uuid() function from the pgcrypto module instead. https://www.postgresql.org/docs/12/uuid-ossp.html https://www.postgresql.org/docs/12/uuid-ossp.html
        leetrout 5y ago
        Ah! Well TIL. Thank you
    - tragomaskhalos 5y ago
      Yeah in general any extension should go in its own schema.
  - tuatoru 5y ago
    Put your tables in table space, and limit things in public to be read-only or execute-only.
  - megous 5y ago
    That's quite some constrained use case. It's useful to have multiple schemas during development, and to allow the database owner to manipulate them, and not require superuser for these tasks. It's great that this will now be the default. It will make some of my DB initialization scripts easier. Currently database owner can't even remove the public schema in a new database, because it's owned by postgres role. I regularly make differently named copies of schemas and switch between them just by altering the search path in the app, just to try out some modifications or to run testsuites. You can drop and recreate an entire schema in a single transaction without the app having to close connections to the database at all, etc. It's useful for so many things, where grouping some DB objects together has benefits.

spapas82 5y ago

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 5y ago
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 5y ago
  [deleted]

artembugara 5y ago

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 5y ago
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.