Postgres Pro
Downloads
Home   >   mailing lists

Thread: [MASSMAIL]Query regarding functions of postgres

[MASSMAIL]Query regarding functions of postgres

From
Ayush Vatsa
Date:
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

Re: Query regarding functions of postgres

From
"David G. Johnston"
Date:
On Sunday, April 7, 2024, Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:

Whether the below two functions can be marked immutable or not
1. If a function has constant Raise notice inside it. Eg.

Seems legit.
 

2. If a function has Raise notice but extracting current user inside notice, although its output purely depends on its input arguments eg.
No

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

As soon as the system entered the security defined function it changed current_user to the definer of that function and won’t change back until the function returns.  Which it hasn’t when inner function is invoked.

David J. 

			
		

		
	

	
							

	

	
	

		

			
Re: Query regarding functions of postgres

									
		    

		        
From
 
		    	
Tom Lane

		    

		    

		        
Date:

		        




			

		


		

			
				
Ayush Vatsa <ayushvatsa1810@gmail.com> writes:
> 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;

The definition of "immutable" includes a statement that immutable
functions shouldn't have side-effects, so really this comes down to
whether you consider the emission of a NOTICE to be an interesting
side-effect.  It has no further effect on the computation, so you
could probably get away with considering it insignificant if you
choose to.  If you do, you are likely to observe the NOTICE coming
out at unexpected times --- for instance, it might appear once
during the planner's constant-folding phase, even though naive
interpretation of the query suggests that it should appear many
times or not at all.  But beyond that possible POLA violation,
neither of these functions will bother Postgres any.

            regards, tom lane