Thread: [NOVICE] Question about RLS policy
HI All,
I am new to postgresql and try to create RLS for a table using policy. I am wondering can I using plpgsql instated off sql inside using ()?
Garry
Garry Chen wrote: > I am new to postgresql and try to create RLS for a table using policy. I > am wondering can I using plpgsql instated off sql inside using ()? Not sure if I understand you right, but you must put SQL into the USING and WITH CHECK clauses. But you can call a PL/pgSQL function from that SQL. Yours, Laurenz Albe
Please keep the list on CC. Garry Chen wrote: > Thank you very much for your quick reply. I have a stored procedure or function > (getpredicate2) as following and would like to use it inside using() so when I create > policy as > create policy plc_acct_nbr2 on distribution_fc for select to public using (acct_nbr in > (select getpredicate2())) it return error as: > ERROR: operator does not exist: character varying = acct_nbr_lst > HINT: No operator matches the given name and argument type(s). You might need to add > explicit type casts. > ********** Error ********** > > ERROR: operator does not exist: character varying = acct_nbr_lst > SQL state: 42883 > Hint: No operator matches the given name and argument type(s). You might need to add > explicit type casts. > > > CREATE OR REPLACE FUNCTION public.getpredicate2( > ) > RETURNS SETOF acct_nbr_lst [...] Your function returns "SETOF acct_nbr_lst", and "distribution_fc.acct_nbr" seems to be of type character varying. PostgreSQL does not know how to compare these two types, you'll have to tell it. Either cast "acct_nbr" to "acct_nbr_lst", or have the function return "SETOF character varying", or (more complicated) create an operator named "=" that can compare these two data types. Yours, Laurenz Albe