cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Silver Adventurer

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

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
EDB Team Member

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

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();
ERRORcannot set parameter "rolewithin security-restricted operation

 

Hope this helps.

11 REPLIES
EDB Team Member

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

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.
 

Silver Adventurer

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

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

 

EDB Team Member

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

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.

 

Silver Adventurer

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

hi @dhananjayjejur thanks for response

 

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

 

regards

 

 

EDB Team Member

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

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.

 

 

Silver Adventurer

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

hi @chaitalirs, thanks for response 

 

here teh data

user_edb.pngcreate_mv.png

 

 

regards

EDB Team Member

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

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.

Silver Adventurer

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

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 

Highlighted
EDB Team Member

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

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();
ERRORcannot set parameter "rolewithin security-restricted operation

 

Hope this helps.