PostgREST fails to start with ogr_fdw in different schema

[bbarany]

Environment

• PostgreSQL version: PostgreSQL 11.2 (Debian 11.2-2) on x86_64-pc-linux-gnu
• PostgREST version: 5.2
• Operating system: Debian testing

Description of issue

I have a database with a schema (not the api schema) that has foreign tables with the type ogr_fdw.

I created the api_auth and api_anon roles and the api schema, set up permissions etc. I can connect to the database with api_auth and can select from a test table.

However, postgrest (configured for login with api_auth, using the api_anon role and the api schema) fails to start up, instead it throws the following error repeatedly until terminated:

Failed to query the database. Retrying.
{"hint":null,"details":null,"code":"XX000","message":"[1] Server is read-only WFS; no WFS-T feature advertized"}

PostgreSQL log:

2019-04-29 14:55:15.235 CEST [1325] api_auth@lww19 ERROR: [1] Server is read-only WFS; no WFS-T feature advertized
2019-04-29 14:55:15.235 CEST [1325] api_auth@lww19 STATEMENT:
SELECT
n.nspname AS table_schema,
c.relname AS table_name,
NULL AS table_description,
c.relkind = 'r' OR (c.relkind IN ('v','f'))
AND (pg_relation_is_updatable(c.oid::regclass, FALSE) & 8) = 8
OR (EXISTS
( SELECT 1
FROM pg_trigger
WHERE pg_trigger.tgrelid = c.oid
AND (pg_trigger.tgtype::integer & 69) = 69) ) AS insertable
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','r','m','f')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY table_schema, table_name, insertable
ORDER BY table_schema, table_name
2019-04-29 14:55:15.235 CEST [1325] api_auth@lww19 LOG: could not send data to client: Broken pipe
2019-04-29 14:55:15.235 CEST [1325] api_auth@lww19 FATAL: connection to client lost
CPLDestroyMutex: Error = 16 (Device or resource busy)

I understand the the problem is probably with the FDW, it shouldn't throw an error when querying the updatability of the relation.

However, the problem could be avoided if PostgREST had options like "ignore_schemas" or "only_schemas" or similar for whitelisting or blacklisting entire schemas in this query. It would also be a bit more secure and efficient if we could tell it to only ever look at the api schema.

[steve-chavez]

Looks like a particular error with ogr_fdw(FDWs work in general), as I've noticed in an issue pramsey/pgsql-ogr-fdw#163.

Though I think this error should be fixed upstream, a blacklist could be added as an option in PostgREST as you mentioned.

I think that that blacklist would be added to these two queries:

postgrest/src/PostgREST/DbStructure.hs

Line 362 in bcc317d

AND n.nspname NOT IN ('pg_catalog', 'information_schema')

postgrest/src/PostgREST/DbStructure.hs

Line 406 in bcc317d

AND n.nspname NOT IN ('pg_catalog', 'information_schema', $1)

For now, you could try modifying those, build from source and see if it indeed solves the problem.

[bbarany]

I confirm that excluding the "problematic" schema in the queries you mentioned fixes the problem.
Having a black- or whitelist would be nice.