Fully qualified relation and function names

[mkleczek]

Problem

Right now PostgREST requires configuration of db-schemas to expose database schemas. It also requires setting PostgREST specific headers in the request to select a schema if there are more than one configured in db-schemas. There were some ideas on how to make it more flexible, eg #2783. Suggestions on changing URI format and include schema name in path were rejected as not backwards compatible.

Solution

Accept fully qualified relation and function names, eg:

http://localhost:8000/cinema.films (SELECT * FROM cinema.films)
http://localhost:8000/rpc/app1.fun1(5) (SELECT * FROM app1.fun1(5))
http://localhost:8000/cinema.films?select=people.directors (Fully qualified computed relationship)

There would need to be some disambiguating done to handle relation/function names containing periods (like /rpc/index.html). To maintain backwards compatibility, names in a schema selected for current request from configured db-schemas should have precedence over fqns.

That would allow getting rid of db-schemas config altogether in the future (and would probably require configuration of db-excluded-schemas with default value public,pg_catalog,information_schema).

[steve-chavez]

Not sure about getting rid of db-schemas because we need some type of allowlisting but there was a discussion about a syntax for schema qualifying the objects on #2157 (comment), essentially swapping . for /, so:

http://localhost:8000/cinema/films?select=people/directors

On people/directors we would need to urlencode / to %2F.

Also a discussion about removing the /rpc prefix on #1086.

[wolfgangwalther]

http://localhost:8000/cinema.films?select=people.directors (Fully qualified computed relationship)

This will conflict with, for example, the ordering syntax, where those embeddings also need to be specified.

I think using schemas as part of the path is still the way to go. This also prevents any cross-schema stuff, which I think is a good thing to have.