Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

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

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.albe@cybertec.at> wrote:

On Tuesday, January 27, 2026, Steve Chavez <steve@supabase.io> wrote:

Hello hackers,

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’d be more inclined to change this incompatibility than try to affect
action at a distance with a database setting. But suspect the status-quo
is likely to prevail. Maybe we need a view of views that reference RLS
relations that aren’t security_invoker? Add something to the docs? If one
knows enough to enable a database setting they can institute different less
problematic solutions as well. Maybe we provide an event trigger example.

David J.

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

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.albe@cybertec.at> wrote:

On Wed, Jan 28, 2026 at 12:19 PM Steve Chavez <steve@supabase.io> wrote:

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)

Please don't top-post.

Inventing alternative syntax with the same fundamental issue, just an
arguably different failure threshold, is unappealing.

David J.

On Wed, 2026-01-28 at 14:19 -0500, Steve Chavez wrote:

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 wouldn't say so.

Creating a view that allows a user to see only certain columns of a table
is something quite different from restricting the rows a user can operate on.

Yours,
Laurenz Albe

But that is a property of just regular views not necessarily
security_barrier right? i.e. "to be able to hide certain columns".

Please don't top-post.

My bad, to be honest I don't understand how I can reply to different
paragraphs in a more structured way.
Are there guidelines/examples on the pg docs about this rule?

Best regards,
Steve

On Wed, 28 Jan 2026 at 15:28, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Wed, 2026-01-28 at 15:43 -0500, Steve Chavez wrote:

But that is a property of just regular views not necessarily security_barrier right? i.e. "to be able to hide certain columns".

Right, but without "security_barries = on" it may be that a sneaky attacker
can subvert the security. With that setting, only LEAKPROOF functions and
operators are can be pushed into the view definition.

But we are getting off-topic. My point is that your proposed database setting
would change the behavior of such a view so that it wouldn't work any more.

Yours,
Laurenz Albe