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
