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 1tTlJ4-009w3E-F6 for pgsql-general@arkaria.postgresql.org; Fri, 03 Jan 2025 17:17:54 +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 1tTlI5-003gSY-MR for pgsql-general@arkaria.postgresql.org; Fri, 03 Jan 2025 17:16:53 +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 1tTlI5-003gS3-5K for pgsql-general@lists.postgresql.org; Fri, 03 Jan 2025 17:16:53 +0000 Received: from mail-oi1-x236.google.com ([2607:f8b0:4864:20::236]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tTlI2-001vC9-2t for pgsql-general@lists.postgresql.org; Fri, 03 Jan 2025 17:16:51 +0000 Received: by mail-oi1-x236.google.com with SMTP id 5614622812f47-3eb9de518e2so6261716b6e.2 for ; Fri, 03 Jan 2025 09:16:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1735924610; x=1736529410; 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=ooTRAqBsvGQGe+clgCrIOpE2yj6iQIL2IPdyebqsM7U=; b=HIUx8ULHPByeqIvDJTI6S/ubaX2zLH8kK8CBZjnAbrUkNbY/onAwCkQXhO/rGAFwHp vmvgDJWUc0q920vDEEMxmWLo1FSJIIV8mi8M6Ugl/bKTFlMDfyD4qcXpEdBPy2oMqoaN wjJ1SDymykpwjVabOITXRqvqC6BIIyWcEvXA4YgEA+w/4qujN7i6FinXvNixkmDvHv3e dF6FuUPtDVBoPrTAv25Q3lzBK3b2v0KNGTC5EUuhB/5U56Sipy7weNKXd9vrxum6UNiO +3rbHCoS8dRdPcIN2PSGGeUxo1nY/gXOSFNvorAbwvZx1auYJr6dvpaIBYuB2SfYHDkb qk2g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1735924610; x=1736529410; 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=ooTRAqBsvGQGe+clgCrIOpE2yj6iQIL2IPdyebqsM7U=; b=joo1iye+BkaMyGOTy9jn8XUFNqErqI7c6RkUFRX01hfkneTOTFL1gTcuEuqIckOzp9 GLYhKlCeD1d9VYelJlzAlgd3J3N8Cq9g9drnYjj6Caw2iQ+W3wG4Cv3F4ftId1w7d0rm xTwNDh2Bd+v7QWvjQ6JCU0AKFDem2mkiock7qAD4vswVOOEcWUVISfaXdJIStkXa4vjr SqELl8ptu1ner/CltKViPu4zQvcbDj8y5TLP484hVRr5ph46uO+Y/Tluhtemijbsd/RB rlZ7Rluc5Q0PJ8IwbTrfwxb2HMRTM8MXg+uSsPGXE8XfgM75TE654A4wt8TS7sbL7ze5 4Xiw== X-Gm-Message-State: AOJu0YwZRm6IY6gz+5g1rNxLSQ9mqBKc7cBYBvCiU4NuTSJw5yBBrNOh 3t8igBbJGXSshrHOMeuOGT0PuUul/Yd+Vax8FrRahGQwNcEjgr2ULXFVOrDEsQWqX0LBuyn99fJ pJ0oebKagSilOFjyv8MLgnP960PYEaZ2H X-Gm-Gg: ASbGncvBrw9Jd/26V4c9mtzpHTTWtHwUNUSf0sh3oTFqPeZdNjKcZSRzBPWzlvcSw4I +XnL13BLKSot1s7spTxi6Ov+KyYif/QIxoSdWeg== X-Google-Smtp-Source: AGHT+IEfGPH6bgIGVr9DWvtIBgWJX3qFh3A3rKdqID9GPxNI8J01eIUM6pjTahq7BGpz1i4/yjF59Vpy53EncO0TVw8= X-Received: by 2002:a05:6808:1a02:b0:3eb:42d0:f3c with SMTP id 5614622812f47-3ed88edfc72mr26323623b6e.3.1735924610188; Fri, 03 Jan 2025 09:16:50 -0800 (PST) MIME-Version: 1.0 References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de> <20241228004009.267f21b78394c934f27f9974@magnetkern.de> <20250103135332.7910a008714abaa04d32e5e2@magnetkern.de> <20250103174849.f071d9c6942f76a9b812157c@magnetkern.de> In-Reply-To: <20250103174849.f071d9c6942f76a9b812157c@magnetkern.de> From: "David G. Johnston" Date: Fri, 3 Jan 2025 10:16:15 -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="000000000000bf4ed8062ad071f5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bf4ed8062ad071f5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Jan 3, 2025 at 9:48=E2=80=AFAM Jan Behrens wrote: > On Fri, 3 Jan 2025 08:34:57 -0700 > "David G. Johnston" wrote: > > > On Friday, January 3, 2025, Jan Behrens wrote= : > > > > > > I would like to know if the above example is correct. It seems overal= l > > > bulky, but I haven't found a better way, assuming that it can be > > > unknown where a particular extension has been installed to. In > > > particular I feel a bit insecure about where I have to fully qualify, > > > and where not. See the comments in the code above. > > > > > > Short answer, you cannot looking at a definition and know the answer - > > whether the code is going to be executed in a sanitized search_path is > what > > matters. > > I don't understand. Do you mean my last example is wrong / insecure? > If so, why? > It is at risk because it depends on the session search_path. That is all. Whether that risk turns into a failure to execute depends on how/when it is executed. I'm not that comfortable talking about security risks in this context though the current design goal is to mitigate such security issues by setting things up so the function execution fails rather than is executed insecurely. This is presently mainly done by setting the search_path to just effectively pg_catalog before executing the query, breaking any code depending on other schemas existing in the search_path. > > Anything that would be executed during pg_restore has to be made > > safe. Therefore, code that is only ever executed by applications > directly > > can use swarch_path. > > Why should the function be executed during pg_restore? If the function is used in building an index, or a materialized view, are the common cases. Trigger functions too. Note, this is talking about evaluating functions generally, not the one provided here specifically. > I could do that, but I would like to understand if that is really > necessary as it makes the interface more complicated, and I would like > to avoid unnecessary complexity in my interface. > > Is it really impossible to have functions without SET search_path in > the definition of a PL/pgSQL function if I fully-qualify all types in > the DECLARE section and if all other non-qualified identifiers occur > after set_config('search_path', ...)? > > If you add a set_config to the body of the function then you indeed avoid the problem. It is basically equivalent to adding a SET clause to the create function command. In this case even when the function is executed in a sanitized search_path environment (such as the one established by pg_restore) you are not relying on it. That non-reliance is all that really matters. David J. --000000000000bf4ed8062ad071f5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Jan 3, 2025 at 9:48=E2=80=AFAM Jan Behrens <jbe-mlist@magnetkern.de> wro= te:
On Fri, 3 Jan 2025 08:34:57 -= 0700
"David G. Johnston" <david.g.johnston@gmail.com> wrote:

> On Friday, January 3, 2025, Jan Behrens <jbe-mlist@magnetkern.de> wrote: > >
> > I would like to know if the above example is correct. It seems ov= erall
> > bulky, but I haven't found a better way, assuming that it can= be
> > unknown where a particular extension has been installed to. In > > particular I feel a bit insecure about where I have to fully qual= ify,
> > and where not. See the comments in the code above.
>
>
> Short answer, you cannot looking at a definition and know the answer -=
> whether the code is going to be executed in a sanitized search_path is= what
> matters.

I don't understand. Do you mean my last example is wrong / insecure? If so, why?

It is at risk because it depe= nds on the session search_path.=C2=A0 That is all.=C2=A0 Whether that risk = turns into a failure to execute depends on how/when it is executed.=C2=A0 I= 'm not that comfortable talking about security risks in this context th= ough the current design goal is to mitigate such security issues by setting= things up so the function execution fails rather than is executed insecure= ly.=C2=A0 This is presently mainly done by setting the search_path to just = effectively pg_catalog before executing the query, breaking any code depend= ing on other schemas existing in the search_path.


> Anything that would be executed during pg_restore has to be made
> safe.=C2=A0 Therefore, code that is only ever executed by applications= directly
> can use swarch_path.

Why should the function be executed during pg_restore?
If the function is used in building an index, or a materialized v= iew, are the common cases.=C2=A0 Trigger functions too.

<= div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif= ">Note, this is talking about evaluating functions generally, not the one p= rovided here specifically.

=C2=A0

I could do that, but I would like to understand if that is really
necessary as it makes the interface more complicated, and I would like
to avoid unnecessary complexity in my interface.

Is it really impossible to have functions without SET search_path in
the definition of a PL/pgSQL function if I fully-qualify all types in
the DECLARE section and if all other non-qualified identifiers occur
after set_config('search_path', ...)?

=
If you add a set_config to the body of the function then you indeed avoi= d the problem.=C2=A0 It is basically equivalent to adding a SET clause to t= he create function command.=C2=A0 In this case even when the function is ex= ecuted in a sanitized search_path environment (such as the one established = by pg_restore) you are not relying on it.=C2=A0 That non-reliance is all th= at really matters.

David J.
=C2=A0
= --000000000000bf4ed8062ad071f5--