public inbox for [email protected]
help / color / mirror / Atom feedRe: PG16.1 security breach?
5+ messages / 4 participants
[nested] [flat]
* Re: PG16.1 security breach?
@ 2024-06-12 21:37 Ron Johnson <[email protected]>
2024-06-12 22:08 ` Re: PG16.1 security breach? David G. Johnston <[email protected]>
2024-06-12 22:56 ` Re: PG16.1 security breach? Tom Lane <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Ron Johnson @ 2024-06-12 21:37 UTC (permalink / raw)
To: [email protected] <[email protected]>
On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston <
[email protected]> wrote:
> On Mon, Jun 10, 2024 at 2:21 AM Laurenz Albe <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG16.1 security breach?
2024-06-12 21:37 Re: PG16.1 security breach? Ron Johnson <[email protected]>
@ 2024-06-12 22:08 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2024-06-12 22:08 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: [email protected] <[email protected]>
On Wed, Jun 12, 2024 at 2:37 PM Ron Johnson <[email protected]> wrote:
> On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston <
> [email protected]> wrote:
>
>> On Mon, Jun 10, 2024 at 2:21 AM Laurenz Albe <[email protected]>
>> 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.
>
>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG16.1 security breach?
2024-06-12 21:37 Re: PG16.1 security breach? Ron Johnson <[email protected]>
@ 2024-06-12 22:56 ` Tom Lane <[email protected]>
2024-06-12 23:07 ` Re: PG16.1 security breach? David G. Johnston <[email protected]>
2024-06-13 11:59 ` Re: PG16.1 security breach? Joe Conway <[email protected]>
1 sibling, 2 replies; 5+ messages in thread
From: Tom Lane @ 2024-06-12 22:56 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: [email protected] <[email protected]>
Ron Johnson <[email protected]> writes:
> On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston <
> [email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG16.1 security breach?
2024-06-12 21:37 Re: PG16.1 security breach? Ron Johnson <[email protected]>
2024-06-12 22:56 ` Re: PG16.1 security breach? Tom Lane <[email protected]>
@ 2024-06-12 23:07 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2024-06-12 23:07 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Ron Johnson <[email protected]>; [email protected] <[email protected]>
On Wed, Jun 12, 2024 at 3:57 PM Tom Lane <[email protected]> wrote:
> Ron Johnson <[email protected]> writes:
> > On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston <
> > [email protected]> 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.
>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG16.1 security breach?
2024-06-12 21:37 Re: PG16.1 security breach? Ron Johnson <[email protected]>
2024-06-12 22:56 ` Re: PG16.1 security breach? Tom Lane <[email protected]>
@ 2024-06-13 11:59 ` Joe Conway <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Joe Conway @ 2024-06-13 11:59 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Ron Johnson <[email protected]>; +Cc: [email protected] <[email protected]>
On 6/12/24 18:56, Tom Lane wrote:
> Ron Johnson <[email protected]> writes:
>> On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston <
>> [email protected]> 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;
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<----------------------
> Repeat for each schema that you think might be publicly readable
> (which is only public by default).
indeed
> BTW, in PG 15 and up, the public schema is not writable by
> default, which attacks basically the same problem from a different
> direction.
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-06-13 11:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-12 21:37 Re: PG16.1 security breach? Ron Johnson <[email protected]>
2024-06-12 22:08 ` David G. Johnston <[email protected]>
2024-06-12 22:56 ` Tom Lane <[email protected]>
2024-06-12 23:07 ` David G. Johnston <[email protected]>
2024-06-13 11:59 ` Joe Conway <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox