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 1tT3KH-0037KD-Jc for pgsql-general@arkaria.postgresql.org; Wed, 01 Jan 2025 18:20:14 +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 1tT3KG-009aDi-SP for pgsql-general@arkaria.postgresql.org; Wed, 01 Jan 2025 18:20:12 +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 1tT3KG-009aDZ-Cp for pgsql-general@lists.postgresql.org; Wed, 01 Jan 2025 18:20:12 +0000 Received: from mail-oi1-x230.google.com ([2607:f8b0:4864:20::230]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tT3KE-001aq0-2B for pgsql-general@lists.postgresql.org; Wed, 01 Jan 2025 18:20:11 +0000 Received: by mail-oi1-x230.google.com with SMTP id 5614622812f47-3ee29277d44so3879438b6e.1 for ; Wed, 01 Jan 2025 10:20:10 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1735755610; x=1736360410; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=2Es22QVom7k+gEDmetBKeY/tjUOx55vH59AkF881bdQ=; b=QkeWeEfrmG8kDRxKRjn/en/2XLYgTwtOA5rO4rqBqZDLgk7eh2k8kvEc4iSYjGJhKE Q4Ho+mmeai5aH/2smGdqdwsJsAYCNEa/Ciimtnovdw2tBGkwrKwZHJ1RtOcbyAj8NwVm LEXx7+tA6n1VY/201+5jcFz/8EyoorWioMoN9FrY+gRK1eqrtB5ezbf1MR+OsJWJZkm0 Zc9UILvVBg+ELO9e5gOAEw1CBSK04XPIw/Q53kvORo9ftnH7G/B54PR2czvcVRoiER3H qzuxi2ze9iPbWKyaU6rChZziLwMDPR2r+c9JvOZnvAlND//Rjn6ULWNbMpEsX2lQSe2z Olkg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1735755610; x=1736360410; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=2Es22QVom7k+gEDmetBKeY/tjUOx55vH59AkF881bdQ=; b=ceLZECf8rcDwZxrsLjS3yLOP0B3o1lZeNWFMi02hw/s2YOWH88hMBQEpJ8EhDDRJdf aN47uwLnEwz63xNmKZvr68DfnhU87GlGnfTc9MdSlI9l10F9VGbOC4U9reCaTCoKLlVF 0hFI3ZF6kBYyaPvZI6g4zelUft7nK2UhqDYRfsIjK1D3Wz+K1tgSeGjJLM/6mLx6rDql 2vu7I2ptQJXmI4p4rM55UUwVye3HhDYWwHnkihjtFYKkgJ6p8EA6YSHNXkD+kLwdsBHr PRrclvUvxOoqX7Gr96YUnsFGRFi9ni7HnmFkFn02/5J0Bl3WYhei9RomXLd12fvLLubk ngkQ== X-Gm-Message-State: AOJu0Yzxtd9pQPgJCJL37INLbxXdbIvgp2ya2WviL6vVHCLberK3ArXe S8M0fG2BtvZ6McCGQttbrisHcsP/UiGkrLmWfwy028/TG0/AiQRKT2uAXkBE9raosds7rOswxBv RXE4cv3uTecBQ7ly0sH077DxnwiB9DOCB2SE= X-Gm-Gg: ASbGncstx3z2p4lTxvn6xa98iKQYbskUKHwsTwxBP/rqxBzA+nMmtp5wkSPsX23e1Oa JZarLg2yuwKh+mQxsU6mX7y1ysT3PKVOq+VnzdA== X-Google-Smtp-Source: AGHT+IE3P0L+G7tCP9Zl77Z+mHWRyNpDlDJHojqixtjykMT5oryCBJAGUnOU8IlMznX/GEp7J8cf1N1im9kwJXPsvDc= X-Received: by 2002:a05:6808:22a2:b0:3eb:66a1:9d93 with SMTP id 5614622812f47-3ed88f3ebd2mr19217979b6e.14.1735755608457; Wed, 01 Jan 2025 10:20:08 -0800 (PST) MIME-Version: 1.0 References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de> <20241228004009.267f21b78394c934f27f9974@magnetkern.de> <20250101185504.3d50c571c3448512e94288e8@magnetkern.de> In-Reply-To: <20250101185504.3d50c571c3448512e94288e8@magnetkern.de> From: "David G. Johnston" Date: Wed, 1 Jan 2025 11:19:32 -0700 Message-ID: Subject: Re: search_path for PL/pgSQL functions partially cached? To: Jan Behrens Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000075886d062aa9186a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000075886d062aa9186a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jan 1, 2025 at 10:55=E2=80=AFAM Jan Behrens wrote: > On Sat, 28 Dec 2024 00:40:09 +0100 > Jan Behrens wrote: > > > On Fri, 27 Dec 2024 13:26:28 -0700 > > "David G. Johnston" wrote: > > > > > > Or is it documented somewhere? > > > > > > > https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGS= QL-PLAN-CACHING > > > > I can't find any notes regarding functions and schemas in that section. > > "Because PL/pgSQL saves prepared statements and sometimes execution plans in this way, SQL commands that appear directly in a PL/pgSQL function must refer to the same tables and columns on every execution; that is, you cannot use a parameter as the name of a table or column in an SQL command." Changing search_path is just one possible way to change out which object a name tries to refer to so it is not called out explicitly. > "SQL-language and PL-language functions provided by extensions are at > risk of search-path-based attacks when they are executed, since parsing > of these functions occurs at execution time not creation time." > > Moreover, it isn't true for all > SQL-language functions, as can be demonstrated with the following code: > Yeah, when we added a second method to write an SQL-language function, one that doesn't simply accept a string body, we didn't update that section to point out that is the string input variant of create function that is affected in this manner, the non-string (atomic) variant stores the result of parsing the inline code as opposed to storing the raw text. David J. --00000000000075886d062aa9186a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jan 1, 2025 at 10:55=E2=80=AFAM Jan Behrens <jbe-mlist@magnetkern.de> wr= ote:
On Sat, 28 Dec 2024 00:40:09= +0100
Jan Behrens <jbe-mlist@magnetkern.de> wrote:

> On Fri, 27 Dec 2024 13:26:28 -0700
> "David G. Johnston" <david.g.johnston@gmail.com> wrote:
>
> > > Or is it documented somewhere?
> >
> > ht= tps://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-P= LAN-CACHING
>
> I can't find any notes regarding functions and schemas in that sec= tion.


"Because PL/pgSQL save= s prepared statements and sometimes execution plans in this way, SQL comman= ds that appear directly in a PL/pgSQL function must refer to the same table= s and columns on every execution; that is, you cannot use a parameter as th= e name of a table or column in an SQL command."
<= span class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-seri= f">
Changing search_path is just one possible way = to change out which object a name tries to refer to so it is not called out= explicitly.


"SQL-language and PL-language functions provided by extensions are at<= br> risk of search-path-based attacks when they are executed, since parsing
of these functions occurs at execution time not creation time."
=C2=A0
Yeah, when we added a second method to write= an SQL-language function, one that doesn't simply accept a string body= , we didn't update that section to point out that is the string input v= ariant of create function that is affected in this manner, the non-string (= atomic) variant stores the result of parsing the inline code as opposed to = storing the raw text.

David J.

--00000000000075886d062aa9186a--