This is what I want, so I ended up duplicating aut...
# sqls
sseppola
03/24/2022, 2:55 PMThis is what I want, so I ended up duplicating auth.uid() into the public schema in order to keep the auth schema isolated from users
g
garyaustin
03/24/2022, 3:01 PM@User I just looked at your code (just looked at error before) and thought you were accessing an auth table. I wonder if it is "select auth.uid()" causing the issue. auth.uid() on its own works in RLS and rpc functions. You might try:
Copy code
insert into...
values(auth.uid(), new_username)s
sseppola
03/24/2022, 3:31 PMInteresting! When I use values(...) select my editor warns about syntax error.
How can I still use select in order to fetch other properties?
My actual query looks like:
Copy code
insert into public.workout_session(owner_id, tags, upstream_workout_id)
select public.viewer_id(), workout.tags, workout.id
from (select id, tags from public.workout where id = workout_id::uuid) as workout
returning * into new_session;sseppola
03/24/2022, 3:43 PMI tried to get the auth.uid() out of the select statement with a variable, so now the sql in question is:
Copy code
select * from public.workout where id = workout_id::uuid into target_workout;
insert into public.workout_session(owner_id, tags, upstream_workout_id)
values (auth.uid(), target_workout.tags, target_workout.id)
returning * into new_session;g
garyaustin
03/24/2022, 3:51 PMHmmm, I'm puzzled, but not an expert on SQL. I'll double check with an rpc call, but not sure why auth.uid() would be different here than in RLS or if statements in rpc functions...
garyaustin
03/24/2022, 4:10 PMI just ran this as security invoker function from an rpc call:
Copy code
begin
insert into test(test3333)
values (auth.uid());
end;s
sseppola
03/24/2022, 4:24 PMOk, I'll try the same, one moment
sseppola
03/24/2022, 4:30 PMI still get the same error using the following:
Copy code
create table public.rpc_test (
test_value uuid
);
CREATE OR REPLACE FUNCTION public.rpc_test_fn()
RETURNS public.rpc_test
LANGUAGE 'plpgsql'
SECURITY INVOKER
AS
$$
begin
insert into rpc_test(test_value)
values (auth.uid());
end
$$;g
garyaustin
03/24/2022, 4:36 PMMine is also on hosted platform, no changes to schema. Are you sure you are not getting a return error on that one... you say to return a record, but don't...
garyaustin
03/24/2022, 4:47 PMIt is updating the table with the uid...
s
sseppola
03/25/2022, 8:53 AMI get the error:
{"code": "42501", "details": null, "hint": null, "message": "permission denied for schema auth"}