Thread

1. Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

   Steve Chavez <steve@supabase.io> — 2026-01-27T16:36:51Z

   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 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.
   
   Best regards,
   Steve Chavez
   

2. Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-01-27T17:21:11Z

   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
   
   
   
   

3. Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

   Steve Chavez <steve@supabase.io> — 2026-01-27T17:46:41Z

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

4. Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

   David G. Johnston <david.g.johnston@gmail.com> — 2026-01-27T18:02:34Z

   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.
   

5. Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-01-27T19:12:34Z

   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
   
   
   
   

6. Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

   Steve Chavez <steve@supabase.io> — 2026-01-28T19:19:31Z

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

7. Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

   David G. Johnston <david.g.johnston@gmail.com> — 2026-01-28T19:39:18Z

   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.
   

8. Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-01-28T20:28:43Z

   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
   
   
   
   

9. Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

   Steve Chavez <steve@supabase.io> — 2026-01-28T20:43:54Z

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

10. Re: Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-01-29T06:25:10Z

    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