On Friday, June 7, 2024, Zwettler Markus (OIZ) <
Markus.Zwettler@zuerich.ch> wrote:
grant usage on schema oiz to public;
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;
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.