After Revoking Select in postgres, RLS stopped working

Question

I ran this query

REVOKE SELECT ON services_connected FROM anon;

and added this policy

create policy "workspace_only"
on "public"."services_connected"
for all
to anon
using ((workspace_id = (((current_setting('request.jwt.claims'::text, true))::json ->> 'workspace_id'::text))::bigint));

My Expectation was that this would restrict select operation but allow other operations (insert, update, delete) but now I am getting permission denied for all my responses

This is a function I am calling

DROP FUNCTION IF EXISTS public.disconnect_service;

CREATE OR REPLACE FUNCTION public.disconnect_service(service_name_value TEXT)
RETURNS VOID AS $$
DECLARE
  workspace_id_value BIGINT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'workspace_id')::BIGINT;
BEGIN
  DELETE from services_connected where workspace_id = workspace_id_value and service_name = service_name_value;
  DELETE FROM users_list WHERE workspace_id = workspace_id_value and service_name = service_name_value; 
END;
$$ LANGUAGE plpgsql;

any idea what I am doing wrong?

Answer 1

For must UPDATE and DELETE statements you also need SELECT privileges. Consider

DELETE from services_connected
where workspace_id = workspace_id_value
  and service_name = service_name_value;

PostgreSQL first has to read the table to identify the rows that meet the WHERE condition, only then it can delete the rows. That is not allowed without the SELECT privilege, so you get a "permission denied" error. This statement would work:

DELETE FROM services_connected;

because it has no WHERE condition.