Row level security
AdrienPensart opened this issue · 4 comments
Hello, thank you for documentation update!
I try to use upsert with row level security enabled, I have this table :
create table if not exists musicbot_public.music
(
id serial primary key,
user_id integer not null references musicbot_public.user (id) on delete cascade default musicbot_public.current_musicbot(),
title text default '',
album text default '',
artist text default '',
constraint unique_music unique (title, album, artist, user_id) deferrable
);
create index if not exists music_user_idx on musicbot_public.music (user_id);
(complete version here: https://github.com/AdrienPensart/musicbot/blob/master/musicbot/schema/d_music.sql)
With this spec in my mutation :
where: {
title: "Welcome To Bucketheadland"
album: "Giant Robot"
artist: "Buckethead"
}
I get this error :
there is no unique or exclusion constraint matching the ON CONFLICT specification
If I try to use userId: 1 in where and object spec, I have another error :
insert or update on table \"music\" violates foreign key constraint \"music_user_id_fkey\"
It works well when I disable row level security.
My old code (https://github.com/AdrienPensart/musicbot/blob/96d55d03096f998118b99c6b8fa4ba230030fe19/musicbot/schema/raw_music.sql) for upsert worked with a deferred constraint in a plpgsql function, but if I put deferrable again, it triggers this error :
ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters
Do you have an idea to fix this ?
Maybe I need a constraint trigger ?
Maybe this plugin should wrap the upsert statement inside a plpgsql function ?
there is no unique or exclusion constraint matching the ON CONFLICT specification
that makes sense. i think you were correct to add userId, otherwise the db scan would not be performant.
insert or update on table "music" violates foreign key constraint "music_user_id_fkey"
interesting. what do we know about this FK? i'd say run postgres in debug mode, logging all statements, or run postgraphile in debug mode, & capture the emitted query. discern what about the query specifically makes RLS grumpy. obviously we want RLS to work :). however, my PG is actually a bit rusty. i hit issues like this everytime i turn on RLS 🙄 😄 . i've never used deferrable, so you're now the domain expert here.
if we need to tune the emitted query to handle this case, i see no qualms with that. however, i'd need you to diagnose this and suggest a patch!
godspeed!
It works with these kind of constraint :
constraint unique_music_by_user unique (title, album, artist, user_id)
I had to inject userId manually inside the query, I am not sure if user IDs should be exposed to users with postgraphile RLS enabled...but it works.
Another issue is that this upsert does not play well with postgraphile-plugin-nested-mutations, linksUsingId is ignored, so we can't do nested upserts yet.
Maybe I can make multiple upserts using previous upsert result
mutation {
upsertMusic(
where: {
title: "Doomride"
album: "Giant Robot"
artist: "Buckethead"
userId: 1
}
input: {
music: {
title: "Doomride"
album: "Giant Robot"
artist: "Buckethead"
duration: 1
genre: "Avantgarde"
keywords: ["experimental", "heavy", "intro"]
number: 1
rating: 3.5
linksUsingId: {
create: [
{
url: "tests/fixtures/folder1/Buckethead/1994 - Giant Robot/01 - Doomride.flac"
}
{
url: "ssh://crunch@XXX.XXX.XX.XXX:tests/fixtures/folder1/Buckethead/1994 - Giant Robot/01 - Doomride.flac"
}
]
}
}
}
) {
clientMutationId
}
}
They are talking about it here :
For the moment I fixed everything using simple arrays, not a separate table...I close but if nested mutation are supported, it would be awesome!