Resource embedding in the query instead of in the URL

[BorislavZlatanov]

I am wondering if it would be possible to make a nested API response (i.e. embedded resource) by specifying the nesting in the SQL query itself rather than in the endpoint URL. So rather than:

/tableone?select=a,b,tabletwo(c,d,e)

would it be possible to make something like this:

SELECT
a,
b,
  --begin fields nested under b
  c,
  d,
  e
  --end fields nested under b
FROM tableone
JOIN tabletwo
ON tabletwo.tableone_id = tableone.id;

So the URL can simply be /myview or /rpc/my_function - more friendly and without special characters that could cause issues. But even more importantly, devs wanting to utilize postgREST won't have to learn all the intricacies of how to do nesting, aggregate functions and so many other things through various kinds of syntax in the endpoint URL (often accompanied by structuring your function or view correspondingly so that the endpoint works).

Formulating a wide variety of queries through the URL also has limitations which don't exist if we can structure the API response within the SQL query itself.

As another point, not everybody wants to make their tables open to being queried directly and in various ways. I would much rather formulate the SQL queries myself and give the user access to the view or function, instead of letting the user modify the URL to make many different kinds of queries against my database. With open source software, this ability to add and execute all sorts of functions and joins and operators and so on can open up the doors to finding potential exploitations. It seems very difficult to foresee what combination of functions and operators might lead to a vulnerability in some situation.

Since I am making an app template which I'd like other devs to use for making their apps, keeping things more simple and secure, and using already known and widely used standards, seems far more preferable to me than creating new kinds of syntax, new learning curves and potential (and very difficult to predict) vulnerabilities.

Does anyone have any thoughts on this?

[rotty3000]

I am not an expert with PostgREST, but can't you already do this?

Create stored procedures which do the queries and filter as you like and only expose those.

I don't see any reason that you would be forced to expose more than exactly what you want.

[BorislavZlatanov]

Yes, I was thinking about that option, but it would have issues as well. I am not a postgREST expert either, so I might be mistaken.

Bear in mind, we are talking about nesting some fields under others.

As I understand it, to make the response as a JSON, postgREST uses some Postgres functions such as row_to_json(), array_agg() and array_to_json() (as explained in this post). So to nest some fields under other fields, I could manually use those functions for the rows I want to nest, i.e. I'd manually create the JSON response for the nested fields (including multiple levels of nesting). This doesn't seem to me like a good idea since the point of postgREST is to automatically form it as JSON for you. I don't know what issues it could create if a part of the response is manually made into JSON, and afterwards it's processed by postgREST.

Also, it would create another difficulty for devs to have to manually create the nested JSON - although this is what I've done before (prior to using postgREST) when I've made queries to Postgres and manually made a nested response in Nest.js, we can do far better and show the power of postgREST if it does the JSON nesting for us.

[rotty3000]

But given that PostgREST is generic how do you expect to tell it to do the nesting for you? Somehow you need to declare what you want. A stored procedure (or view) which does that seems like a fine method of declaration to me.

Here's an example of what I think you are asking for (as a stored procedure):

CREATE FUNCTION my_function
RETURNS SETOF record
AS $$
    SELECT
        one.a,
        one.b,
            --begin fields nested under tabletwo
            two.c,
            two.d,
            two.e
            --end fields nested under tabletwo
    FROM tableone as one
    JOIN tabletwo as two
    ON two.tableone_id = one.id;
$$ LANGUAGE SQL;

GET /rpc/my_function HTTP/1.1

Functionally, this is equivalent to /tableone?select=a,b,...tabletwo(c,d,e) but without any parameters.

(and there would be no need to make tableone or tabletwo available.)

[rotty3000]

any experts here please feel free to confirm (or rebuff) my assumptions :D

[BorislavZlatanov]

Yes, for sure we have to somehow tell postgREST how we want it to nest the fields for us, and what you showed in the example function would be a great way to do it. I am hoping the postgREST devs would consider adding this kind of ability for us to declare the nesting within the SQL query itself.

[wolfgangwalther]

As another point, not everybody wants to make their tables open to being queried directly and in various ways. I would much rather formulate the SQL queries myself and give the user access to the view or function

That's what we call Schema Isolation.

So to nest some fields under other fields, I could manually use those functions for the rows I want to nest, i.e. I'd manually create the JSON response for the nested fields (including multiple levels of nesting). This doesn't seem to me like a good idea since the point of postgREST is to automatically form it as JSON for you. I don't know what issues it could create if a part of the response is manually made into JSON, and afterwards it's processed by postgREST.

That's not a problem. You can return json from RPCs. You can return json column in your views. Arbitrarily nested, no problem...

Also, it would create another difficulty for devs to have to manually create the nested JSON - although this is what I've done before (prior to using postgREST) when I've made queries to Postgres and manually made a nested response in Nest.js, we can do far better and show the power of postgREST if it does the JSON nesting for us.

... but you don't need to do the json part yourself. You can also just return composite type columns, arrays of composite types, SET OF ... from RPCs etc. - all arbitrarily nested again. PostgREST / PostgreSQL will turn all of that into nice json for you.

Maybe I just misunderstand what you want to do, though.

[BorislavZlatanov]

Thank you very much for the explanation and the pointers. I guess there are many possible ways to accomplish this, as you say, and after some exploration I settled on using some json functions with which to form nested fields. This seems pretty simple and I can form the API response exactly how I want to.

So I json_agg() to nest an array field and json_build_object() to nest an object field, like this:

SELECT
a,
b,
  -- Nest under b
  (SELECT json_agg(t1)
    FROM (
    SELECT c
  FROM tabletwo
    WHERE tabletwo.tableone_id = tableone.id) t1
    ) AS myfieldname
FROM tableone;

And this would put all rows from the subquery into a single field as an array. The subquery result has to return only one column. If it returns more than one, json_build_object() can be used instead, like so:

(SELECT json_build_object(
      'c', t1.c) 
      FROM (
      SELECT c
      FROM tabletwo
      WHERE tabletwo.tableone_id = tableone.id) t1
      ) AS myfieldname

That's what we call Schema Isolation.

Yes, for sure. I was more so thinking about creating a function that takes in parameters and does more complicated things, like the things mentioned as potentially unsafe here. If there are any of the potentially unsafe kind of things in a function but the function is carefully constructed so that there wouldn't be a security issue, a potential vulnerability might be created if the SQL is changed by an attacker adding functions, operators and such to the endpoint URL. Do you think this could be an issue? Is there any way I could stop users from adding onto the endpoint URL, e.g. if they try /rcp/myfunction?myparam=blah,select=a,b,c(d) - to prevent them from changing the SQL even if they add select=a,b,c(d).

Thanks again for your help.

[wolfgangwalther]

I was more so thinking about creating a function that takes in parameters and does more complicated things, like the things mentioned as potentially unsafe here.

You are creating more problems than you are solving when doing this. You wanted to write your own queries, because of security concerns:

With open source software, this ability to add and execute all sorts of functions and joins and operators and so on can open up the doors to finding potential exploitations. It seems very difficult to foresee what combination of functions and operators might lead to a vulnerability in some situation.

This is just not how it works. Once you write dynamic SQL, you are far more likely to make a mistake, not get it right and introduce those security hazards.

The implementation of PostgREST prevents SQL injection. We use parametrized queries, so no matter what Robert'); DROP TABLE Students;-- tries to do, he won't succeed.

If there are any of the potentially unsafe kind of things in a function but the function is carefully constructed so that there wouldn't be a security issue,

That's a big if!

a potential vulnerability might be created if the SQL is changed by an attacker adding functions, operators and such to the endpoint URL. Do you think this could be an issue? Is there any way I could stop users from adding onto the endpoint URL, e.g. if they try /rcp/myfunction?myparam=blah,select=a,b,c(d) - to prevent them from changing the SQL even if they add select=a,b,c(d).

Now no matter which code you write inside your function, additional query params parsed by PostgREST (select, order, ...) are never going to affect the SQL inside your function. They might affect the SQL outside the function, in the overall query, but not your inner query / dynamic SQL. However, the arguments passed to your function could lead to such problems, if the dynamic SQL you create allows injection.

TLDR: Avoid dynamic SQL, use plain PostgREST features. That's the best and most secure you can do.

[BorislavZlatanov]

OK, thanks for the explanation! I fully agree with you. It's just that I am creating a template that can be used by other devs to create apps, and I wanted to minimize security concerns as much as possible. But as you point out, it's a big "if" whether something potentially unsafe can be made in a way that wouldn't have security issues. So probably better to go to the source and avoid potentially unsafe things altogether, as you are suggesting.