Subject: PG16.1 security breach?

From: "Zwettler Markus (OIZ)" <[email-masked]>
Date: Fri, 7 Jun 2024 11:04:08 +0000

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: "David G. Johnston" <[email-masked]>
Date: Fri, 7 Jun 2024 07:16:04 -0700

> revoke execute on function oiz.f_set_dbowner (p_dbowner text, p_dbname
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.