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
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.
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.
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
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.
Hope this helps.
hi @dhananjayjejur thanks for response
hummmm, then I will extract the functions from within the packages for mat view :/
regards
Hi asotolongo,
While creating MATERIALIZED view there are few security restrictions applied, namely :
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.
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.
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
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.