Function returning table from non `search_path` schema

[dvv]

Describe the bug Seems a function returning a table from another schema is not exposed.

To Reproduce Steps to reproduce the behavior:

begin;

create schema if not exists a1; grant usage on schema a1 to public;
create table a1.foo(id int); grant select on table a1.foo to public;
create or replace function a1.the_foo() returns a1.foo stable return (select f from a1.foo f where f.id = 1);
grant execute on function a1.the_foo() to public;

create schema if not exists a2; grant usage on schema a2 to public;
create or replace function a2.get_the_foo() returns a1.foo stable as $$ select * from a1.the_foo() $$ language sql;
grant execute on function a2.get_the_foo() to public;

set local search_path to 'a1';
select graphql.resolve($${__type(name: "Query") {fields(includeDeprecated: false) {name args {name type {kind name ofType {kind name}}}}}}$$)->'data'->'__type'->'fields'->1;
-- {"args": [], "name": "the_foo"}

set local search_path to 'a2';
select graphql.resolve($${__type(name: "Query") {fields(includeDeprecated: false) {name args {name type {kind name ofType {kind name}}}}}}$$)->'data'->'__type'->'fields'->1;
-- null

rollback;

Expected behavior I would expect fully-typed return values to be exposed based on permissions and regardless of search_path.

Versions:

• PostgreSQL: 16.1
• pg_graphql commit ref: ee8ef69

[dvv]

To narrow the problem.

I believe we should collect array of entities used in exposed functions (arg_types || type_oid). Filter them by schema usage permission. Expose them as types and may be connections (if some function returns the setof of the entity). Do NOT expose collection/mutation for them, effectively obey search_path instruction.

That way we won't be in need to proxy each and every piece of hidden schemas to the exposed schema.

[olirice]

yes, you're exactly right with the solution

One way would be to add a CTE under this to collect any tables or views referenced by functions on the search_path where the referenced table is not on the search_path and then updating the join here to include them

It might not actually require an update to the rust source but TBD