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 1r06Gm-003JJs-I1 for pgsql-hackers@arkaria.postgresql.org; Mon, 06 Nov 2023 20:32:24 +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 1r06Gk-001H2g-Cj for pgsql-hackers@arkaria.postgresql.org; Mon, 06 Nov 2023 20:32:22 +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 1r06Gj-001H2Y-Sr for pgsql-hackers@lists.postgresql.org; Mon, 06 Nov 2023 20:32:22 +0000 Received: from mail-pj1-x1036.google.com ([2607:f8b0:4864:20::1036]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r06Gh-004I3P-9E for pgsql-hackers@postgresql.org; Mon, 06 Nov 2023 20:32:20 +0000 Received: by mail-pj1-x1036.google.com with SMTP id 98e67ed59e1d1-28014fed9efso3900081a91.0 for ; Mon, 06 Nov 2023 12:32:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1699302738; x=1699907538; darn=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=/5D3r6U+3fymO4/fKX00+KYzgil2a5T1cmwzfFq5Atg=; b=UHl2pPZ4KzzPsoPag5rn1sA1Hsu7PGzeirDSVdDdavI/v7QuwLZcI/13LY03HuMnHR oOgXXDKq4DCe+xY8/WIwZrTomJ8T8xR+q3XQL3S7LXcf2J0iUWbxNEWTbRwRmog2p0z0 bbgINNQ6NrYnI/QFC/3T+mP65EsFVhDhllgiE1XciVc2X9MB+cJhxaa3FW2HPt6312KA U/wEsVmBUkwudH8vEQiRIJwlQ5K7ZX9XKI0XDqfgx1yyFB5YT3cvJ+ejAV1uRq+hWGpO koe8J9B0zYzGaaKVAjZ6lvT7n40mAlwuysLxEOLW2OcmX9Z19UPVnrbe1gzdh5cuzjwC 6ztA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1699302738; x=1699907538; 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=/5D3r6U+3fymO4/fKX00+KYzgil2a5T1cmwzfFq5Atg=; b=mJ1nGGfBeADkFTNWa2eMAPXBzV3f4b8Xvc38fxQh13cE/rsBJHUUJ4jbrhS6vUioQr VI4Dl2cEsCrnrCguVBZqyNt9IHtB0rek9v+XMNEAcaJx/2ZSycadf9Ts8WOl97QGsAF7 3VvJ7LYSAEeO3/vJuSaAv6u0Vc9/fEOI8vr6mHxMqq+fjdt+j4wGxLGUWHhGG3Q3wbKO dsWNu3wpVCqOEWy4z3hyG7dWwRH4U521ywQRq2pEGBPOprCMDxMDa+4Fo7Bt9ifibbcA Vbq19skQIWolUUPvrA0RdEWGa9CjKAcH1IAdipN7sKYdpxDKGF0tyYcq4ucxyMsOQLwm uHdQ== X-Gm-Message-State: AOJu0Yy9Lk3ohUtAAiWEjmx1tdGR79WKHsm58/NBJD1uBZoAwGAQ2y5C Ih4ojfHGai+HejIZbg7nmvEostJQkPPlxFe6aoI= X-Google-Smtp-Source: AGHT+IGbVxJTlB0z4FknSrX9bW9gXmNvmBdNcUeBecA5WXsvKTz6qTM4aH9XuhLqcWh+Kt5juNpVcH0ka1k3yuWrxUo= X-Received: by 2002:a17:90b:11c4:b0:27c:ef18:d270 with SMTP id gv4-20020a17090b11c400b0027cef18d270mr23501977pjb.20.1699302738166; Mon, 06 Nov 2023 12:32:18 -0800 (PST) MIME-Version: 1.0 References: <2cf41c8edeaaf7acb757095e6eceab8ae926f3da.camel@j-davis.com> <20230715211333.GB3675150@rfd.leadboat.com> <20230717175813.GA717781@nathanxps13> <2ad1dd4e5be5567c0d75676b348eab33b529e974.camel@j-davis.com> <88ec1efb7f5ee85e8a95962acaec3d1453d6b68f.camel@j-davis.com> In-Reply-To: <88ec1efb7f5ee85e8a95962acaec3d1453d6b68f.camel@j-davis.com> From: Isaac Morland Date: Mon, 6 Nov 2023 15:31:58 -0500 Message-ID: Subject: Re: Fix search_path for all maintenance commands To: Jeff Davis Cc: Nathan Bossart , Noah Misch , "David G. Johnston" , Gurjeet Singh , pgsql-hackers@postgresql.org, Robert Haas , Greg Stark , Tom Lane Content-Type: multipart/alternative; boundary="00000000000013192b060981c043" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000013192b060981c043 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, 2 Nov 2023 at 14:22, Jeff Davis wrote: > On Tue, 2023-10-31 at 13:16 -0400, Isaac Morland wrote: > > > Perhaps the search_path for running a maintenance command should be > > the search_path set for the table owner (ALTER ROLE =E2=80=A6 SET searc= h_path > > =E2=80=A6)? > > That's an interesting idea; I hadn't considered that, or at least not > very deeply. I feel like it came up before but I can't remember what > (if anything) was wrong with it. > > If we expanded this idea a bit, I could imagine it applying to SECURITY > DEFINER functions as well, and that would make writing SECURITY DEFINER > functions a lot safer. > I still think the right default is that CREATE FUNCTION stores the search_path in effect when it runs with the function, and that is the search_path used to run the function (and don't "BEGIN ATOMIC" functions partially work this way already?). But I suggest the owner search_path as an option which is clearly better than using the caller's search_path in most cases. I think the problems are essentially the same for security invoker vs. security definer. The difference is that the problems are security problems only for security definers. > After that, change search_path on function invocation as usual > > rather than having special rules for what happens when a function is > > invoked during a maintenance command. > > I don't follow what you mean here. > I=E2=80=99m referring to the idea that the search_path during function exec= ution should be determined at function creation time (or, at least, not at function execution time). While this is a security requirement for security definer functions, I think it=E2=80=99s what is wanted about 99.9% of the t= ime for security invoker functions as well. So when the maintenance command ends up running a function, the search_path in effect during the function execution will be the one established at function definition time; or if we go with this "search_path associated with function owner" idea, then again the search_path is determined by the usual rule (function owner), rather than by any special rules associated with maintenance commands. --00000000000013192b060981c043 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, 2 Nov 2023 at 14:22, Jeff Davis &= lt;pgsql@j-davis.com> wrote:
On Tue, 2023-10-31 at = 13:16 -0400, Isaac Morland wrote:

> Perhaps the search_path for running a maintenance command should be > the search_path set for the table owner (ALTER ROLE =E2=80=A6 SET sear= ch_path
> =E2=80=A6)?

That's an interesting idea; I hadn't considered that, or at least n= ot
very deeply. I feel like it came up before but I can't remember what (if anything) was wrong with it.

If we expanded this idea a bit, I could imagine it applying to SECURITY
DEFINER functions as well, and that would make writing SECURITY DEFINER
functions a lot safer.

I still think th= e right default is that CREATE FUNCTION stores the search_path in effect wh= en it runs with the function, and that is the search_path used to run the f= unction (and don't "BEGIN ATOMIC" functions partially work th= is way already?). But I suggest the owner search_path=C2=A0as an option whi= ch is clearly better than using the caller's search_path in most cases.=

I think the problems are essentially the same for= security invoker vs. security definer. The difference is that the problems= are security problems only for security definers.

>=C2=A0 After that, change search_path on function invocation as usual > rather than having special rules for what happens when a function is > invoked during a maintenance command.

I don't follow what you mean here.

= I=E2=80=99m referring to the idea that the search_path during function exec= ution should be determined at function creation time (or, at least, not at = function execution time). While this is a security requirement for security= definer functions, I think it=E2=80=99s what is wanted about 99.9% of the = time for=C2=A0security invoker functions as well. So when the maintenance c= ommand ends up running a function, the search_path in effect during the fun= ction execution will be the one established at function definition time; or= if we go with this "search_path associated with function owner" = idea, then again the search_path is determined by the usual rule (function = owner), rather than by any special rules associated with maintenance comman= ds.

--00000000000013192b060981c043--