Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

Hi David, Laurenz,

> Other people who use views with "security_barrier = on"

CMIIW, but wasn't security_barrier a mechanism to do RLS before actual RLS
(policies) were introduced? So in a way aren't they superseded by RLS?

> I’d be more inclined to change this incompatibility than try to affect
action at a distance with a database setting.

Could we instead have a shortcut for view creation like `CREATE SECURE
VIEW` (would be the same as WITH (security_invoker = true)`) ? This at
least makes it harder to forget specifying the option and also denotes that
by default views are insecure (since they're most likely created by
security_definer=superuser)

Best regards,
Steve

On Tue, 27 Jan 2026 at 14:12, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

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