我必须自己处理这个问题。我目前用视图解决这个问题,但将来更愿意选择 RLS 策略、触发器或特权函数(截至目前尚未测试)。我在下面分享我对这个问题的研究笔记。
列级安全性(“CLS”)意味着根据某些条件有选择地禁止列值 UPDATE、INSERT 甚至 SELECT。有几种替代解决方案(summary https://dba.stackexchange.com/a/323514),各有利弊。下面详细讨论它们。
选项 1:RLS 政策
(到目前为止我最喜欢的选项,但我还没有在实践中使用它。)
在这里,对于防止更新的 CLS 策略,您将使用行级安全 (RLS) 策略来检索旧行并比较受保护列的字段值是否会从旧行更改为新行。此问题的候选解决方案已发布为堆栈溢出答案 https://stackoverflow.com/a/71167428,但这仍然需要做成通用函数。
乍一看,这似乎比触发器更好:它具有相同的优点,此外,Supabase 无论如何都提倡使用 RLS 策略进行访问控制,并且对 RLS 的 UI 支持比触发器好得多。因此,它将通过降低复杂性来提高数据库的一致性和可维护性。
但是,Supabase RLS 编辑器不能用于复杂的 RLS 策略(问题报告 https://github.com/supabase/supabase/issues/12443),因此作为一种解决方法,应该将所有 RLS 代码包装到单个或嵌套函数调用中,或者至少不超过一行代码。更好的是在 Supabase 外部的版本控制下维护 SQL 源代码,并在您想要更改 RLS 策略、表、函数等时将其复制并粘贴到 Supabase SQL 编辑器中。
选项 2:触发器
See here https://github.com/supabase/supabase/discussions/656#discussioncomment-4974429我原来的指示,然后改进的说明 https://github.com/orgs/supabase/discussions/656#discussioncomment-5594653作者:Github 用户 christophemarois。
优点:
缺点:
-
Supabase UI 中尚未很好地支持触发器:只能更改触发器状态,但无法在 UI 中显示或编辑,只能在 PostgreSQL 控制台中显示或编辑。实际上,这并不是什么大问题,因为对于任何现实项目,您都必须直接使用 PostgreSQL 数据库。
-
它需要 PGSQL 或其他编程语言的知识……对于某些人来说,我们希望使用 Supabase 避免编程。然而,该解决方案使用一个抽象函数来接收列到白名单(“允许更改”),因此不需要真正的编程,只需部署一些可重用的代码。
选项 3:特权函数
“您可以使用 SECURITY DEFINER 将表隐藏在 FUNCTION 后面。表本身不会提供 UPDATE 访问权限,用户只能通过 FUNCTION 更新表。” (source https://dba.stackexchange.com/a/323514)
在该函数中,您可以按照您喜欢的任何方式确定列级访问权限。模式中任何此类函数public
可通过 API 自动获取:
“编写 PostgreSQL SQL 函数 [...] 并通过以下方式调用它们supabase.rpc('function_name', {param1: 'value'});
." (source https://dev.to/davepar/going-serverless-with-supabase-103h).
但问题是,API 不再具有“所有内容都在表中可用”的统一结构。
选项 4:用户特定的视图
See the 指示 https://dba.stackexchange.com/a/301113。更多说明:
“您可以创建一个视图来仅显示您想要的列,请确保您使用WHERE
语句,因为它会忽略 RLS(通常),然后使用 RLS 来阻止原始表。" (source https://github.com/supabase/supabase/discussions/8663#discussioncomment-3484271)
该解决方案已由 Supabase 维护者推荐 https://github.com/supabase/supabase/discussions/8663#discussioncomment-3484271。不过,总的来说,RLS 策略和触发因素似乎更可取。
为了使这个解决方案安全,您必须使用选项security_barrier = on
(details https://www.postgresql.org/docs/current/rules-privileges.html),这会严重影响视图性能。解决这个问题的唯一方法是不使用WHERE
子句,而不是通过以下方式重新使用基表的 RLS 策略security_invoker = on
。这需要将基表移动到 API 未公开的自定义数据库方案(见下文)。
优点:
-
Simple.视图就像表一样,每个人都知道 PostgreSQL 表 - 与触发器或(复杂的)RLS 策略相反。
-
您会看到您编辑的内容。可以看到表中记录的用户(或其应用程序)不必担心由于 RLS 策略而导致这些记录是否可编辑。用户可以看到任何内容,他们都可以编辑。
-
❓ 可根据需要扩展。 (对此仍不确定。)视图中只能提供允许特定用户编辑的列。为了找到正确的列,有时需要更多上下文。没问题:在 API 访问时,根据需要再次连接底层基表中的视图和列。仅代理主键列id
需要始终包含在视图中;这不是问题:如果用户尝试编辑它,则只有在使用新值时才能成功,在这种情况下,实际上会创建一条新记录,无论如何,用户可能都可以这样做。(有待确认的是,仍然可以进行具有适当访问保护的更新。)
缺点:
-
使桌面空间变得混乱。理想情况下,API 将以正确的数据库设计中的形式公开数据。通过公开其他视图,API 会变得不必要的复杂。
-
无法真正复用底层表的RLS策略。通过使用来完成security_invoker = on
创建视图时(details https://github.com/supabase/supabase/discussions/901)。然而,在执行此操作时,可以通过视图更新记录的同一用户也可以更新基表中的该记录,从而规避使用视图的列访问限制。解决这个问题的唯一方法是将基表移动到 API 未公开的自定义数据库方案。这是可能的,但增加了更多的结构复杂性。
-
需要更改默认查看权限。由于这些是简单视图,因此它们是 PostgreSQL 中的“可更新”视图。与 Supabase 模式 public 中默认的表级/视图级权限一起,这意味着所有用户,甚至匿名用户,都可以从这些视图中删除记录,从而导致底层表中的记录被删除。
要解决此问题,必须从视图中删除 INSERT 和 DELETE 权限。这是对默认 Supabase 权限的更改,理想情况下是不必要的。
有一个替代解决方案,但它不是很实用:您可以使用以下命令创建视图security_invoker = on
重用底层表的RLS策略。然后使用这些 RLS 策略来防止记录删除。然而,他们必须允许 SELECT 和 UPDATE;因此,除非您将底层表移动到 API 未公开的架构,否则用户将可以绕过为其创建视图的列级安全性。
-
没有好的办法来限制某些特定的使用 values 在某个列中向某些用户显示。这是因为视图不能有自己的 RLS 策略。有几种方法可以解决这个问题:
-
解决这个问题的最佳方法可能是构建表,以便允许对列具有写访问权限的用户使用该列中的每个值。例如,代替列role
(用户、管理员)和status
(已申请、已批准、未批准),会有可为空的布尔列user_application
, admin_application
, user_status
, admin_status
.
-
对于复杂情况,另一个选择是将基础表移动到 API 不可访问的自定义模式(同时仍然向所有 Supabase 角色授予使用和权限;see https://stackoverflow.com/a/73282539),在该基础表上创建 RLS 策略,并通过以下方式在视图中重用它们:security_invoker = on
.
-
对于复杂情况,另一种选择是在视图或底层表上使用触发器。
选项 5:列级访问权限
“您只能提供对表的一部分列的 UPDATE 访问:GRANT UPDATE(col1, col2)。(details https://stackoverflow.com/a/3281750)" (source https://dba.stackexchange.com/a/323514)
据报道,维护所有这些访问权限很麻烦。在 Supabase 中,不可能向不同的经过身份验证的用户授予不同的权限,因为所有用户都使用相同的角色访问数据库authenticated
。不过,在 PostgREST 级别上工作时,您可以在此处有不同的选项。
选项 6:表拆分
与视图相比,这将主表分成多个部分。使用 RLS 策略,定义谁可以对每个部分表执行什么操作;而且,与只能在 WHERE 子句中部分模拟 RLS 策略的视图不同,RLS 策略还可以用于限制用户可以对列使用哪些值。要一起使用它们,必须将它们加入请求中。
将表格一分为二时非常好。但有时拆分几乎是“每列一张表”,例如每个角色一列的权限管理表。这很糟糕,因为它“原子化”了数据,而不是将其保持为适当的正常形式,这意味着管理员甚至无法以舒适的方式访问数据。这可以通过再次组合拆分表并提供对这些底层表的写入访问的视图来解决。但仍然有很多桌子需要处理。它很丑”。