Postgraphile: No values were updated in collection because no values were found (but the record does exist)

[AlineFrancois]

Hello :)

I am pretty new to graphQL so I'm sorry if this is very trivial. I'm trying to update a user first name using a mutation, but it returns No values were updated in collection 'users' using key 'id' because no values were found., which is very weird because I can see my record when querying allUsers

Here is the mutation I tried:

mutation test($input: UpdateUserByIdInput!) {
  updateUserById(input: $input) {
    user {
      firstName
      id
    }
  }
}

And the variable:

{
    "input":{
         "id": 6,
        "userPatch": {
            "firstName": "Aline update"
        }
    }
}

And here is my query on allUsers and the result, showing the record where id = 6:

Am I missing something ?

[benjie]

Hi @AlineFrancois; please try running PostGraphQL with the environmental variable DEBUG="postgraphql:postgres*"; it should output the SQL queries that are being ran which should give us some more hints.

Are you using Row Level Security or specific grants?

[jensneuse]

I had the same issue so my guess is your table permissions are insufficient. Make sure to allow insert, update and delete to current user.

[AlineFrancois]

Ok I'm making some progress thanks to you guys, I think I got this error because I was trying locally without setting the jwt token type and secret, and without sending any tokens.
Then I remembered that we do use RLS (at least trying lol) and it requires a valid token to identify the user.

So I wanted to test with a valid token and stumble accross some issues.

I tried to run it locally from the command line but I can't make it work; for some reasons -A or --jwt-audiences are ignored and it won't accept my auth0 tokens as the audience is not 'postgraphql'.

So I guess I have to debug it directly from my lambda function, in which the jwt-audiences option works well.

When providing the right token, I get another error where updateUser is still null, and the message is "Unexpected end of JSON input". I believe this is due to updateUser being null though.

@benjie Do you know how I can include this DEBUG environment variable when running postgraphql with nodejs ? We're using createPostGraphQLSchema and withPostGraphQLContext, and I couldn't find anything about environment variables in the doc

[jensneuse]

jwt-audiences also didn't work for me but there's an easy workaround.
Follow this guide and parse the header yourself: https://github.com/postgraphql/postgraphql/blob/master/docs/library.md
You can pass extra values to your database as described here:
https://github.com/postgraphql/postgraphql/pull/399
(It's not in the official documentation)
This way you can handle the jwt stuff yourself.

[benjie]

@AlineFrancois Just add the environmental variable when you run your node process, they're inherited (e.g. DEBUG="postgraphql:*" npm start)

[benjie]

I don't currently use the JWT features so can't be much help here I'm afraid - sorry.

[AlineFrancois]

Thanks :)

Finally able to run it with the debugging to understand what's up

So I am now using a valid token; here are the policies we set up so far regarding the user table:

create policy select_user_table on user_table for select
using (true); -- everyone can see the user list

create policy update_user_table on user_table for update to myRole
using (id = current_user_id());

create policy delete_user_table on user_table for delete to myRole
using (id = current_user_id());

grant select, update, delete on table user_table to myRole;

And here is the current_user_id function we defined:

select u.id
from user_table as u
where u.auth_id = current_setting('jwt.claims.sub')::varchar(50)

Basically we retrieve the row ID of the current user using the sub claim from the token. Note that all our users will also have the 'myRole' role thanks to the token

This is all I get from the debug info:

postgraphql:postgres begin +0ms
postgraphql:postgres select set_config($1, $2, true), set_config($3, $4, true), set_config($5, $6, true), set_config($7, $8, true), set_config($9, $10, true), set_config($11, $12, true), set_config($13, $14, true) +12ms
postgraphql:postgres commit +21ms
1 error(s) as myRole in 105.22ms :: mutation ($input: UpdateUserInput!) { updateUser(input: $input) { user { firstName } } } }

I don't know why it's calling set_config that much ?

[AlineFrancois]

UPDATE because I mixed things up lol

So when I try to call updateUser I get this new' Unexpected end of JSON token' error, but when calling updateUserById I'm back with the orginal issue in the title

And here is the debug info for this call

 postgraphql:postgres begin +0ms
   postgraphql:postgres select set_config($1, $2, true), set_config($3, $4, true), set_config($5, $6, true), set_config($7, $8, true), set_config($9, $10, true), set_config($11, $12, true), set_config($13, $14, true) +12ms
  postgraphql:postgres with __local_0__ as ( update "schema_name"."user_table" set "first_name" = $1 where "id" = ($2 + 0) returning * ) select row_to_json(__local_0__) as object from __local_0__ +20ms
 postgraphql:postgres commit +13ms
 1 error(s) as myRole in 114.59ms :: mutation test($input: UpdateUserByIdInput!) { updateUserById(input: $input) { user { firstName id } } }

EDIT

Ok guys, after further investigation, turned out I'm just an idiot :D

Did a select * from pg_policies; (thanks @florianherrengt for pointing this query to me) to see if we got our policies wrong and lo and behold there was no policy on UPDATE anymore .. Got lost during our fiddling with all that stuff I guess.

Sooooo this error is just the expected error when trying to update something you shouldn't get access to using RLS :)

Sorry for all the spam and a big thank you to @benjie and @jnsone11, you handed me some very nice tips about postgraphql :)

[vhf]

I had the same problem and google lead me here. This issue definitely helped me understand it was a permission problem, since my particular mistake was a bit different than what you could read here I'm writing it with the hope it'll help someone who'd follow the same path:

I had this exact same problem because of a confusion between using and with check:

No values were updated in collection 'thread' using key 'id' because no values were found.

create policy update_thread on my_project.thread for update to my_project_person
  with check (my_project.current_person_is_admin());

works:

create policy update_thread on my_project.thread for update to my_project_person
  using (my_project.current_person_is_admin());