| From: | "Zwettler Markus (OIZ)" <Markus(dot)Zwettler(at)zuerich(dot)ch> | 
|---|---|
| To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> | 
| Subject: | PG16.1 security breach? | 
| Date: | 2024-06-07 11:04:08 | 
| Message-ID: | GV0P278MB00996776669F54A7EADB64688BFB2@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Ron Johnson | 2024-06-07 12:04:33 | Re: PG 14 pg_basebackup accepts --compress=server-zst option | 
| Previous Message | Laurenz Albe | 2024-06-07 08:31:16 | Re: Oracle to Postgres - Transform Hash Partition |