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 1sHX4k-008iVV-Rf for pgsql-general@arkaria.postgresql.org; Wed, 12 Jun 2024 23:08:19 +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 1sHX4i-009Qdh-EQ for pgsql-general@arkaria.postgresql.org; Wed, 12 Jun 2024 23:08:17 +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 1sHX4h-009QZ5-T5 for pgsql-general@lists.postgresql.org; Wed, 12 Jun 2024 23:08:16 +0000 Received: from mail-oo1-xc36.google.com ([2607:f8b0:4864:20::c36]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sHX4f-0011sh-Sg for pgsql-general@lists.postgresql.org; Wed, 12 Jun 2024 23:08:15 +0000 Received: by mail-oo1-xc36.google.com with SMTP id 006d021491bc7-5baf982f56dso251066eaf.3 for ; Wed, 12 Jun 2024 16:08:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718233693; x=1718838493; 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=nha+Y2IdOtMqocv04yBOqN7SgLvVMoWCHqDWV8R/s8E=; b=hPke5tr+nWbHpDO7JT5ba9guUCjb5S98/f/o+T6gBUsCUOnySgb9iB9kk9wT6eEJJO jY0IMItzTeiy6qLQX4+WObbPhNGONWPEjSJcksqJzBhW9yvQk/zJ2wHDETdUwiLRfL8B QPzUeL1c60kRD3ZQr6go5p+lLggkuVkSwiuZ+CJIqeUgw98fU4QrvS0mzznZJy7F2c8q yyJwuhjz1CE9V8RDCX1lThpdIHVxbrkcmyEOjICcU49alaRjvBTCTHTeAsbdllM+bJJ7 Ld72AlyXhrspcYgbrr36k9pl4TVKsdb6c2iXQgao8ATPl+2ect7sSefgnmh+WOFaM47/ 8uDQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718233693; x=1718838493; 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=nha+Y2IdOtMqocv04yBOqN7SgLvVMoWCHqDWV8R/s8E=; b=mm6h+JmhwTmLvWYAltK4tbhYVAcNjjFIDHF2qwhuUU15Hcv4Y7tsnJjvuBvgM6Rbzb T4owW1zGdelNrqdgmLaFCNJ9gUdDkJBrqysCXIGEdYeSw/jI7JcfmpD0owy2kpGX/Xvz OGWfzJ34KzmTN4DWw3fbnUcsSQJsbK8xyfqXwdeCXYnOoKg0xahaM3jc/4a/8x7hXbyY Wo5vbUrSroydX2xK+E5jE2n3KWayHWGkwJYoFcuRbx5DgnK37fFAEPlGkLaZzYcT+uFo f5qBw2gLsQ6K+8CtJ1Ozyb0slpqR5QmQLUSUkZL85hw/XlqBp/ZSwVgWeUNPUYfuFYEU S24Q== X-Forwarded-Encrypted: i=1; AJvYcCV/eNSB1XqYGbZyPown1vRdI6vy4gIZQdORM/EW2ObGELU0RVcZsFtdIOmALB4eTdoDBwTok9Kv6HLhwY9ytN4f1DhmJvvWTH1Y2/6nYH9UBClw X-Gm-Message-State: AOJu0Yz7taljUg6G0Ej1R11A8yJjaVl2ubCjSkDGd10lbsWcoW6DOfY2 0Tx/aiI2NaExUTCTfAO5RYGQVfPHW26VLslNLPzNeEh01jLAJlmGzUoMXr+SjSv66XdtZLzJ+b1 gEDLDleUPSxFe+SJl7a6WX1NaYBkw1xir X-Google-Smtp-Source: AGHT+IECj7l2wl5zhABIsSmjdK1741aZ+px0ValaBxSG1k2ZkY8rGKwxWNnm6dg1M4XBCXD4as10Wxw+MNhkSAw89gw= X-Received: by 2002:a05:6820:1c9c:b0:5bb:e55:56a8 with SMTP id 006d021491bc7-5bb3b7a8db3mr3703988eaf.0.1718233692966; Wed, 12 Jun 2024 16:08:12 -0700 (PDT) MIME-Version: 1.0 References: <8c533be4-5ed8-4658-86b6-212fb2d4d1a3@joeconway.com> <6d223a4891287cfb08b720103faef2da1b5719f3.camel@cybertec.at> <416045c0e7deac5b9f25e5fc89beec2a702a0b4c.camel@cybertec.at> <1691575.1718233014@sss.pgh.pa.us> In-Reply-To: <1691575.1718233014@sss.pgh.pa.us> From: "David G. Johnston" Date: Wed, 12 Jun 2024 16:07:36 -0700 Message-ID: Subject: Re: PG16.1 security breach? To: Tom Lane Cc: Ron Johnson , "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000e91782061ab9747b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e91782061ab9747b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jun 12, 2024 at 3:57=E2=80=AFPM Tom Lane wrote: > Ron Johnson writes: > > On Wed, Jun 12, 2024 at 4:36=E2=80=AFPM David G. Johnston < > > david.g.johnston@gmail.com> wrote: > >> I think my point is that a paragraph like the following may be a usefu= l > >> addition: > >> > >> If one wishes to remove the default privilege granted to public to > execute > >> all newly created procedures it is necessary to revoke that privilege > for > >> every superuser in the system > > > That seems... excessive. > > More to the point, it's wrong. Superusers have every privilege there > is "ex officio"; we don't even bother to look at the catalog entries > when considering a privilege check for a superuser. Revoking their > privileges will accomplish nothing, and it does nothing about the > actual source of the problem (the default grant to PUBLIC) either. > Apparently my forgetting the word "default" in front of privilege makes a big difference in understanding/meaning. Alter Default Privileges FOR postgres Revoke Execute on Functions From PUBLIC; That is what I meant, I was wrong in that I wrote permission instead of "d If one wishes to remove the default privilege granted to public to execute all newly created procedures it is necessary to revoke that [default] privilege for every superuser in the system. The FOR postgres part is inferred, it matches the current role if omitted. If I now create (or even if there already existed) a new superuser named davidj and they create a function, the public pseudo-role will be able to execute that function. You would first need to execute the above command, substituting davidj for postgres, if you want to prevent that. David J. --000000000000e91782061ab9747b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jun 12, 2024 at 3:57=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
=
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Wed, Jun 12, 2024 at 4:36=E2=80=AFPM David G. Johnston <
> david.= g.johnston@gmail.com> wrote:
>> I think my point is that a paragraph like the following may be a u= seful
>> addition:
>>
>> If one wishes to remove the default privilege granted t= o public to execute
>> all newly created procedures it is necessary to revoke that privil= ege for
>> every superuser in the system

> That seems... excessive.

More to the point, it's wrong.=C2=A0 Superusers have every privilege th= ere
is "ex officio"; we don't even bother to look at the catalog = entries
when considering a privilege check for a superuser.=C2=A0 Revoking their privileges will accomplish nothing, and it does nothing about the
actual source of the problem (the default grant to PUBLIC) either.

Apparently my forgetting the word "defa= ult" in front of privilege makes=C2=A0a big difference in understandin= g/meaning.

Alter Default Privileges FOR postgres Rev= oke Execute on Functions From PUBLIC;

That is what=C2= =A0I meant, I was wrong in that I wrote permission instead of "d
=
If one wishes to = remove the default privilege granted to public to execute
all n= ewly created procedures it is necessary to revoke that [default] privilege=C2=A0for
every superuser in the system<= span class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-seri= f">.

The FOR postgres part is inferred, it matches th= e current role if omitted.

If I now create (or even if= there already=C2=A0existed) a new superuser named davidj=C2=A0and they cre= ate a function, the public pseudo-role will be able to execute that functio= n.=C2=A0 You would first need to execute the above command, substituting da= vidj for postgres, if you want to prevent that.

David = J.

--000000000000e91782061ab9747b--