Using Function Filters for Stored Procedures with Predicate Pushdown

[robertsosinski]

Hey again Y'all,

Quick question on the docs here: https://postgrest.org/en/v9.0/api.html#function-filters

I see if I make a function available via /rpc that returns a table type response, I can do filtering on the response. Would these filters/predicate get pushed down into whatever that function is querying? Also, if the answer is yes for a function that returns setof <real table>; what about if you define a dynamic table e.g. returns setof TABLE(id int, data text)?

Thanks!

-Robert

[wolfgangwalther]

To be able to push down filters, the function needs to be inlined. PostgreSQL has a number of requirements for a function to be able to be inlined, see here: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions#Inlining_conditions_for_table_functions

In short: Yes, it should be possible.

[robertsosinski]

Thanks @wolfgangwalther super helpful answer.

One more question, I noticed that inline table functions have the following constraint: the function is not SECURITY DEFINER

One thing I want to do is expose a PostgREST rpc function in one schema, say api_public, that can query tables in another schema that the PostgREST role does not have access to, say private. I've done this by putting functions in the api_public schema with SECURITY DEFINER and then define the function with the role that has access to the private schema. Marking the function with SECURITY INVOKER gives me a permission denied for schema private error.

I'm looking at using these functions to provide a constrained interface to more private data, so if there are other ways happy to hear them.

Thanks, as always, for the help!

-Robert

[wolfgangwalther]

In many of those cases a view instead of a function/rpc would work better. Views are security definer by default, but can still push down conditions - the exact nature of which depends on whether the view has a security barrier set and which filters / functions are used (leakproof or not etc.).

In the rare case where you actually need a function, basically because you are constructing a "smart view" that takes some arguments, you might be able to find a middle-way by:

• keeping tables in private
• adding some views on those tables, which could either be in the exposed public schema or in another hidden schema, for which the role running the request needs access, though.
• then make the exposed functions use the views.

You would still be able to push down some of the filters first by inlining and then through the view, if the conditions for that apply. However, there might not always be that much difference between granting access to the table and granting access to that intermediate view... the only advantage I see here, would be that you can shut down access for the private schema completely, which might make you feel more confident about security.

Of course, you could also select only some of the table's columns in the view, or add a WHERE clause to hide some of the rows... but you could do the same via column level privileges and row level security on the table directly.

In any case: Inlining means the function body statement is put into the outer query as-is - and therefore must run with the privileges of that user. So there is not really a way around that.

---

TLDR:

I'm looking at using these functions to provide a constrained interface to more private data, so if there are other ways happy to hear them.

Use column-level privileges and/or row level security for fine grained access.

[robertsosinski]

Thanks again @wolfgangwalther for the info. I think I have something that works well now; sharing here for others if it helps.

1. I created a view which I use to pre-filter rows that are visible to users. I would like to use RLS, but there are some complexities around access that helps to solve via a query. As the view can securely access private schema resources while also doing predicate pushdown; this solves my first issue. I can also put this view into a schema that the api user cannot see from PostgREST (controlling that with the PGRST_DB_SCHEMA parameter) but has permission to access from PostgreSQL (using grant select permissions). It also pulls out a larger permissions/access query from my function into a more reusable view. Thanks for the recommendation.

2. I then query the view via a function of type sql and security invoker. I can now pass more complex inputs as function parameters (such as tsqueries) that interact with data I don't want to expose via the view (such as tsvector strings) while also letting the user do further selections/ordering/etc... on the data they can see with url params.

This starts to look something like this:

GET /rpc/search?q=hello20%world&order=rank.asc&pinned=is.true

where q is my function parameter, and both order and pinned are query parameters that manipulate the result set with predicate pushdown working (confirmed with a query plan analysis).

I fell like I now have a good mix of securing access to private tables; abstracting unnecessary data/inputs from users; ease-of-use; and performance!

Thanks again, so glad to have this kind of convo here!