|
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 |
