PG16.1 security breach?

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-general by date

  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