In a particular table I don't want user to select but can perform insert, update and delete (ignore the use case for now)
Here, Is what I wrote before
create policy "workspace_only"
on "public"."services_connected"
as restrictive
for all
to anon
using ((workspace_id = (((current_setting('request.jwt.claims'::text, true))::json ->> 'workspace_id'::text))::bigint));
create policy "no_select"
on "public"."services_connected"
as restrictive
for SELECT
to anon
using (false);
but this doesn't allow to insert even when this is true
((workspace_id = (((current_setting('request.jwt.claims'::text, true))::json ->> 'workspace_id'::text))::bigint));
This works though
create policy "no_select"
on "public"."services_connected"
for SELECT
to anon
using (false);
create policy "workspace_insert_only"
on "public"."services_connected"
for insert
to anon
WITH CHECK ((workspace_id = (((current_setting('request.jwt.claims'::text, true))::json ->> 'workspace_id'::text))::bigint))
What am I doing wrong here?
Answering the question and clarifying the case: take a look at Application of Multiple Policies:
When multiple policies of the same command type apply to the same command, then there must be at least one PERMISSIVE policy granting access to the relation, and all of the RESTRICTIVE policies must pass.
The first one disables all operations regardless of what's request.jwt.claims set to because you're missing at least one PERMISSIVE policy. You can add one as a base and it will work exactly how you expect: (demo 1)
create policy "base_permissive" on "public"."services_connected"
as permissive for all to anon
using (true)
with check (true);
The second one works because both policies are by default permissive but you'd have to enable insert update and delete separately (demo 2) to avoid an overlap of a permissive for all (or/including for select) that would be treated as an alternative to the one that's meant to disable select - in that scenario you'd be effectively adding OR false that would get ignored (demo 3)
A third way would be to set up one permissive for all and one restrictive for select: (demo 4)
create policy "no_select" on "public"."services_connected"
as restrictive for SELECT to anon
using (false);
create policy "workspace_insert_only"
on "public"."services_connected"
for all
to anon
using (workspace_id = ( (current_setting('request.jwt.claims'::text, true)
)::json ->> 'workspace_id'::text
)::bigint
)
with check (workspace_id = ( (current_setting('request.jwt.claims'::text, true)
)::json ->> 'workspace_id'::text
)::bigint
);
Note that if you add a where section to your update or delete, or follow insert, update or delete with a returning clause, it'll also be treated as a select and therefore disabled. Same if it was a revoke select preventing that.
As to the recommendation, I agree with the comments that a simple REVOKE select ON services_connected FROM anon; will do the job (demo 5) and it works fine with security definer. You can still guard the workspace_id match through RLS policies (demo 6).