PG16.1 security breach? - Mailing list pgsql-general

From Zwettler Markus (OIZ)
Subject PG16.1 security breach?
Date
Msg-id GV0P278MB00996776669F54A7EADB64688BFB2@GV0P278MB0099.CHEP278.PROD.OUTLOOK.COM
Whole thread Raw
Responses Re: PG16.1 security breach?
Re: PG16.1 security breach?
List 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

 

 

 

 

 

pgsql-general by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: Oracle to Postgres - Transform Hash Partition
Next
From: Ron Johnson
Date:
Subject: Re: PG 14 pg_basebackup accepts --compress=server-zst option