From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Johnson <ronljohnsonjr@gmail.com>
cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: PG16.1 security breach?
In-reply-to: <CANzqJaCZ_+UKf5g5qW8XDzVQO08yhKgJtr-T3vD0SAf5jLF0FA@mail.gmail.com>
References: <GV0P278MB00996776669F54A7EADB64688BFB2@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM> <8c533be4-5ed8-4658-86b6-212fb2d4d1a3@joeconway.com> <GV0P278MB00993C93868025F89845F58D8BFB2@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM> <6d223a4891287cfb08b720103faef2da1b5719f3.camel@cybertec.at> <CAKFQuwaMthLY0XFtv44EBwc=nAwJO0_onACZoG0bnj9jvPBA5Q@mail.gmail.com> <416045c0e7deac5b9f25e5fc89beec2a702a0b4c.camel@cybertec.at> <CAKFQuwbtQzCnXyaRdxeXOqEWszYoQqZiJwdy41X1bH_=cJK-ug@mail.gmail.com> <CANzqJaCZ_+UKf5g5qW8XDzVQO08yhKgJtr-T3vD0SAf5jLF0FA@mail.gmail.com>
Comments: In-reply-to Ron Johnson <ronljohnsonjr@gmail.com>
	message dated "Wed, 12 Jun 2024 17:37:22 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1691574.1718233014.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Wed, 12 Jun 2024 18:56:54 -0400
Message-ID: <1691575.1718233014@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1691575.1718233014%40sss.pgh.pa.us>
Precedence: bulk

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>> I think my point is that a paragraph like the following may be a useful
>> 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.

What I'd do if I didn't like this policy is some variant of

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

Repeat for each schema that you think might be publicly readable
(which is only public by default).

BTW, in PG 15 and up, the public schema is not writable by
default, which attacks basically the same problem from a different
direction.

			regards, tom lane