From: | Ayush Vatsa <ayushvatsa1810(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Query regarding functions of postgres |
Date: | 2024-04-07 12:04:19 |
Message-ID: | CACX+KaM2bePqt6Oz=t2pA=B=+i2Amc9CsdR6nmbmR84M9Xs7mg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tony Bazeley | 2024-04-07 13:18:43 | Re: pg_dumpall - restoration problem- resolved |
Previous Message | Tom Lane | 2024-04-07 01:05:44 | Re: pg_dumpall - restoration problem |