Search Postgresql Archives

Query regarding functions of postgres

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi PostgreSQL Community,
Recently I was reading about functions Immutability and security definer but got confused
Whether the below two functions can be marked immutable or not
1. If a function has constant Raise notice inside it. Eg.

CREATE OR REPLACE FUNCTION text_equals(text, text) 
RETURNS boolean AS $$
BEGIN
    RAISE NOTICE 'Comparing two texts';
    RETURN $1 = $2;
END;
$$ LANGUAGE plpgsql;
2. If a function has Raise notice but extracting current user inside notice, although its output purely depends on its input arguments eg.
CREATE OR REPLACE FUNCTION text_equals(text, text) 
RETURNS boolean AS $$
BEGIN
    RAISE NOTICE 'Current user: %', current_user;
    RETURN $1 = $2;
END;
$$ LANGUAGE plpgsql;
On security definer part I am confused with the below example
set role postgres;
CREATE OR REPLACE FUNCTION outer_function()
RETURNS TEXT AS $$
DECLARE
user_text TEXT;
BEGIN
SELECT 'OuterFunction() -> Current user is ' || current_user INTO user_text;

user_text := user_text || ' | ' || inner_function();

RETURN user_text;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
create role test;
create role alex;
grant create on schema public to test;
set role test;
CREATE OR REPLACE FUNCTION inner_function()
RETURNS TEXT AS $$
DECLARE
current_user_text TEXT;
BEGIN
current_user_text := 'InnerFunction() -> Current user is ' || current_user;
RETURN current_user_text;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY INVOKER;
set role alex;
select outer_function();
                                      outer_function                                       
-------------------------------------------------------------------------------------------
 OuterFunction() -> Current user is postgres | InnerFunction() -> Current user is postgres
Shouldn't it be "InnerFunction() -> Current user is alex" instead of postgres as alex called the security invoker function

I tried reading docs but couldn't get any satisfactory answers, it will be helpful if someone helped me out here

Thanks,
Ayush Vatsa
SDE AWS 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux