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