Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

On Tue, 2026-01-27 at 12:46 -0500, Steve Chavez wrote:
> > If somebody runs a DDL script in
> > a database created with SECURITY_INVOKER_VIEWS TRUE, it could happen that the resulting schema
> > causes unexpected "permission denied" errors in the application.
>
> IMO that's much better than leaking information by default, which views do with security_definer.

That's what you think. Other people who use views with "security_barrier = on" to allow
unprivileged users a restricted view on confidential data would be very unhappy indeed if
their CREATE VIEW statements would suddenly create views that give the users an unexpected
"permisson denied".

You might argue that they should set "security_invoker = off" explicitly if they want to
be sure that that cannot happen, but then I'm going to answer that the same applies to your
use case.

> One problem is that it could indeed be confusing if an ALTER DATABASE modified SECURITY_INVOKER_VIEWS and then all queries start failing.
> So one enhancement could be to only allow SECURITY_INVOKER_VIEWS at creation time, like with the LOCALE option.

I am slightly confused. I had understood your proposal to be that SECURITY_INVOKER_VIEWS only
applies at CREATE VIEW time anyway. If you want the setting to override any "security_invoker"
setting on existing views, I like that even less, because it would prevent people from
explicitly opting out (unless you propose to change "security_invoker" into a ternary setting
with values "on", "off" and "unset").

Yours,
Laurenz Albe