Having more than one internal schema on schema isolation is confusing #736
Description
Problem
Related to https://postgrest.org/en/stable/explanations/schema_isolation.html
A PostgREST instance exposes all the tables, views, and stored procedures of a single PostgreSQL schema(a namespace of database objects). This means private data or implementation details can go
I think I understand using an 'api' as the schema that is exposed to the outside work. It makes sense to have a separate schema for the actual implementation. I do not understand why you would have more than one (core + internal + private).
... more that one implementation schema.
My assumption is that "core", "internal" and "private" are just examples of names where your implementation would go. Is this correct?
Solution
Just have one internal schema. Maybe add another one for "extensions".
Activity
cboecking commentedon Jan 11, 2024
Thank you!
Here are my ignorant thoughts regarding what makes sense to me:
wolfgangwalther commentedon Jan 20, 2024
In my projects, I have settled on 5 schemas:
exposed
: This schema is set fordb-schemas
. It's the only schema that is directly accessible via PostgREST and generates all the endpoints.extra
: This schema is set fordb-extra-search-path
. Putting objects here allows them to still be recognized by PostgREST - but they won't be exposed. For example, some internal views can live here, which you don't want exposed, but you still want PostgREST to be able to "see through them" to infer foreign key relationships on the base tables. Also computed columns, which need to be in the search path can live here, without accidentally exposing them as RPCs in the exposed schema. Finally, I also keep some domains over my internals types here, so that I never expose anydata.xxx
custom types to the api layer - those would show up in the OpenAPI output. Everything inextra
just shows up without schema prefix, because it's in the search path.api
: Internal helper functions, which PostgREST does not need to know about, but are still only relevant to the api layer and not to data or business logic layers.I think those three schemas (four including data) should be represented in the "schema isolation" part, because they are needed to understand the differences between
db-schemas
,db-extra-search-path
and purely internal schemas.Each extension has it's own schema for me. That means it's immediately clear whenever I call a function from an extension, because it will always be prefixed with the extension's name.
cboecking commentedon Jan 23, 2024
One quick question I have is how to manage different versions of an API. Here is my assumption:
I am including this thought in this thread because the assumption is that we can separate versions in different schemas.
I believe this works if you adopt the practice of where you allow the public/exposted versions of the schema to introduce breaking changes; however, you ensure the private schema does not break any actively supported public schema versions.
Curious to hear thoughts... Chuck
wolfgangwalther commentedon Jan 24, 2024
I have not found a satisfying answer to that, yet.
Yes, absolutely. That makes a lot of sense.
Right.
Also mentioned here: #69. A slightly different approach is discussed in PostgREST/postgrest#2166.