Thread: PG16.1 security breach?
I am running the following on Postgres 16.1 in database "postgres" as a superuser:
revoke create on schema public from public;
revoke create on database postgres from public;
create schema if not exists oiz;
revoke create on schema oiz from public;
grant usage on schema oiz to public;
create or replace function oiz.f_set_dbowner (p_dbowner text, p_dbname text)
returns void
language plpgsql
security definer
as $$
…
when I create a new role in following:
create role testuser with password 'testuser' login;
postgres=# \du testuser
List of roles
Role name | Attributes
-----------+------------
testuser |
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!
postgres=> \conninfo
You are connected to database "postgres" as user "testuser" on host "cmpgdb-pg-eng900.eng.cmp.szh.loc" (address "10.199.112.56") at port "5017".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
postgres=> select oiz.f_set_dbowner ('testuser','database1');
f_set_dbowner
---------------
(1 row)
The role is also able to execute the function even I revoke any execute privilege explicitly:
revoke execute on function oiz.f_set_dbowner (p_dbowner text, p_dbname text) from testuser;
There are also no default privileges on the schema:
postgres=# \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)
postgres=> \df+ oiz.f_set_dbowner
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Internal name | Description
--------+---------------+------------------+-------------------------------+------+------------+----------+----------+----------+---------------------+----------+---------------+-------------
oiz | f_set_dbowner | void | p_dbowner text, p_dbname text | func | volatile | unsafe | postgres | definer | =X/postgres +| plpgsql | |
| | | | | | | | | postgres=X/postgres | | |
(1 row)
postgres=> \l postgres
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
postgres | postgres | UTF8 | libc | de_CH.utf-8 | de_CH.utf-8 | | | =Tc/postgres +
| | | | | | | | postgres=CTc/postgres
(1 row)
What I am missing? Is there something new with PG 16? Is it a bug?
Cheers, Markus
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
> -----Ursprüngliche Nachricht----- > Von: Joe Conway <mail@joeconway.com> > Gesendet: Freitag, 7. Juni 2024 15:22 > An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>; pgsql- > general@lists.postgresql.org > 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
On Friday, June 7, 2024, Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch> wrote:
grant usage on schema oiz to public;
The role is also able to execute the function even I revoke any execute privilege explicitly:
revoke execute on function oiz.f_set_dbowner (p_dbowner text, p_dbname text) from testuser;
You never typed “grant execute … to testuser” nor setup a default privilege for them, so there is nothing there to revoke. As was noted, the combination of your explicit usage grant, and the default execute grant, given to the public pseudo-role, enables this.
There are also no default privileges on the schema:
You explicitly granted usage to the pseudo-role public…
It is doubtful we’d add a global setting to control this. And it’s a hard sell changing such a pervasive default. As most functions are security invoker, and many are side-effect free, the default does have merit. If your function is neither undoing the default is something that should probably be done.
I could maybe see adding a new “revoke all default privileges from public” command.
David J.
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
On 6/7/24 06:54, Zwettler Markus (OIZ) wrote: >> -----Ursprüngliche Nachricht----- >> Von: Joe Conway <mail@joeconway.com> >> Gesendet: Freitag, 7. Juni 2024 15:22 >> An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>; pgsql- >> general@lists.postgresql.org >> 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 adrian.klaver@aklaver.com
On Friday, June 7, 2024, Laurenz Albe <laurenz.albe@cybertec.at> 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.
On Fri, 2024-06-07 at 07:42 -0700, David G. Johnston wrote: > On Friday, June 7, 2024, Laurenz Albe <laurenz.albe@cybertec.at> 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
On Mon, Jun 10, 2024 at 2:21 AM Laurenz Albe <laurenz.albe@cybertec.at> 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.
On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Jun 10, 2024 at 2:21 AM Laurenz Albe <laurenz.albe@cybertec.at> 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.
On Wed, Jun 12, 2024 at 2:37 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Wed, Jun 12, 2024 at 4:36 PM David G. Johnston <david.g.johnston@gmail.com> wrote:On Mon, Jun 10, 2024 at 2:21 AM Laurenz Albe <laurenz.albe@cybertec.at> 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 systemThat 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.
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
On Wed, Jun 12, 2024 at 3:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
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 forevery 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.
On 6/12/24 18:56, Tom Lane wrote: > 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; 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