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?
customerandpaymentactually contain?