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 1sFaO6-003i1m-A1 for pgsql-general@arkaria.postgresql.org; Fri, 07 Jun 2024 14:16:15 +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 1sFaO3-00CIhR-Dm for pgsql-general@arkaria.postgresql.org; Fri, 07 Jun 2024 14:16: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 1sFaO3-00CIh5-0c for pgsql-general@lists.postgresql.org; Fri, 07 Jun 2024 14:16:11 +0000 Received: from mail-oo1-xc29.google.com ([2607:f8b0:4864:20::c29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sFaNy-000Agg-JY for pgsql-general@lists.postgresql.org; Fri, 07 Jun 2024 14:16:10 +0000 Received: by mail-oo1-xc29.google.com with SMTP id 006d021491bc7-5b96a78639aso719722eaf.1 for ; Fri, 07 Jun 2024 07:16:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1717769766; x=1718374566; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=QzPuciPdYTeVKMxsRMW0/BoNmJAFMd5UnKEgQk3zmzk=; b=Vhb/AwSuZmk1TpjRzdDUkpJChnz03b8jh/CGA9rkX7ue7Y2mv6Vfff3M6POqDSiTBO oEZ4okl35zKzzuvCttUPW6UCqS0rpyY+CCOZ0OnTWrCxIWNt47pQXCpJ5tVVqG5alfD4 +tVY5X1CM2zM0nhb2flRc6EEmYb2ndBlZTG5MdsYVpyaq9oVYZRVBNmP8smYx6EvZ7a2 L9HV3srHcS8mso1U4c+K5aM9l1l29Doh6MbTVHAG/h7WO+7b9e4ecxigEb/IOj4Z+RIS 5G/8kmQWWai4Ag443WaJ2Jt2dkQ7/qBzLw0F4CtofG08E3wtwxxNIAVlAU8MA7f1EHtI L5pw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1717769766; x=1718374566; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=QzPuciPdYTeVKMxsRMW0/BoNmJAFMd5UnKEgQk3zmzk=; b=AjEo7NcEwBAncZpyGC9nuhz/pmN3usmjhvg4Z+21v+X43jfR1gsMXSXJLX+t5K5adS WqsLYcjuBeiXOL0NymOXVCBwxQkYODuPBSVQZPlVL1t3lMzxqu5T3mzPZVHepD9PN7r3 whAyBD8SFQug7C7d0qgp5xYH0VfvUi103alNZUd7gujmscXTzryqOhJKa/MAaQcQ6Gpm bA/vXbWyTVSlmagfGNg8dBUdDdrqjGJpm6Ptp5ntYFebuWZCi+Wlv8mKWnW8l2qZ2132 R2RIzmanpcaK3Uape8zjXDbq+xZl1z2NvU6dWLWmovzZjKRX+Y+J2o3//4tryyZZmMKD kPXg== X-Gm-Message-State: AOJu0YxGnACBkccjiXXIiNZNMRVppdfEir8ZiroG13q7GVqT1ffU5awx S9ORBo4E0pRfOEMQDg803y2ES4YwbDIyEowlFWS2kbG83sjNZA4TH21YncCmEtt1Ar83iVrJASO YynHtOjE1oro07ske9pyuWSlXTP8= X-Google-Smtp-Source: AGHT+IG0ulMe6Zl52g4XtrH/m490EoS1rVbiNFgp0FTNvI7D/+VfjMPvIxPRyHwMHDGmcIdq/FZKwy20/WaqJcWHgSk= X-Received: by 2002:a05:6820:1504:b0:5ba:8884:4138 with SMTP id 006d021491bc7-5baae89df2bmr2538051eaf.7.1717769765380; Fri, 07 Jun 2024 07:16:05 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a05:6802:387:b0:530:392d:d678 with HTTP; Fri, 7 Jun 2024 07:16:04 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Fri, 7 Jun 2024 07:16:04 -0700 Message-ID: Subject: Re: PG16.1 security breach? To: "Zwettler Markus (OIZ)" Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000abea95061a4d703f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000abea95061a4d703f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Friday, June 7, 2024, Zwettler Markus (OIZ) wrote: > > grant usage on schema oiz to public; > > > > The role is also able to execute the function even I revoke any execute > privilege explicitly: > > > > revoke execute on function oiz.f_set_dbowner (p_dbowner text, p_dbname > text) from testuser; > > You never typed =E2=80=9Cgrant execute =E2=80=A6 to testuser=E2=80=9D nor s= etup a default privilege for them, so there is nothing there to revoke. As was noted, the combination of your explicit usage grant, and the default execute grant, given to the public pseudo-role, enables this. > > > There are also no default privileges on the schema: > > You explicitly granted usage to the pseudo-role public=E2=80=A6 It is doubtful we=E2=80=99d add a global setting to control this. And it= =E2=80=99s a hard sell changing such a pervasive default. As most functions are security invoker, and many are side-effect free, the default does have merit. If your function is neither undoing the default is something that should probably be done. I could maybe see adding a new =E2=80=9Crevoke all default privileges from = public=E2=80=9D command. David J. --000000000000abea95061a4d703f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Friday, June 7, 2024, Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch> wrote:


grant usage on schema oiz to public;=

=C2=A0

The role is also able to execute the functi= on even I revoke any execute privilege explicitly:

=C2=A0

revoke execute on function oiz.f_set_dbowne= r (p_dbowner text, p_dbname text) from testuser;


You never typed =E2= =80=9Cgrant execute =E2=80=A6 to testuser=E2=80=9D nor setup a default priv= ilege for them, so there is nothing there to revoke.=C2=A0 As was noted, th= e combination of your explicit usage grant, and the default execute grant, = given to the public pseudo-role, enables this.

=C2=A0=

There are also no default privileges on the= schema:


You explicitly granted usage to the pseudo-role public=E2= =80=A6


It is doubtful we=E2=80=99d = add a global setting to control this. =C2=A0 =C2=A0And it=E2=80=99s a hard = sell changing such a pervasive default.=C2=A0 As most functions are securit= y invoker, and many are side-effect free, the default does have merit.=C2= =A0 If your function is neither undoing the default is something that shoul= d probably be done.

I could maybe see adding a new= =E2=80=9Crevoke all default privileges from public=E2=80=9D command.
=

David J.

--000000000000abea95061a4d703f--