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