Having more than one internal schema on schema isolation is confusing

[steve-chavez]

Problem

Related to https://postgrest.org/en/stable/explanations/schema_isolation.html

See: https://matrix.to/#/!YGChDzXeYxtlBZqVsc:gitter.im/$V_mpHPVIPg_QJ2YMWWu7s3K5d1_WGmbcBwrdm2oaRxU?via=gitter.im&via=matrix.org&via=matrix.freyachat.eu

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".

[cboecking]

Thank you! Here are my ignorant thoughts regarding what makes sense to me:

• private schema to hold internal representations
• api schema to hold rest interface (usually behind authentication)
• public schema to expose htmx functions (might or might not involve authentication)

[wolfgangwalther]

In my projects, I have settled on 5 schemas:

• Two schemas to hold the data tables + business logic. The reason for two schemas is just the way I migrate the schema and not related to PostgREST (see https://github.com/PostgREST/postgrest/discussions/2999#discussioncomment-7592206).
• Three schemas for the api layer:
  • exposed: This schema is set for db-schemas. It's the only schema that is directly accessible via PostgREST and generates all the endpoints.
  • extra: This schema is set for db-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 any data.xxx custom types to the api layer - those would show up in the OpenAPI output. Everything in extra 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.

Maybe add another one for "extensions".

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]

One quick question I have is how to manage different versions of an API. Here is my assumption:

• Each API version gets it own schema, and therefore its own postgrest config and therefore its own port.
• Use nginx to map a port to a specific 443 url path (example: https://someurl.com/v1/sometable gets mapped to port 3000 vs https://someurl.com/v2/sometable gets mapped to port 3001)

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]

One quick question I have is how to manage different versions of an API.

I have not found a satisfying answer to that, yet.

I am including this thought in this thread because the assumption is that we can separate versions in different schemas.

Yes, absolutely. That makes a lot of sense.

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.

Right.

Also mentioned here: #69. A slightly different approach is discussed in https://github.com/PostgREST/postgrest/issues/2166.