Mat view error: ERROR: cannot set parameter "role" within security-restricted operation

[asotolongo]

Hi all , i want to create a mat view that call a function inside a package and i get the following error:

ERROR:  cannot set parameter "role" within security-restricted operation

here is the code:

CREATE OR REPLACE PACKAGE mi_paquete
is

FUNCTION mi_funcion(p_valor numeric) RETURN varchar2;  
END mi_paquete;

CREATE OR REPLACE PACKAGE BODY mi_paquete
IS
 FUNCTION mi_funcion(p_valor numeric)  RETURN varchar2 is

    mayus  varchar2;
    tempo varchar2;
    BEGIN
        select datname into tempo   from pg_database where oid=p_valor;
        
        if tempo in ('template1','template0') then
            mayus := 'BD no validas';
        
        else 
             mayus := upper(tempo);
        end if;
        
        
        RETURN mayus;
    EXCEPTION
    WHEN no_data_found THEN
        
        RETURN 'no encontrada';

    END;  

END mi_paquete;




CREATE  MATERIALIZED VIEW vista_v as 
select mi_paquete.mi_funcion(15709)

---------------------------------
ERROR:  cannot set parameter "role" within security-restricted operation
********** Error **********

ERROR: cannot set parameter "role" within security-restricted operation
SQL state: 42501

any idea? i never use set role inside the packages!!! 

i used:  "EnterpriseDB 10.4.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit"

Thanks 

[dhananjayjejur]

Hi asotolongo,

CREATE  MATERIALIZED VIEW vista_v as 
select mi_paquete.mi_funcion(15709)

This query will run within a security-restricted operation; in particular, calls to functions that themselves create temporary tables will fail with below error.

ERROR: cannot set parameter "role" within security-restricted operation

Function mi_funcion is creating table tempo inside its definition. You can replace it with rowtype variable.

Hope this helps.
 

[asotolongo]

hi @dhananjayjejur, thanks for response, 

i don't understand :
'Function mi_funcion is creating table tempo inside its definition'

however, if I create the same function in edbspl or  plpgsql (both without a package), the materialized view is created fine with both functions, 

example:

-----------------function in edbspl-----------------
 CREATE OR REPLACE FUNCTION mi_funcionedbspl(p_valor numeric) RETURN varchar2 is

 mayus varchar2;
 tempo varchar2;
 BEGIN
 select datname into tempo from pg_database where oid=p_valor;
 
 if tempo in ('template1','template0') then
 mayus := 'BD no validas';
 
 else 
 mayus := upper(tempo);
 end if;
 
 
 RETURN mayus;
 EXCEPTION
 WHEN no_data_found THEN
 
 RETURN 'no encontrada';

 END; 



CREATE MATERIALIZED VIEW vista_vedbspl as 
select mi_funcionedbspl(15709)

--------------------function in plpgsql------------
CREATE OR REPLACE FUNCTION mi_funcionpg(p_valor numeric) RETURNS varchar2 as $$ 
declare mayus varchar2;
 tempo varchar2; 
BEGIN 
select datname into tempo from pg_database where oid=p_valor;
 if tempo in ('template1','template0') then 
mayus := 'BD no validas'; 
else mayus := upper(tempo);
 end if; 
RETURN mayus; 
EXCEPTION WHEN no_data_found THEN 
RETURN 'no encontrada'; 
END; 
$$ language plpgsql ; 

CREATE MATERIALIZED VIEW vista_vpg as
 select mi_funcionpg(15709)

 

will it be something related to edbspl language and/or package?

any idea?

Regards

[dhananjayjejur]

Hi asotolongo,

This seems something related to packages.

Below is the document for Materialized view the error has been mentioned. However, it is not clear the exact reason.

CREATE MATERIALIZED VIEW

Hope this helps.

[asotolongo]

hi @dhananjayjejur thanks for response

hummmm, then I will extract the functions from within the packages for mat view :/ 

regards

[chaitalirs]

Hi  asotolongo,

While creating MATERIALIZED view there are few security restrictions applied, namely :

• Cannot create set parameters

• Cannot PREPARE TRANSACTION

• Cannot create temporary table

ERROR:  cannot set parameter "role" within security-restricted operation

 This is something related to the role. Can you share the user/role details by which you are executing the create MATERIALIZED view. Share the \du command output along with it.

[asotolongo]

hi @chaitalirs, thanks for response 

here teh data

regards

[chaitalirs]

Hi asotolongo,

I would suggest, if it is feasible then proceed with creating function instead of using package.

Meanwhile, I will look into this and get back to you. 

Thank you for your patience.

[asotolongo]

hi @chaitalirs, thanks again

i will wait some days, while you look into this, maybe can be a bug or the form that call de function inside packages execute some command implicit  "set role"

iam migrating a oracle database to edb postgres and have several mat view with call to function inside packages...

regards 

[chaitalirs · Accepted solution]

Hi asotolongo,

This issue we are getting is while populationg the data in the materialized view, if you create the materialized with option no data it is getting created.

CREATE  MATERIALIZED VIEW vista_v as 
select mi_paquete.mi_funcion(15709) with  no data;

But, obviously this does not make any sense.

Calling package from the materialized view is no longer supported now because of the limitation of security restricted operation from the version greater than 9.3.0.1.

Below is the test case prepared long time back :

edb=# select version();
version 
-------------------------------------------------------------------------------------------------------------------
EnterpriseDB 9.3.0.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
(1 row)

edb=# CREATE OR REPLACE PACKAGE pkgedbtest
edb-# As
edb$# function funtest() return text;
edb$# END pkgedbtest;
CREATE PACKAGE
edb=# CREATE OR REPLACE PACKAGE BODY pkgedbtest As
edb$# function funtest() return text
edb$# as
edb$# Begin
edb$# return cast('test' as text);
edb$# end ;
edb$# END pkgedbtest;
CREATE PACKAGE BODY
edb=# CREATE MATERIALIZED VIEW MV_TEST_PKG
edb-# as
edb-# select * from pkgedbtest.funtest();
SELECT 1
edb=# select * from MV_TEST_PKG;
funtest
---------
test
(1 row)

edb=# select version();
version 
-------------------------------------------------------------------------------------------------------------------
EnterpriseDB 9.4.2.7 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit
(1 row)

edb=# CREATE OR REPLACE PACKAGE pkgedbtest
edb-# As
edb$# function funtest() return text;
edb$# END pkgedbtest;
CREATE PACKAGE
edb=# CREATE OR REPLACE PACKAGE BODY pkgedbtest As
edb$# function funtest() return text
edb$# as
edb$# Begin
edb$# return cast('test' as text);
edb$# end ;
edb$# END pkgedbtest;
CREATE PACKAGE BODY
edb=# CREATE MATERIALIZED VIEW MV_TEST_PKG
edb-# as
edb-# select * from pkgedbtest.funtest();
ERROR: cannot set parameter "role" within security-restricted operation

Hope this helps.