postgres.email
❮

Add SECURITY_INVOKER_VIEWS option to CREATE DATABASE

  • Jump to comment-1
    Steve Chavez<steve@supabase.io>
    Jan 27, 2026, 4:37 PM UTC
    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 .. SECURITYINVOKERVIEWS
    <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
    View in PostgreSQL Archives →
    • Jump to comment-1
      David G. Johnston<david.g.johnston@gmail.com>
      Jan 27, 2026, 6:02 PM UTC
      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.
      View in PostgreSQL Archives →
    • Jump to comment-1
      Laurenz Albe<laurenz.albe@cybertec.at>
      Jan 27, 2026, 5:21 PM UTC
      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 .. SECURITYINVOKERVIEWS <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 SECURITYINVOKERVIEWS TRUE, it could happen that the resulting schema
      causes unexpected "permission denied" errors in the application.
      Yours,
      Laurenz Albe
      View in PostgreSQL Archives →
      • Jump to comment-1
        Steve Chavez<steve@supabase.io>
        Jan 27, 2026, 5:46 PM UTC
        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 SECURITYINVOKERVIEWS 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 SECURITYINVOKERVIEWS and then all queries start failing.
        So one enhancement could be to only allow SECURITYINVOKERVIEWS 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 .. SECURITYINVOKERVIEWS
        <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 SECURITYINVOKERVIEWS TRUE, it could happen that
        the resulting schema
        causes unexpected "permission denied" errors in the application.

        Yours,
        Laurenz Albe
        View in PostgreSQL Archives →
        • Jump to comment-1
          Laurenz Albe<laurenz.albe@cybertec.at>
          Jan 27, 2026, 7:12 PM UTC
          On Tue, 2026-01-27 at 12:46 -0500, Steve Chavez wrote:
          If somebody runs a DDL script in
          a database created with SECURITYINVOKERVIEWS 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 SECURITYINVOKERVIEWS and then all queries start failing.
          So one enhancement could be to only allow SECURITYINVOKERVIEWS at creation time, like with the LOCALE option.
          I am slightly confused. I had understood your proposal to be that SECURITYINVOKERVIEWS 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
          View in PostgreSQL Archives →
          • Jump to comment-1
            Steve Chavez<steve@supabase.io>
            Jan 28, 2026, 7:19 PM UTC
            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 SECURITYINVOKERVIEWS 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 SECURITYINVOKERVIEWS and then all queries start failing.
            So one enhancement could be to only allow SECURITYINVOKERVIEWS at
            creation time, like with the LOCALE option.

            I am slightly confused. I had understood your proposal to be that
            SECURITYINVOKERVIEWS 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
            View in PostgreSQL Archives →
            • Jump to comment-1
              Laurenz Albe<laurenz.albe@cybertec.at>
              Jan 28, 2026, 8:28 PM UTC
              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
              View in PostgreSQL Archives →
              • Jump to comment-1
                Steve Chavez<steve@supabase.io>
                Jan 28, 2026, 8:44 PM UTC
                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
                View in PostgreSQL Archives →
                • Jump to comment-1
                  Laurenz Albe<laurenz.albe@cybertec.at>
                  Jan 29, 2026, 6:25 AM UTC
                  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
                  View in PostgreSQL Archives →
            • Jump to comment-1
              David G. Johnston<david.g.johnston@gmail.com>
              Jan 28, 2026, 7:40 PM UTC
              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.
              View in PostgreSQL Archives →