OpenAPI description foreign key description: schema missing

[fjf2002]

Environment

• PostgreSQL version: docker image 13
• PostgREST version: docker image v7.0.1
• Operating system: ubuntu 20.04 on WSL2

Description of issue

Preface

Foreign Keys to other schemas are valid, and other schemas can be queried by PostgREST, see https://postgrest.org/en/stable/api.html?highlight=openapi#switching-schemas .

Problem

The OpenAPI description contains in definitions.tablename.properties.columnname information on foreign keys:

This is a Foreign Key to `tablename.name`.<fk table='tablename' column='name'/>

What I am missing is the schema name (if this foreign key points to a table in another schema).
Could this be added? Thank you.

[steve-chavez]

Foreign Keys to other schemas are valid, and other schemas can be queried by PostgREST, see

This is true, but we don't allow cross-schema resource embedding.

We send a Content-Profile: withYourSchema header for every response(in case PostgREST is serving multiple schemas), grabbing that would give you the schema for the foreign keys. Would that solve your issue?

[fjf2002]

Hello Steve,
I am not sure if I understand you or you unterstand me at this point. I try to paraphrase:

When I download the OpenAPI spec for schema A, it will contain column descriptions like

This is a Foreign Key to `tablename.name`.<fk table='tablename' column='name'/>

These lines also occur for tables outside schema A. But how can I tell in what schema I can find the table "tablename"?

[wolfgangwalther]

We had a discussion about foreign keys over here: #1794 (comment)

We didn't change anything in the OpenAPI output, yet, but I assume we will not show any FKs that target other schemas at all down the road. At that point you can safely assume that any FK targets a table in the currently queried schema. Therefore, I'd consider this a bug right now.

[fjf2002]

Well, er... I'm currently relying on what you consider a "bug", so please do not remove that feature (?)

[wolfgangwalther]

Are you relying on the whole <fk ...> info being present - or are you relying on the fact that FKs to tables in other (non-exposed) schemas are shown, too?

I consider the 2nd one a bug, not the first.

[fjf2002]

Well, both. I don't have all my database objects in one single schema, but structured into multiple schemas.

[wolfgangwalther]

I don't have all my database objects in one single schema, but structured into multiple schemas.

So do I. But I fail to understand the value in having <fk> info for links to non-exposed tables. I can't use this info for embedding at all.

[fjf2002]

Well, my table A in schema A has as a foreign key the primary key of table B in schema B. Both tables and schemas are exposed - but the tables are in different schemas.

My client software uses the postgrest meta information, especially the fk relationships. For example, when editing table A, the foreign key is displayed as a dropdown of table B records.

The described relation between tables of different schemas is currently present in postgrest, but lacks schema information, as discussed.

[wolfgangwalther]

Both tables and schemas are exposed - but the tables are in different schemas.

So you are accessing those different schemas via the Accept-Profile header?

[fjf2002]

So you are accessing those different schemas via the Accept-Profile header?

Correct. (Or is there an alternative way?)

[wolfgangwalther]

While this is not documented explicitely, yet, I understand our approach of Schema Isolation to not only isolate the public schema from private/hidden schemas - but also to isolate different public schemas from each other. The Accept-Profile header feature (i.e. accessing different public schemas) is meant for api versioning or multi-tenant applications - both cases where those public schemas need to be separated cleanly from each other. The feature is not meant to support a "single api spread across multiple public schemas".

[fjf2002]

Thank you for your detailed answer. I still would be interested in being able to split my database in separate parts, or schemas, for a better overview.

[fjf2002]

Meanwhile I have migrated away from using the postgrest OpenAPI spec. I'm now using my own "meta-model", built as a PostgreSQL view, heavily using pg_catalog tables. I am now more flexible with this approach. My FK-relationships across schemas are now expressed by the latter.