Question 257142 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – After Revoking Select in postgres, RLS stopped working

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    If you have a policy in place that can verify the workspace_id condition, you don’t need to do it again in the function. You can go one step further and pass the service_name as a setting and leave it to the policy to check and compare it the same way you do for workspace_id: demo

    create policy "permissive_base" on "public"."services_connected"
as permissive for all to anon using(true) with check (true);

create policy "workspace_and_sevice_name_match" 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)
            AND
            (service_name = (current_setting('request.jwt.claims'::text, true))::json ->> 'service_name'::text )  )
with check ((workspace_id = (((current_setting('request.jwt.claims'::text, true))::json ->> 'workspace_id'::text))::bigint)
            AND
            (service_name = (current_setting('request.jwt.claims'::text, true))::json ->> 'service_name'::text )  );
--same would be required for users_list

CREATE OR REPLACE FUNCTION public.disconnect_service(service_name_value TEXT)
RETURNS VOID SECURITY INVOKER AS $f$
DELETE from services_connected;
--DELETE FROM users_list;
--for both of these, the RLS attaches 
--`where workspace_id = workspace_id_value and service_name = service_name_value`
$f$ LANGUAGE SQL;

    Without the where, there’s no need for select permission. It’s breaking these statement the same way a returning clause would – both of them potentially leak the contents of the table anon shouldn’t be able to inspect, so

    That, or define the function with security definer parameter the way you did for a selector function you mentioned in the other thread.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search