Push element inside an array column

[sscarduzio]

Given a table with a column of type character varying[], it should be possible to update this value with one more item.

PATCH /table/column 
{
 "push_value": "blah"
} 

should translate to:

update table set column = array_append(column, 'blah')

Similarly for jsonb[] typed columns:

PATCH /table/column 
{
 "push_value": {"a": "b"}
} 

would translate to:

update events set speakers = speakers || '{"a":"b"}'::jsonb

---

We could also support removing items from the arrays with something like:

PATCH /table/column 
{
 "pop_value": "blah"
} 

[begriffs]

Can you explain the use case for string concatenation in more detail?

[sscarduzio]

I fixed the character varying[] example, as it does not work with the append syntax. My bad!

[elimisteve]

@begriffs @sscarduzio It appears that there is no way to append to an array via PostgREST, is that correct? I see #465 and am thinking I should just create my own very simple RPC function that does the array append I desire...

[mckinlde]

@elimisteve did you create an rpc? I am currently trying to append to a bigint[] array and surprised there isn't a function for that.

[elimisteve]

@mckinlde I don't recall but yes that's how I would solve this problem if I were you; just make a 1-line function that you call via POST request to /rpc/your_func_name.

[mckinlde]

@elimisteve Thanks for the reply, after more reading I discovered that passing a parameter to a sql function would open me to an injection attack (input from the client being handled as instructions, rather than my current architecture that only handles input as data), so I've opted to instead use two requests GET/POST and handle appending client side.

If someone else wants to rm my comments as off-topic feel free; I'm uneasy about derailing this thread already as a non-contributing end user of pgrest.

[elimisteve]

after more reading I discovered that passing a parameter to a sql function would open me to an injection attack

@mckinlde Not if your function just appends to a bigint[] as described above.

Furthermore, part of the beauty of PostgREST -- if one handles auth correctly (using RLS etc) -- is that PostgREST becomes the database role/user of whoever is making the incoming authenticated request, and thus cannot do anything to the DB that the request-making user doesn't have permission to do. Pretty cool!

[mckinlde]

@elimisteve "Not if your function just appends to a bigint[] as described above."
I don't understand--an rpc can take a parameter int and append it without that parameter being vulnerable to a ;?

"using RLS etc"
+1, this capability is one of the main reasons I chose the tool--that said, I struggle with implementation. Can you refer me to a workbook? The best resource I have found so far is linked, I'll admit I haven't read it as I'm still full-time on MVP features: https://github.com/steve-chavez/socnet

[elimisteve]

@steve-chavez User input is surely escaped, yes? That's what Joe told me at Noisebridge, though he wasn't certain that every corner case was covered IIRC.

Plus user input that's supposed to be an int is parsed inside PostgREST and only used if it's actually an int, I believe; a semicolon can't simply be successfully injected by the user because PostgREST's int parsing would fail.

[mckinlde]

@elimisteve

@steve-chavez User input is surely escaped, yes? That's what Joe told me at Noisebridge, though he wasn't certain that every corner case was covered IIRC.

Plus user input that's supposed to be an int is parsed inside PostgREST and only used if it's actually an int, I believe; a semicolon can't simply be successfully injected by the user because PostgREST's int parsing would fail.

Would this hold for strings as well? For example, an rpc/login?

How do magnets work?(joke)

I posted in this thread, Postgres has a built-in function Array-append. Is it simpler in practice to add support for Postgres builtins than to implement 3rd standards? (in that thread, rfc6092 for json ops that include array appending)

:

In my case jsonb is not relevant; I have a bigint[] and I'd like to pass something like:

 const { data: userData, error: userError } = await patchPgData(
  rdsUri,
  Token,
  `/user?id=eq.${session.user_id}`,
  { saved_articles: `array_append(saved_articles, ${article.article_id})` }
);

An RPC was suggested in #488, and while that would work it seems unnecessarily specific. Could the built-in PostgreSQL Array-append function be called from postgrest? That (seems like) would cover many use cases, not require custom RPCs, and allow postgres errors to propogate through end-user http requests as I can attest I am already used to.

[steve-chavez]

after more reading I discovered that passing a parameter to a sql function would open me to an injection attack

We use prepared statements for all queries, so the above won't happen.

I don't understand--an rpc can take a parameter int and append it without that parameter being vulnerable to a ;?

That would be invalid input, for the same reason as:

postgres=> select '1;'::int;
ERROR:  invalid input syntax for type integer: "1;"
LINE 1: select '1;'::int;