Re: Policy function not working (IN parameter not passed)

On Fri, 2020-05-29 at 08:47 +0000, Rainer Floegel (Suva) wrote:
> create or replace function policy_test.get_partners(text)
>   returns boolean as
> $$
> declare
>  
>   is_true   boolean := FALSE;
>   the_count integer := 0;
>  
> begin
>  
>    select  count(*)
>      into  the_count
>      from  policy_test.partners a
>     where  a.business_partner = '125210000'
>       and  a.business_partner = $1;
>  
>    if the_count = 1 then
>      is_true := TRUE;
>    end if;
>  
>    return is_true;
>  
> end;
> $$ language plpgsql;
> 
> create policy test_partner_policy on policy_test.partners
>   using (policy_test.get_partners(business_partner));
> 
> select * from policy_test.partners;
> 
> Above select throws numerous lines shown underneath; sometimes in between the echoed SQL
> error message max_stack_depth exceeded occurs. Beyond that no other error messages appear. 
> 
> SQL statement "select  count(*)
>                           from  policy_test.partners a
>     where  a.business_partner = '125210000'
>       and  a.business_partner = $1"
> PL/pgSQL function policy_test.get_partners(text) line 9 at SQL statement
> SQL statement "select  count(*)
>                           from  policy_test.partners a
>     where  a.business_partner = '125210000'
>       and  a.business_partner = $1"
> PL/pgSQL function policy_test.get_partners(text) line 9 at SQL statement

What do you expect?

In the function you select from the table, so the policy is applied, which calls
the function, and so on.  Infinite recursion.

I am quite uncertain what your policy is supposed to achieve, but you should not
SELECT from the same table in the function.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com