public inbox for [email protected]
help / color / mirror / Atom feedRe: PG16.1 security breach?
7+ messages / 5 participants
[nested] [flat]
* Re: PG16.1 security breach?
@ 2024-06-07 13:22 Joe Conway <[email protected]>
2024-06-07 13:54 ` AW: [Extern] Re: PG16.1 security breach? Zwettler Markus (OIZ) <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Joe Conway @ 2024-06-07 13:22 UTC (permalink / raw)
To: Zwettler Markus (OIZ) <[email protected]>; [email protected] <[email protected]>
On 6/7/24 07:04, Zwettler Markus (OIZ) wrote:
> I am running the following on Postgres 16.1 in database "postgres" as a
> superuser:
<snip>
> create or replace function oiz.f_set_dbowner (p_dbowner text, p_dbname text)
<snip>
> create role testuser with password 'testuser' login;
<snip>
> than this new role is able to execute the function oiz.f_set_dbowner
> immediately even I did not grant execute on this function to this role!
See:
https://www.postgresql.org/docs/current/sql-createfunction.html
In particular, this part:
8<------------------------
Another point to keep in mind is that by default, execute privilege is
granted to PUBLIC for newly created functions (see Section 5.7 for more
information). Frequently you will wish to restrict use of a security
definer function to only some users. To do that, you must revoke the
default PUBLIC privileges and then grant execute privilege selectively.
To avoid having a window where the new function is accessible to all,
create it and set the privileges within a single transaction. For example:
8<------------------------
HTH,
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* AW: [Extern] Re: PG16.1 security breach?
2024-06-07 13:22 Re: PG16.1 security breach? Joe Conway <[email protected]>
@ 2024-06-07 13:54 ` Zwettler Markus (OIZ) <[email protected]>
2024-06-07 14:32 ` Re: AW: [Extern] Re: PG16.1 security breach? Laurenz Albe <[email protected]>
2024-06-07 14:35 ` Re: AW: [Extern] Re: PG16.1 security breach? Adrian Klaver <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Zwettler Markus (OIZ) @ 2024-06-07 13:54 UTC (permalink / raw)
To: Joe Conway <[email protected]>; [email protected] <[email protected]>
> -----Ursprüngliche Nachricht-----
> Von: Joe Conway <[email protected]>
> Gesendet: Freitag, 7. Juni 2024 15:22
> An: Zwettler Markus (OIZ) <[email protected]>; pgsql-
> [email protected]
> Betreff: [Extern] Re: PG16.1 security breach?
>
> On 6/7/24 07:04, Zwettler Markus (OIZ) wrote:
> > I am running the following on Postgres 16.1 in database "postgres" as
> > a
> > superuser:
>
> <snip>
>
> > create or replace function oiz.f_set_dbowner (p_dbowner text, p_dbname
> > text)
>
> <snip>
>
> > create role testuser with password 'testuser' login;
>
> <snip>
>
> > than this new role is able to execute the function oiz.f_set_dbowner
> > immediately even I did not grant execute on this function to this role!
>
> See:
> https://www.postgresql.org/docs/current/sql-createfunction.html
>
> In particular, this part:
> 8<------------------------
> Another point to keep in mind is that by default, execute privilege is granted to
> PUBLIC for newly created functions (see Section 5.7 for more information).
> Frequently you will wish to restrict use of a security definer function to only some
> users. To do that, you must revoke the default PUBLIC privileges and then grant
> execute privilege selectively.
> To avoid having a window where the new function is accessible to all, create it and
> set the privileges within a single transaction. For example:
> 8<------------------------
>
> HTH,
>
> --
> Joe Conway
> PostgreSQL Contributors Team
> RDS Open Source Databases
> Amazon Web Services: https://aws.amazon.com
>
> --- Externe Email: Vorsicht mit Anhängen, Links oder dem Preisgeben von
> Informationen ---
Argh. No! What a bad habit!
Might be good idea for an enhancement request to create a global parameter to disable this habit.
Thanks Markus
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AW: [Extern] Re: PG16.1 security breach?
2024-06-07 13:22 Re: PG16.1 security breach? Joe Conway <[email protected]>
2024-06-07 13:54 ` AW: [Extern] Re: PG16.1 security breach? Zwettler Markus (OIZ) <[email protected]>
@ 2024-06-07 14:32 ` Laurenz Albe <[email protected]>
2024-06-07 14:42 ` PG16.1 security breach? David G. Johnston <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2024-06-07 14:32 UTC (permalink / raw)
To: Zwettler Markus (OIZ) <[email protected]>; Joe Conway <[email protected]>; [email protected] <[email protected]>
On Fri, 2024-06-07 at 13:54 +0000, Zwettler Markus (OIZ) wrote:
> > Another point to keep in mind is that by default, execute privilege is granted to
> > PUBLIC for newly created functions (see Section 5.7 for more information).
>
> Argh. No! What a bad habit!
>
> Might be good idea for an enhancement request to create a global parameter to disable this habit.
I don't see the problem, since the default execution mode for functions is
SECURITY INVOKER.
But you can easily change that:
ALTER DEFAULT PRIVILEGES FOR ROLE function_creator REVOKE EXECUTE ON FUNCTION FROM PUBLIC;
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* PG16.1 security breach?
2024-06-07 13:22 Re: PG16.1 security breach? Joe Conway <[email protected]>
2024-06-07 13:54 ` AW: [Extern] Re: PG16.1 security breach? Zwettler Markus (OIZ) <[email protected]>
2024-06-07 14:32 ` Re: AW: [Extern] Re: PG16.1 security breach? Laurenz Albe <[email protected]>
@ 2024-06-07 14:42 ` David G. Johnston <[email protected]>
2024-06-10 09:21 ` Re: PG16.1 security breach? Laurenz Albe <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2024-06-07 14:42 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Zwettler Markus (OIZ) <[email protected]>; Joe Conway <[email protected]>; [email protected] <[email protected]>
On Friday, June 7, 2024, Laurenz Albe <[email protected]> wrote:
> On Fri, 2024-06-07 at 13:54 +0000, Zwettler Markus (OIZ) wrote:
> > > Another point to keep in mind is that by default, execute privilege is
> granted to
> > > PUBLIC for newly created functions (see Section 5.7 for more
> information).
> >
> > Argh. No! What a bad habit!
> >
> > Might be good idea for an enhancement request to create a global
> parameter to disable this habit.
>
> I don't see the problem, since the default execution mode for functions is
> SECURITY INVOKER.
>
> But you can easily change that:
>
> ALTER DEFAULT PRIVILEGES FOR ROLE function_creator REVOKE EXECUTE ON
> FUNCTION FROM PUBLIC;
>
You named function_creator here when in this example the role creating the
new object is postgres. 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?
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PG16.1 security breach?
2024-06-07 13:22 Re: PG16.1 security breach? Joe Conway <[email protected]>
2024-06-07 13:54 ` AW: [Extern] Re: PG16.1 security breach? Zwettler Markus (OIZ) <[email protected]>
2024-06-07 14:32 ` Re: AW: [Extern] Re: PG16.1 security breach? Laurenz Albe <[email protected]>
2024-06-07 14:42 ` PG16.1 security breach? David G. Johnston <[email protected]>
@ 2024-06-10 09:21 ` Laurenz Albe <[email protected]>
2024-06-12 20:35 ` Re: PG16.1 security breach? David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2024-06-10 09:21 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Zwettler Markus (OIZ) <[email protected]>; Joe Conway <[email protected]>; [email protected] <[email protected]>
On Fri, 2024-06-07 at 07:42 -0700, David G. Johnston wrote:
> On Friday, June 7, 2024, Laurenz Albe <[email protected]> wrote:
> > On Fri, 2024-06-07 at 13:54 +0000, Zwettler Markus (OIZ) wrote:
> > > > Another point to keep in mind is that by default, execute privilege is granted to
> > > > PUBLIC for newly created functions (see Section 5.7 for more information).
> > >
> > > Argh. No! What a bad habit!
> > >
> > > Might be good idea for an enhancement request to create a global parameter to disable this habit.
> >
> > I don't see the problem, since the default execution mode for functions is
> > SECURITY INVOKER.
> >
> > But you can easily change that:
> >
> > ALTER DEFAULT PRIVILEGES FOR ROLE function_creator REVOKE EXECUTE ON FUNCTION FROM PUBLIC;
>
> You named function_creator here when in this example the role creating the new object is postgres.
Then use "postgres" rather than "function_creator".
An ALTER DEFAULT PRIVILEGES statement always only changes default privileges for objects
created by a certain user.
> 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.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PG16.1 security breach?
2024-06-07 13:22 Re: PG16.1 security breach? Joe Conway <[email protected]>
2024-06-07 13:54 ` AW: [Extern] Re: PG16.1 security breach? Zwettler Markus (OIZ) <[email protected]>
2024-06-07 14:32 ` Re: AW: [Extern] Re: PG16.1 security breach? Laurenz Albe <[email protected]>
2024-06-07 14:42 ` PG16.1 security breach? David G. Johnston <[email protected]>
2024-06-10 09:21 ` Re: PG16.1 security breach? Laurenz Albe <[email protected]>
@ 2024-06-12 20:35 ` David G. Johnston <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2024-06-12 20:35 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Zwettler Markus (OIZ) <[email protected]>; Joe Conway <[email protected]>; [email protected] <[email protected]>
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 as well as any roles that directly have
create permission on a schema and also those that inherit a create
permission on a schema. Lastly, any new roles created in the future with
direct or indirect create permission on a schema must also be altered. In
other words, the first time a role creates a routine the default privileges
involved with that creation will including granting execute to public,
unless said default privileges have already been revoked.
Maybe generalized to any of the default privileges. I find the existing
wording to gloss over the fact that one cannot just decide up front they
want to not allow these default privileges to public once on a system-wide
basis but must continually maintain the default privileges as new roles are
added that are allowed to create different objects, directly or otherwise.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: AW: [Extern] Re: PG16.1 security breach?
2024-06-07 13:22 Re: PG16.1 security breach? Joe Conway <[email protected]>
2024-06-07 13:54 ` AW: [Extern] Re: PG16.1 security breach? Zwettler Markus (OIZ) <[email protected]>
@ 2024-06-07 14:35 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Adrian Klaver @ 2024-06-07 14:35 UTC (permalink / raw)
To: Zwettler Markus (OIZ) <[email protected]>; Joe Conway <[email protected]>; [email protected] <[email protected]>
On 6/7/24 06:54, Zwettler Markus (OIZ) wrote:
>> -----Ursprüngliche Nachricht-----
>> Von: Joe Conway <[email protected]>
>> Gesendet: Freitag, 7. Juni 2024 15:22
>> An: Zwettler Markus (OIZ) <[email protected]>; pgsql-
>> [email protected]
>> Betreff: [Extern] Re: PG16.1 security breach?
>>
>> On 6/7/24 07:04, Zwettler Markus (OIZ) wrote:
>
> Argh. No! What a bad habit!
>
> Might be good idea for an enhancement request to create a global parameter to disable this habit.
Read this
https://www.postgresql.org/docs/current/ddl-priv.html
through several times, it will make things clearer. In particular the
part that starts "PostgreSQL grants privileges on some types of objects
to PUBLIC by default when the objects are created. ..."
>
> Thanks Markus
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-06-12 20:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-07 13:22 Re: PG16.1 security breach? Joe Conway <[email protected]>
2024-06-07 13:54 ` AW: [Extern] Re: PG16.1 security breach? Zwettler Markus (OIZ) <[email protected]>
2024-06-07 14:32 ` Re: AW: [Extern] Re: PG16.1 security breach? Laurenz Albe <[email protected]>
2024-06-07 14:42 ` David G. Johnston <[email protected]>
2024-06-10 09:21 ` Laurenz Albe <[email protected]>
2024-06-12 20:35 ` David G. Johnston <[email protected]>
2024-06-07 14:35 ` Re: AW: [Extern] Re: PG16.1 security breach? Adrian Klaver <[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