Обсуждение: [NOVICE] Question about RLS policy

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