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 1ug9So-00GZ2n-LM for pgsql-hackers@arkaria.postgresql.org; Sun, 27 Jul 2025 22:03:27 +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 1ug9Sm-0022bz-S3 for pgsql-hackers@arkaria.postgresql.org; Sun, 27 Jul 2025 22:03:25 +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 1ug9Sm-0022br-Cd for pgsql-hackers@lists.postgresql.org; Sun, 27 Jul 2025 22:03:24 +0000 Received: from mail-qv1-xf31.google.com ([2607:f8b0:4864:20::f31]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ug9Sk-0016GL-1f for pgsql-hackers@postgresql.org; Sun, 27 Jul 2025 22:03:23 +0000 Received: by mail-qv1-xf31.google.com with SMTP id 6a1803df08f44-7074710a90bso3794816d6.0 for ; Sun, 27 Jul 2025 15:03:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1753653800; x=1754258600; 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=Zoy4QgPOViNWTA0seYxg4WY2TYBRHvrli4JsPcd1F+8=; b=DNNTzR8DoO0l9+9qQyP6+4c+FzkoLvK+KhDkCN/9ow5JV++jTgH4AERMjCptmS6B5I PHk58BO42rkgGTG+6NeV3x9XBxJ9QFeIiYQeoQfQnAXt+i18LWt7/8R27tVoukmAP4hj 6mLhg35JFxa06i6GOeU6QNg+gIe2X05HGyDLV8s5ezQrCUWyj/2IRytXvgLb5j3s35s7 LOtA5o6Fyesv/5NwzUbbUdU/BH/uIh5nLm+gTScsuioEyjhz1L5jN1YjzmzxVxF7ZiEH +zXodrZQZYTbx1HGGsCtxywCMkGgyg6th0q3uUQr+mx9ke6TpxzZkSxKH6Ys1p6vYzK9 MGHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1753653800; x=1754258600; 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=Zoy4QgPOViNWTA0seYxg4WY2TYBRHvrli4JsPcd1F+8=; b=LDttvzh7oWFlc+cRQ2OaAcqqJLRwJKM1LoyQ4+NIGo87jANqLS8Tlh0rrp+o1h+EpT Hem++J7ROF5iKP44j67aIwovgB65QKquxYTACrIMcYg7Hx0jL7LZ49dKZ82RBHryc0zz Fkryzf4iaFpYQGcNGDGOa2ygIuE5eoQBfYobVHsmxLdbygRdc2rYb3XGqh4FmUXQCnul s3mfcy1FnKn/9icCH7+igl2qmsG3vbJV8P5OnwZqJ6JsLzb1tyGH/gA4ROzmQepskaRb Y+DxWzaGWIR1NpBb5mICRiLnq3PIf1sG+E24RdVFgzB7vukCzoIjegyBTE7BmZMZVfmp ge6Q== X-Forwarded-Encrypted: i=1; AJvYcCUukL0JZ7xqQfszHGFz53wMPitXYWfbOJDrhlRHZmSLB6IX5iNmxYWwIntOAWO4Lyg14fzmxKtV7zvuwh3B@postgresql.org X-Gm-Message-State: AOJu0YweYI29SUHl5mjfLKtxcLRXhG/zckm4MvhXpSc59jwFcdaeI3aX bTnUxlrO6NKuFTnX8gDK486UaGMchqVN7lkRh1xNtvMWszmWX5hM2UINgF5r2XHlIPa3AyQPZx8 YCgVxMLc2SKdN5NTyzYkSFRRQ2kJqeM8= X-Gm-Gg: ASbGncvuG/NWpQwHvFG1l7vYPdp8I2py5Kx3jo3XT5mG8+3bz9nZ1TMhphxrKBWAB+h b6u0N+PFTlviFS6hy+6lllo+mG2FC3UBMup3q/7eUXC6g5vLZNe+O0Rfn+hDZb4Fj3OZMcGy411 n284ctir0N28daS9mNLk7kfi78l+5dG+gmEq0EYnvvmVHFQveEppoak/VORusRpkocR4ZDtiTP0 pnUUG6FSpxydfi7U4Uz6hmM80WbzvU/gLKxgrY= X-Google-Smtp-Source: AGHT+IGrDpEwVRgn8J7OOAfy0ohTmVhJvbSiitvsxemtniq4vI7vqLvegrq75mRaGIu7o3vYAxsgpg4xQlIFsvr2khs= X-Received: by 2002:a05:6214:e4c:b0:704:f7d8:ed70 with SMTP id 6a1803df08f44-7072062e764mr158457106d6.46.1753653800246; Sun, 27 Jul 2025 15:03:20 -0700 (PDT) MIME-Version: 1.0 References: <585e996c-a5c6-4e61-acc4-d92b7a1458ea@vondra.me> In-Reply-To: From: Sadeq Dousti Date: Mon, 28 Jul 2025 00:03:09 +0200 X-Gm-Features: Ac12FXzW-ysCjAkUEEL0YPsICcSI5Y1jAm2m4WZEgiFkQdAP6-7d1756C89ZBpI Message-ID: Subject: Re: Extension security improvement: Add support for extensions with an owned schema To: Jelte Fennema-Nio Cc: Tomas Vondra , "David G. Johnston" , Jeff Davis , PostgreSQL-development , "David E. Wheeler" , Artem Gavrilov Content-Type: multipart/alternative; boundary="000000000000d2a26c063af0571e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d2a26c063af0571e Content-Type: text/plain; charset="UTF-8" Dear Jelte, I like the idea! In fact, I was thinking about the general search_path confusion issue in Postgres (see also [1]), and what the root cause is. IMHO, some search paths should always take priority - e.g., if a function is defined in both pg_catalog and as a UDF, the UDF should only be called if it is fully qualified, regardless of what the search_path is. But that would require an overhaul of the Postgres resolution mechanism, and is out of the scope of this patch. For this patch, I have a few suggestions: (a) The patch affects DROP EXTENSION in that it drops the schema as well, if it's owned by the extension. This needs to be mentioned in the documentation. In addition, an extra confirmation (e.g., "This will drop schema nnnn as well, do you wish to continue?") when dropping the extension might be desired, as the extension schema could contain user data (e.g., pg_cron keeps the jobs and their execution details). (b) From the patch description: > Writing the sql migration scripts that are run by CREATE EXTENSION > and ALTER EXTENSION UPDATE are security minefields for extension authors. While "ALTER EXTENSION UPDATE" is mentioned as a minefield, the patch does not fix it (only ALTER EXTENSION ... SET SCHEMA is affected AFAICS). A possible remedy could be that, before the update, the extension makes sure no (sensitive?) object (e.g., UDF/Operator) created by a non-superuser exists in its schema. (c) Does it make sense to add the "owned_schema" option to the CREATE EXTENSION command? Something like: CREATE EXTENSION xyz WITH owned_schema=true This way, even if the extension itself is not (yet) updated to have owned_schema in its control file, the DBA can rely on the schema lifecycle management that comes with owned_schema=true. An alternative could be to have it by default true (security by default), and if the DBA doesn't want it for whatever reason, they have to explicitly set it to false during CREATE EXTENSION. (d) As David (Wheeler) mentioned in the thread, an extension control file can have the "requires" field, in which an extension X installation depends on other extensions Y & Z to be installed. I was thinking if X calls a function from Y during installation, and Y does not have owned_schema, the search_path confusion attack can be transitively applied. It could make sense that X refuses to install, unless both Y & Z (= all required extensions) are marked as owned_schema=true. Although in favor of backwards compatibility, this can be overridable by an option in CREATE EXTENSION, such as "WITH transitive_owned_schema=false". [1] https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/ -- Best Regards, Sadeq Dousti Trade Republic Bank GmbH --000000000000d2a26c063af0571e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Dear Jelte,

I like the idea! In fact, I was thinkin= g about the general search_path confusion issue in Postgres (see also [1]),= and what the root cause is. IMHO, some search paths should always take pri= ority - e.g., if a function is defined in both pg_catalog and as a UDF, the= UDF should only be called if it is fully qualified, regardless of what the search_path is. But that would require an overhaul of the Postgres resol= ution mechanism, and is out of the scope of this patch.

For this pat= ch, I have a few suggestions:

(a) The patch affects DROP EXTENSION i= n that it drops the schema as well, if it's owned by the extension. Thi= s needs to be mentioned in the documentation. In addition, an extra confirm= ation= (e.g., "This will drop schema nnnn as well, do you wish to continue?&= quot;) when dropping the extension might be desired, as the exte= nsion schema could contain user data (e.g., pg_cron keeps the jobs and thei= r execution details).

(b) From the patch description:
> Writin= g the sql migration scripts that are run by CREATE EXTENSION
> and A= LTER EXTENSION UPDATE are security minefields for extension authors.
While "ALTER EXTENSION UPDATE" is mentioned as a minefield, the = patch does not fix it (only ALTER EXTENSION ... SET SCHEMA is affected AFAI= CS). A possible remedy could be that, before the=C2=A0update, the extensi= on makes sure no (sensitive?) object (e.g., UDF/Operator) created by a no= n-superuser exists in its schema.


(c) Does it make sense to add = the "owned_schema" option to the CREATE EXTENSION command? Someth= ing like:

CREATE EXTENSION xyz
WITH owned_schema=3Dtrue

Th= is way, even if the extension itself is not (yet) updated to have owned_sch= ema in its control file, the DBA can rely on the schema lifecycle managemen= t that comes with owned_schema=3Dtrue. An alternative could be to have it b= y default true (security by default), and if the DBA doesn't want it fo= r whatever reason, they have to explicitly set it to false during=C2=A0CR= EATE EXTENSION.

(d) As David (Wheeler) mentioned in the thread,= an extension control file can have the "requires" field, in whic= h an extension X installation depends on other extensions Y & Z to = be installed. I was thinking if X calls a function from Y during installati= on, and Y does not have owned_schema, the search_path confusion attack= =C2=A0can be transitively applied. It could make sense that X refuses to in= stall, unless both Y & Z (=3D all required extensions) are marked as = owned_schema=3Dtrue. Although in favor of backwards compatibility, this= can be overridable by an option in CREATE EXTENSION, such as "WIT= H tra= nsitive_owned_schema=3Dfa= lse".


[1] https://www.cybertec-postgre= sql.com/en/abusing-security-definer-functions/

--

Best Re= gards,
Sadeq Dousti
Trade Republic Bank GmbH

--000000000000d2a26c063af0571e--