Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rtRGI-00ElDT-A2 for pgsql-general@arkaria.postgresql.org; Sun, 07 Apr 2024 12:04:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rtRGH-006JZr-Ee for pgsql-general@arkaria.postgresql.org; Sun, 07 Apr 2024 12:04:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rtRGG-006JZi-P1 for pgsql-general@lists.postgresql.org; Sun, 07 Apr 2024 12:04:37 +0000 Received: from mail-ed1-x534.google.com ([2a00:1450:4864:20::534]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rtRGE-001doA-6y for pgsql-general@lists.postgresql.org; Sun, 07 Apr 2024 12:04:35 +0000 Received: by mail-ed1-x534.google.com with SMTP id 4fb4d7f45d1cf-56e0e1d162bso3652386a12.1 for ; Sun, 07 Apr 2024 05:04:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1712491471; x=1713096271; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=rw84qtMwZFsaQchubwpkqjhEO/7t9eGoycmqSTc6QVw=; b=Jvf6cpth7FQ/r9Om4yN8Muc5JJjXADnxEVSN6sUcGbjlmmLZoQFWPXV1Q65YmJ7tL9 iXhcFzQIQAk+BzIDIF6DASmwi+zMkg0xcGUSi8F2pEWTt8XqnkFMMIlfoFCBPPcWXRzk YZAav7sEmNxgpGqdBkr9/ifCc0896GbCuvkgUoDgchkGKiQaBoFaQemtcT/yqSlUnqRw k/JDavGqOyB14j23Qi3eTK7ngLuxA2HqCQxqnhipUhoSugHVQawzvlvU5uKvWpMOzz/X qx7vB4IEcN1bULcu7D2qSXRfP2mYSZA11nnwpkn2vqVRfBUec/ckUsRhT3EjrBernR0r zqRw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712491471; x=1713096271; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=rw84qtMwZFsaQchubwpkqjhEO/7t9eGoycmqSTc6QVw=; b=scMpA9cWVHrYLAMc/bfQNiacpGYgIyZ4iCX8G1TdoaC4brdydDQHylDDxMk4/h0w8z MHF/vcExsYvT3gCtlfK8Q+vSwqllCeQj50pxdDfOHZ5oArqoVwoHc1PqToymABcW7s9B XqVutiBMjWtMLh1QNx7kzuqk2MrWEyxQxlzrWLVUOOc+OQeS0mxAcQa4sf1n9kYg4OGd uJ0cZxZK1LAk579tLau9gO/03WPhel0GjLaBO/340C3nDSKdLnFbfcKVh93zudTRSQyb mKaQgN2DujSAZ2/jrETVaLy6sbbBH4QRs0oS4xl62NZLsn0Z9dT6Zpjhgk28y5k2XCX6 ReSA== X-Gm-Message-State: AOJu0Yw3soW9rYFfIOT5S8olwbI+aBosPk4C5w8/QeaA6iqLYUqcyI2v WHtJcFiBCdwfBcHt/fbLTtlJcgAmUzwlnnwVx8K5KOZlktM1+l7Xnpkru0ENvcebbl/oNnkgqTa S+tWTATmYgcqZiPUyBccS/GN+E5tskOGsIdmOeg== X-Google-Smtp-Source: AGHT+IEnJUqpI2xa2pDyvplTNsyIhGXryQsnmy+cTeafBYx920G79glqTxwyB56hm4PSoV2LC76Ceo0XyC70r5iXTpY= X-Received: by 2002:a50:8e4e:0:b0:56c:2f3a:13a7 with SMTP id 14-20020a508e4e000000b0056c2f3a13a7mr3978201edx.25.1712491471201; Sun, 07 Apr 2024 05:04:31 -0700 (PDT) MIME-Version: 1.0 From: Ayush Vatsa Date: Sun, 7 Apr 2024 17:34:19 +0530 Message-ID: Subject: Query regarding functions of postgres To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000d26e850615807d6d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d26e850615807d6d Content-Type: text/plain; charset="UTF-8" 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 --000000000000d26e850615807d6d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi PostgreSQL Community,
= Recently I was reading about functions Immutability and security= definer but got confused
Whether the be= low 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)=20
RETURNS boolean AS $$
BEGIN
    RAISE NOTICE 'Comparing two text=
s';
    RETURN $1 =3D $2;
END;
$$ LANGUAGE plpgsql;
2. If a function has Raise not=
ice but extracting current user inside notice, although its output purely d=
epends on its input arguments eg.
CREATE OR REPLACE FUNCT=
ION text_equals(text, text)=20
RETURNS boolean AS $$
BEGIN
    RAISE NOTICE 'Current user: %', current_user;
    RETURN $1 =3D $2;
END;
$$ LANGUAGE plpgsql;
On security definer part I am confused with =
the below example
set role postgres=
;
CREATE OR REPLACE FUNCTION outer_func=
tion()
RETURNS TEXT AS $$
DECLARE 
    user_text TEXT;
BEGIN    SELECT 'OuterFunction() -> Current user is ' || current_use=
r INTO user_text;
    
    user_text :=3D user_text || ' | ' =
|| inner_function();
    
    RETURN user_text;
END;
$$ LANGUAG=
E plpgsql VOLATILE SECURITY DEFINER;
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_te=
xt :=3D 'InnerFunction() -> Current user is ' || current_user;    RETURN current_user_text;
END;
$$ LANGUAGE plpgsql VOLATILE SEC=
URITY INVOKER;
set role alex;
select outer_function();
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 outer_function =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 
-------------------------------------------------------------------=
------------------------
=C2=A0OuterFunction() -> Current user is pos=
tgres | 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 an=
swers, it will be helpful if someone helped me out here

Thanks,
Ayush Vatsa
=
SDE AWS
--000000000000d26e850615807d6d--