Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

Hi Laurenz,

> First of all, such a setting won't guarantee that all views get created
with "security_invoker"
set - the user is still free to explicitly set "security_invoker = off"

Yes, but that would be a conscious decision. The idea is to provide a sane
default.

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

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.

Best regards,
Steve Chavez

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

> On Tue, 2026-01-27 at 11:36 -0500, Steve Chavez wrote:
> > Currently views are not secure by default since they bypass RLS.
> PostgreSQL 15 introduced the
> > `WITH (security_invoker = true)` option for this but it's easy to miss
> on every new view created.
> >
> > It's also inconsistent with functions, which default to SECURITY INVOKER.
> >
> > I propose adding an option: `CREATE DATABASE .. SECURITY_INVOKER_VIEWS
> <bool>` (false by default
> > to maintain backwards compat), so a database will have newly created
> views as SECURITY INVOKER.
> >
> > Let me know what you think.
>
> I don't like it.
>
> First of all, such a setting won't guarantee that all views get created
> with "security_invoker"
> set - the user is still free to explicitly set "security_invoker = off".
>
> Second, and more importantly, that is a setting that changes the behavior
> of SQL statements,
> which is something that the project has learned to fear. It is
> problematic if the same SQL
> statement has different semantics with different settings. 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.
>
> Yours,
> Laurenz Albe
>