Hacker News new | past | comments | ask | show | jobs | submit login

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?



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.


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.


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.


Schemas are very handy for grouping related tables under a single namespace.


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.


Thank goodness with Postgres 13 the uuidv4 function got moved into core (not requiring pgcrypto installation)


Maybe you are thinking of uuid-ossp? I'm not aware of pgcrypto being required for UUIDs.


> 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


Ah! Well TIL. Thank you


Yeah in general any extension should go in its own schema.


Put your tables in table space, and limit things in public to be read-only or execute-only.




Consider applying for YC's Spring batch! Applications are open till Feb 11.

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: