public inbox for [email protected]
help / color / mirror / Atom feedPG16.1 security breach?
2+ messages / 2 participants
[nested] [flat]
* PG16.1 security breach?
@ 2024-06-07 11:04 Zwettler Markus (OIZ) <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Zwettler Markus (OIZ) @ 2024-06-07 11:04 UTC (permalink / raw)
To: [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PG16.1 security breach?
@ 2024-06-07 14:16 David G. Johnston <[email protected]>
parent: Zwettler Markus (OIZ) <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2024-06-07 14:16 UTC (permalink / raw)
To: Zwettler Markus (OIZ) <[email protected]>; +Cc: [email protected] <[email protected]>
On Friday, June 7, 2024, Zwettler Markus (OIZ) <[email protected]>
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-06-07 14:16 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-07 11:04 PG16.1 security breach? Zwettler Markus (OIZ) <[email protected]>
2024-06-07 14:16 ` David G. Johnston <[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