supabase：用于列级安全的解决方案

Question

目前，我正在使用supabase数据库。我面对的大障碍之一是列级安全性，似乎比RLS复杂得多。

假设我有一个称为 is_banned 的列，这是可查看但不可编辑的。但是，其余的列应该既可以编辑又可见。

我真正能想到的唯一解决方案是将其分成两个表并在“敏感信息”表上具有RLS - 但是为每个表创建一个私人表似乎是不必要的。

还有其他解决方案吗？

Answer 1

我必须自己处理这个问题。我目前可以使用视图来解决它，但宁愿将来选择RLS策略，触发器或特权功能（截至目前未经测试）。我在下面分享了我研究的注释。

---

列级安全性（“ CLS”）是指根据某些条件选择性地禁止列值更新，插入甚至选择。有几种替代解决方案（摘要），每种都有优势和缺点。下面对它们进行了详细讨论。

选项1：RLS策略

（到目前为止我最喜欢的选项，但我尚未在实践中使用它

。 RLS）策略检索旧行，并比较您受保护的列的字段值是否会从旧行变为新行。解决方案的候选者已发布为堆栈溢出答案，但这仍然必须使其成为通用功能。

乍一看，这似乎比触发因素更好：它具有其优势，此外，Supabase无论如何都会促进使用RLS策略来访问控制，并且对RLS的UI支持要比对触发器的支持更好。因此，它将通过降低复杂性来提高数据库的一致性和可维护性。

但是，supabase RLS编辑器不能用于复杂的RLS策略（“ noreferrer”>问题报告由于解决方法，应将所有RLS代码包装到一个或嵌套的函数调用中，或者至少不超过一行代码。更好的是，在Supabase之外的版本控制下，将SQL源代码维护，并在要更改RLS策略，表格，功能等时，将其复制到Supabase SQL编辑器中。

选项2：触发器

请参见在这里，我的原始说明 a href =“ https://github.com/orgs/supabase/discussions/656#discussioncomment-5594653” rel =“ noreferrer”>改进的说明 github用户Christophemarois。

优点：

• 不添加另一个表或视图，因此数据库结构由数据确定，而不是通过许可系统怪癖来确定。

• 不需要更改默认的supabase权限或表格到示意分配。

• 结合了RLS策略和列级权限的功能。

缺点：

• 触发器在supabase UI中尚未得到很好的支持：只能更改触发器状态，但只能在UI中显示或编辑，只能在postgresql控制台中显示或编辑。实际上，这并不是什么问题，因为无论如何，您都必须直接与PostgreSQL数据库一起工作。

  。

• 它需要了解PGSQL或其他编程语言……对于某些人来说，编程是我们想要使用Supabase避免的。但是，该解决方案使用一个接收到白名单列的抽象函数（“允许更改”），因此无需进行实际编程，只需部署一些可重复使用的代码即可。

选项3：特权功能

“您可以用安全定义器隐藏函数后面的表。该表本身将无法提供更新访问权限，而是用户只能通过函数更新表。” （ source ）

，在该功能中，您可以按照自己喜欢的任何方式确定列级访问权限。模式中的任何此类功能 public 将通过API自动获得：

“写postgresql sql functions […]，并通过 supabase.rpc（'function_name'，{param1：'value'}）; 呼叫。 （ source ）。

但是，问题在于，API不再具有“所有内容都在表中可用”的统一结构。

选项4：特定于用户的视图

请参阅指令。更多说明：

“您可以创建一个视图以仅显示所需的列，请确保使用语句忽略语句，因为它忽略了RLS（通常是），然后使用RLS阻止原始表。 “ （ source> source ）

（ /Github.com/supabase/supabase/discussions/8663#discussioncomment-3484271“ rel =“ noreferrer”>由supabase维护者推荐。总的来说，RLS政策和触发器似乎是可取的。

要使此解决方案安全，您必须使用选项 security_barrier = on （详细信息），这会严重影响视图性能。解决的唯一方法是不使用 where 子句，而是通过 security_invoker = on 重新使用基础表的RLS策略。这需要将基础表移至API不暴露的自定义数据库方案（见下文）。

优点：

• 简单。视图就像桌子一样，每个人都知道PostgreSQL表 - 与触发器或（复杂的）RLS策略相比。

• 您会看到您编辑的内容。用户（或他们的应用程序）可以在表中看到记录，不必担心由于RLS策略是否可以编辑。无论用户可以看到什么，他们都可以编辑。

• ❓可根据需要扩展。 （仍然不确定。） 仅在视图中提供某个某些用户的编辑列。要找到正确的列，有时需要更多上下文。没问题：在API访问时间时，请再次根据需要加入底层基础表的视图和列。只有替代主键列 id 才需要始终包含在视图中。这不是一个问题：如果用户试图编辑它，则只有在使用新值时才能成功，在这种情况下，有效地创建了新的记录，无论如何都可能允许用户执行此操作。 （待确认，仍然可以使用适当的访问保护更新。）

（可以确认，

• API将以适当的数据库设计中的形式公开数据。通过暴露其他视图，API变得不必要地复杂。

• 无法真正重复使用底层表的RLS策略。在创建视图时使用 security_invoker = on 在创建视图时（详细信息）。但是，在执行此操作时，可以通过视图更新记录的相同用户也可以在基础表中更新该记录，从而规避使用该视图的列访问限制。唯一的方法是将基础表移至API未暴露的自定义数据库方案。这是可能的，但增加了结构上的复杂性。

• 需要更改默认视图权限。由于这些简单视图，因此它们在PostgreSQL中是“可更新”视图。加上supabase schema public中默认的表级 /视图级权限，这意味着所有用户，甚至匿名的用户都可以从这些视图中删除记录，从而导致填充表中的记录删除。< / p>。< / p>

  要解决此问题，必须从视图中删除插入物并删除特权。这是对默认的supabase权限的更改，理想情况下是不需要的。

  有一个替代解决方案，但这不是很实际：您可以使用 security_invoker = on 创建视图来重复使用底层表的RLS策略。然后使用这些RLS策略来防止记录删除。但是，他们必须允许选择和更新；因此，除非将底层表移至API未曝光的模式，否则它将允许用户规避创建视图的列级安全性。

• 没有限制某些 值 的好方法。这是因为视图不能具有自己的RLS政策。有几种解决此问题的方法：

  • 可能最佳的工作方式是构建表，以便允许对列的写入访问权限的用户使用该列中的每个值。例如，代替列角色（用户，admin）和状态（应用，批准，不赞成），将有可无效的布尔列 user_application ， admin_application ， user_status ， admin_status 。

  • 对于复杂情况，另一个选项是将基础表移至不访问API的自定义模式（同时仍授予所有Supabase角色的使用和权限；
    

  • 对于复杂情况，另一个选项是在视图或底层表上使用触发器。

  
  

  
  

选项5：列级访问权限

“您只能提供对表的列的一个子集：Grant Update（COL1，COL2）。 >）“（ source ）

据报道，维护所有这些访问权利是一种麻烦。在supabase中，不可能向不同的身份验证的用户提供不同的权限，因为所有这些用户都使用相同的角色 autheratiencatienated 访问数据库。但是，在最高级别上工作时，您可以在这里有不同的选择。

选项6：与视图相比，表分裂

，这将主表分为多个部分。使用RLS策略，定义了谁可以用每个部分表来做什么；而且，与您只能在某个子句中部分模拟RLS策略的视图不同，RLS策略也可以用于限制用户可以用于列的值。要将它们一起使用，必须将它们加入请求。

将桌子分成两个时还可以。但是有时分裂几乎是“每列的一个表”，例如，对于每列角色一个列的许可管理表。这很糟糕，因为它“原子化”了数据，而不是以适当的正常形式保持数据，这意味着数据甚至无法以舒适的方式访问。可以通过将分开表再次组合并提供对这些底层表的写入访问的视图来解决。但是，还有很多表格。这是“丑陋的”。

Answer 2

在PostgreSQL中，您可以通过 Grant 和/或 devoke 语句指定列级权限。

这里棘手的部分是，这些权限是针对PostgreSQL用户/角色而不是针对您的应用程序用户设置的。因此，您需要确保对Supabase使用来执行客户端请求的所有用户设置权限。据我所知，supabase使用 anon 和认证 postgresql角色执行请求，但是，我没有官方文档其他任何人。

You can read more about how to utilize this technique

Answer 3

我花了几天的时间调查supabase并咨询了几个不会导致最佳解决方案的线程，直到我发现一个可以很好地适合RLS（行级别安全）安全策略的线程。

我的表报价我需要限制更新以下字段： id，likes，user_id

我的适配器 so

CREATE OR REPLACE FUNCTION is_restricted_cls(
    _id BIGINT,
    _restricted_field_b BIGINT,
    _restricted_field_c UUID
) RETURNS BOOLEAN AS
$
WITH original_row AS (
    SELECT likes, user_id
    FROM quotes
    WHERE quotes.id = _id
)
SELECT (
    (SELECT likes FROM original_row) IS NOT DISTINCT FROM _restricted_field_b AND
    (SELECT user_id FROM original_row) IS NOT DISTINCT FROM _restricted_field_c
)
$ LANGUAGE SQL SECURITY DEFINER;

进入 oringal_row 将您的字段添加到 select> select（）所有比较

将RLS策略分配到表

在本节中更新字段的安全策略的示例在使用和检查中，

((auth.uid() = user_id) AND is_restricted_cls(id, likes, user_id))

您可以根据您的要求修改列名和比较条件来调整IS_RENTERDIC_CLS函数以适合您的特定目的。