0

I have a problem with SELECT when granting permission for the role.

I need to create a Customer role with the privilege to SELECT from 2 tables. And then I need to create a personalized role for one client, and GRANT customer to this role.

I did it this way:

CREATE ROLE customer;

GRANT SELECT ON public.payment TO customer;
GRANT SELECT ON public.customer TO customer;

ALTER TABLE public.payment ENABLE ROW LEVEL SECURITY;

CREATE POLICY policy_payments
ON public.payment TO customer
USING (customer_id = (
            SELECT customer_id
            FROM public.customer c
            WHERE 'client_' || lower(c.first_name || '_' || c.last_name) = current_role));
    
CREATE role client_julie_sanchez;

GRANT customer TO client_julie_sanchez;
  
SET ROLE client_julie_sanchez;
SELECT * FROM public.payment p

I see an empty table for both roles - Customer and client_julie_sanchez. What's wrong with the code?

4
  • The granted permissions are irrelevant - that part works, since you are actually allowed to execute the select statement; you don't get an error. The empty result set is caused by the RLS policy rather. Commented Feb 3, 2023 at 10:06
  • Which rows do customer and payment actually contain? Commented Feb 3, 2023 at 10:08
  • @Bergi yes, it seems that the problem is in RLS policy, but I don't know why. Customer and payment tables contain customer_id and some other columns Commented Feb 3, 2023 at 11:04
  • No, I mean which rows, not which columns? Though it would be great if you could add the table definitions to your question as well so that we have a minimal reproducible example Commented Feb 3, 2023 at 11:07

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.