search

PostgREST / postgrest

REST API for any Postgres database
https://postgrest.org
MIT License
22.66k stars 1k forks source link

Ensure only schemas in `db-schemas` and `db-extra-search-path` are scanned for the schema cache #2015

Open steve-chavez opened 2 years ago

steve-chavez commented 2 years ago

Some DbStructure queries are not yet filtered by the schemas on db-schemas and db-extra-search-path:

https://github.com/PostgREST/postgrest/blob/5cd7d35966b8e8481215ca181c7629191e7e470e/src/PostgREST/DbStructure.hs#L488

https://github.com/PostgREST/postgrest/blob/5cd7d35966b8e8481215ca181c7629191e7e470e/src/PostgREST/DbStructure.hs#L736

Taken from https://github.com/PostgREST/postgrest/issues/2013#issuecomment-962438926.

steve-chavez commented 2 years ago

Right now the db-extra-search-path docs say:

This parameter was meant to make it easier to use PostgreSQL extensions (like PostGIS) that are outside of the db-schema.

Schemas where extensions are installed(public or extensions) can contain many tables and that conflicts with the purpose of not scanning schemas unnecessarily.

Maybe there should be another config param.

wolfgangwalther commented 2 years ago

I don't think we need to scan all the tables in db-extra-search-path by default. Right now, we're starting with scanning all tables in the whole database. The change should not be to just limit this to the tables in the public and extra schemas - but to reverse the whole process:

Of course, this is most efficiently done in a single query - think #1612.

steve-chavez commented 2 years ago

but to reverse the whole process

That's a great idea, I think it would also cover the use case where only functions are exposed:

Some users only expose functions through PostgREST so they don't really need embedding, that's where the none option could be useful(detecting relationships is generally fast, but could still take a while on big databases).

(From https://github.com/PostgREST/postgrest/issues/1783#issuecomment-962370105)

Since no table/view resource is exposed then functions wouldn't be able to embed(they don't have a target). In this case we could omit relationship scanning.