public inbox for [email protected]  

help / color / mirror / Atom feed

Query regarding functions of postgres
2+ messages / 2 participants
[nested] [flat]

* Query regarding functions of postgres
@ 2024-04-07 12:04 Ayush Vatsa <[email protected]>
  2024-04-07 14:21 ` Re: Query regarding functions of postgres David G. Johnston <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Ayush Vatsa @ 2024-04-07 12:04 UTC (permalink / raw)
  To: [email protected]

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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Query regarding functions of postgres
  2024-04-07 12:04 Query regarding functions of postgres Ayush Vatsa <[email protected]>
@ 2024-04-07 14:21 ` David G. Johnston <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: David G. Johnston @ 2024-04-07 14:21 UTC (permalink / raw)
  To: Ayush Vatsa <[email protected]>; +Cc: [email protected] <[email protected]>

On Sunday, April 7, 2024, Ayush Vatsa <[email protected]> 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.


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-04-07 14:21 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-07 12:04 Query regarding functions of postgres Ayush Vatsa <[email protected]>
2024-04-07 14:21 ` David G. Johnston <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox