this is a more complete error
postgraphile:postgres begin +22s postgraphile:postgres select set_config($1, $2, true), set_config($3, $4, true) +2ms postgraphile:postgres with __local_0__ as ( postgraphile:postgres select to_json(__local_1__.__local_1__) as "value" postgraphile:postgres from unnest("crtrs"."get_notifications_alt"()) as __local_1__ postgraphile:postgres postgraphile:postgres where (TRUE) and (TRUE) postgraphile:postgres postgraphile:postgres postgraphile:postgres postgraphile:postgres ), __local_2__ as (select json_agg(to_json(__local_0__)) as data from __local_0__) select coalesce((select __local_2__.data from __local_2__), ''::json) as "data" +2ms postgraphile:postgres:error error: set-returning functions must appear at top level of FROM
select * from crtrs.get_notifications_alt();on my ide, it works ok
create or replace function admin.event_startdatetime(event admin.event) returns timestamp as $$ select event_start_datetime.event_start_datetime from admin.event left join (select admin.event_date_time.event_id, min(to_timestamp(EXTRACT(EPOCH FROM (min(admin.event_date_time.event_date) || ' ' || min(admin.event_date_time.start_time))::timestamp))::timestamp) over (partition by admin.event_date_time.event_id) as event_start_datetime from admin.event_date_time group by admin.event_date_time.event_id ) as event_start_datetime on event.id = event_start_datetime.event_id; $$ language sql stable security definer; comment on function admin.event_startdatetime(admin.event) is 'Get the start date/time of each event - computed column on admin.events table';
NolanIs it possible to define a RLS policy such that queries in
withclauses run outside of RLS checks? I have an order table, and an attempt table tracking attempts on those orders. I have RLS policies blocking which attempts people who fulfill the orders can see, but now I want a policy that shows orders without any attempts. Problem is, just because a given role can't see an attempt doesn't mean one doesn't exist.
NolanSo right now the RLS policy limiting what attempts someone can see kicks in. Then I have another RLS policy on orders that only shows any for which attempts don't currently exist, but if someone can't see the attempt then this check returns true and just shows everything.
CREATE POLICY select_admins ON topics FOR select USING (organization_id = any(current_user_admin_organization_ids()));
NolanGot it. And just to confirm what I suspect, RLS policies can't block updates to specific columns, right? I.e. if I have an
attemptthat everyone can see but only the recipient of the attempt can change, I should probably just define a custom mutation and @omit the field from the generated update mutation?
NolanStill trying to wrap my head around when it's best to use different tables vs. RLS vs. custom mutations/field definitions.
create role user_a; create_role user_b; grant user_a to postgraphile_user; grant user_b to postgraphile_user; grant update (a) on foo to user_a; grant update (b) on foo to user_b; create policy update_a on foo for update to user_a using(check_a()); create policy update_b on foo for update to user_b using(check_b());) - I've not tried this out yet.