Subject: Re: PG16.1 security breach?

From: Ron Johnson <[email-masked]>
Date: Wed, 12 Jun 2024 17:37:22 -0400

On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston <
[email-masked]> wrote:

> On Mon, Jun 10, 2024 at 2:21 AM Laurenz Albe <[email-masked]>
> wrote:
>
>> > How is it that the default privilege granted to public doesn’t seem to
>> care who the object creator
>> > is yet when revoking the grant one supposedly can only do so within the
>> scope of a single role?
>>
>> I don't understand what you wrote.  ALTER DEFAULT PRIVILEGES also only
>> applies to objects
>> created by a single role when you grant default privileges.
>>
>>
> 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.  You can revoke other privs from public (can't
you?), so why seemingly only do procedures/functions have this difficulty.

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 12 Jun 2024 15:08:14 -0700

On Wed, Jun 12, 2024 at 2:37 PM Ron Johnson <[email-masked]> wrote:

>>> > is yet when revoking the grant one supposedly can only do so within
>>> the scope of a single role?
>>>
>> 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
>>
>
Neither domain, language, nor type seem problematic.  Which just leave
connect and temp on databases which indeed have a similar issue but also
the number of roles with createdb is likely significantly fewer than those
with create on schema.

David J.

---

From: Tom Lane <[email-masked]>
Date: Wed, 12 Jun 2024 18:56:54 -0400

> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 12 Jun 2024 16:07:36 -0700

On Wed, Jun 12, 2024 at 3:57 PM Tom Lane <[email-masked]> wrote:

> execute

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.

---

From: Joe Conway <[email-masked]>
Date: Thu, 13 Jun 2024 07:59:20 -0400

>    REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

In a past blog[1] I opined that this cleans up the default security 
posture fairly completely:

8<----------------------
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE EXECUTE ON ALL ROUTINES IN SCHEMA public FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE EXECUTE ON ROUTINES FROM PUBLIC;

-- And/or possibly, more drastic options:
-- REVOKE USAGE ON SCHEMA public FROM PUBLIC;
-- DROP SCHEMA public CASCADE;

REVOKE TEMPORARY ON DATABASE <your_db> FROM PUBLIC;
REVOKE USAGE ON LANGUAGE sql, plpgsql FROM PUBLIC;
8<----------------------

> (which is only public by default).

indeed

> default, which attacks basically the same problem from a different

also a good point

[1] 
https://www.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-2

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com