Supabase需要列级安全性
#网络开发人员 #postgres #database #supabase

Postgres在安全方面很棒。与MySQL不同，Postgres具有行和列的内置安全措施。让我们看看：

行级安全性（RLS）

首先，您必须为每个表启用它。

使能够 

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

rls允许您为 crud 在单个桌子上进行操作或更明确的INSERT，SELECT，SELECT和DELETE创建SQL策略。

使用

  • USING关键字实际上将过滤表中的现有值。这对SELECT，DELETE和UPDATE语句都很好。您将在INSERT中没有之前的价值。

检查

  • WITH CHECK关键字实际上将过滤在表中的值。这对INSERT和UPDATE语句很好。使用UPDATE语句，您实际上有一个之前和之后的价值，因此您都需要。

选择 

CREATE POLICY "rls_posts_read_public"
  ON public.posts FOR SELECT
  USING (true);

插入 

CREATE POLICY "rls_posts_create_own"
  ON public.posts FOR INSERT
  WITH CHECK (auth.uid() = author);

删除 

CREATE POLICY "rls_posts_delete_own"
  ON public.posts FOR DELETE
  USING (auth.uid() = author);

更新 

CREATE POLICY "rls_posts_update_own"
  ON public.posts FOR UPDATE
  USING (auth.uid() = author)
  WITH CHECK (auth.uid() = author);

这些政策确实是过滤器。想象一下，在引擎盖下的Postgres会过滤您所要求的值。如果您要求的值仍然存在，则可以查看，更新或删除它们。然后，想象一下Postgres将过滤您尝试添加或更新的值。如果值在过滤后仍然存在，则Postgres将允许您添加或更新它们。请记住，随着更新，您必须过滤当前值和新值。

笔记

我喜欢用读取，写作，更新，删除，插入，创建，组，群体，管理员，公共，公共，限制，有限，临时>等的关键字命名这些策略像 rls _ + table_name_access_type ，但这只是我。我喜欢简单性和模式。您可以同样使用更多描述性句子。

列级安全性

列级安全性不存在于supabase中。我希望这样做。但是，您可以使用Postgres角色实现相似的结果。

创建角色

创建您想要的任何角色。

CREATE ROLE moderator;

授予

创建特权

-- GRANT operation (columns) ON table_name TO role
GRANT SELECT (id, title, author) ON public.posts TO moderator;
GRANT UPDATE (id, title, author) ON public.posts TO moderator;
...

撤销

删除特权

-- REVOKE operation ON table_name FROM role
REVOKE SELECT ON public.posts FROM moderator;

supabase具有一些值得注意的角色（请记住这可能会改变）：

  1. anon （公共）
  2. 身份验证
  3. 身份验证者
  4. service_role （admin）

所以，要隐藏帖子的title列，您可以做到这一点：

-- You have to remove privileges to change them
REVOKE SELECT ON public.posts FROM anon;
-- add all values you want, except title
GRANT SELECT (id, content, created_at, updated_at...)
  ON public.posts TO anon;

但是，如果您尝试获取标题：

，它只会丢弃错误，而不是过滤结果。

桌面的权限拒绝了

我希望它只是亲自过滤。

请记住，如果您更新authenticated或authenticator角色，则可能必须注销并重新注销以使JWT刷新，具体取决于您的设置。

auth.users

现在，如果您想允许某些用户具有某些角色，则可以在role列上的auth.users表中更新该用户：

UPDATE auth.users 
  SET role = 'moderator'
  WHERE id = '0x123';

您将必须在您想要该角色可以访问的每个表上创建角色并授予特权，这可能很麻烦。

这是您在此调整时需要制作的一些工具：

查看所有角色 

SELECT rolname FROM pg_roles ORDER BY rolname;

通过角色查看特权 

SELECT table_schema, table_name, string_agg(column_name, ', ')
AS allowed_columns
FROM information_schema.columns
 -- Exclude system schemas
WHERE table_schema NOT LIKE 'pg_%'
  AND table_schema != 'information_schema'
  AND table_name NOT LIKE 'pg_%'
  AND table_name NOT LIKE 'sql_%'
  AND column_name IN (
      SELECT column_name
      FROM information_schema.role_column_grants
      --- Put your role here
      WHERE grantee = 'authenticator'
  )
GROUP BY table_schema, table_name;

您可以看到，这可能会变得复杂，Supabase可以改变他们在任何新版本中这样做的方式。

Supabase需要什么

Supabase需要一种方法来切换身份验证的用户和公共用户可以看到的内容。当然，如果他们有办法通过UI添加角色，那也是不可思议的。最主要的是，将此内置和标准化用于supabase。

Table Update

想象一下像Hasura这样的选项：

Hasura
那太酷了！

supabase团队，你在哪里？

是什么让我的齿轮是某人实际上以简单的拉力请求开始了这个过程：ADD CRUD permissions。 Supabase的某人说：“当然，没问题，我们下周将在此工作。”……不，什么都没有。然后公关昨天随机关闭！为什么？不知道，没有解释。当然，这是一个简单的公关，他们可能会重新思考一种更简单的方法来做到这一点。但是我离题了。

其他工作

根据您想做的事情，还有其他操作的简单工作。

选择

因此，您不能使用RLS隐藏列，因为您只能隐藏所有列或无。您正在过滤行，而不是列。

视图

您可以创建一个视图：

CREATE VIEW public_posts AS
  -- List all columns except "title"
  SELECT column1, column2, column3, ...
  FROM posts;

和supabase：

const { error, data } = await supabase.from('public_posts')
  .select('*');

当然，应使用RLS阻止您的原始posts表。对我来说，这是一项可怕的工作。当您具有视图（锁定）时，您无法编辑原始表格架构，并且视图甚至没有索引（materialized views除外）。您的视图也不能使用RLS，除非您添加WITH (security_barrier)，否则已知会提供slow performance。不是粉丝。

自定义功能

您也可以使用自定义功能。您需要用RLS阻止posts，并将SECURITY DEFINER添加到您的功能中，以便此功能可以查看它。它还要求您执行更高级的事情，例如在功能内翻译RLS策略。您真正要做的就是过滤器。

  • SECURITY INVOKER-称其为（默认）用户的特权
  • SECURITY DEFINER-拥有它的用户特权 
DROP FUNCTION IF EXISTS get_posts;
CREATE OR REPLACE FUNCTION get_posts()
  RETURNS TABLE(
  -- Define table return types
  )
  SECURITY DEFINER
  AS $$
  BEGIN
    --- Put your RLS filters here
    RETURN QUERY
    --- List all columns except "title"
    SELECT id, author, content, ...
    FROM posts;
  END;
$$ LANGUAGE plpgsql;

和supabase：

const { data, error } = await supabase.rpc('get_posts');

现在，RPC功能发生了一些问题。如果您想加入桌子，Supabase在引擎盖下使用的postgREST将自动检测到FK的关系。

const { error, data } = await supabase.rpc('get_posts')
  .select('*, author(*)');

但是，仅当您返回SETOF posts时，这才有效。由于您的返回帖子减去 title 列，因此这行不通。因此，您将在功能中加入。这可能会很快变得复杂。如果您喜欢SQL，那没问题，但是我们希望事情可以维护。我仍然更喜欢亲自观看。

所以，最好的方法是使用GRANT。

删除

您实际上不需要过滤列才能删除，所以这很好。

插入，更新

保护列的最显着方法之一是使用触发功能：

CREATE OR REPLACE FUNCTION prevent_title_change()
  RETURNS TRIGGER
  AS $$
  BEGIN
    IF NEW.title <> OLD.title THEN
      RAISE EXCEPTION 'Changing the title column is not allowed.';
    END IF;
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql
CREATE TRIGGER prevent_title_change_trigger
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW
EXECUTE FUNCTION prevent_title_change();

这将引发异常，尽管您可以轻松地将新值设置为旧值。请参阅我的Date Protection帖子。

  • 您也可以使用自定义功能，例如在视图语句中，并使用RLS阻止原始帖子表。

第二个表

有时最简单的方法是创建第二个表。无论如何，这都是public.profiles表的常见做法。配置文件表可以与auth.users共享一些数据，但不可查看。但是，如果您愿意，也可以使用posts进行此操作。实际上，您可能会有一个无法更新的posts_meta表，也许可以使用一个可以使用的posts表。您设定了自己的RLS政策。

结论

supabase需要使这一简单和一部分的supabase。这些选项都不像创建新角色那样简单，并且都添加了不必要的复杂性。

有关更多Supabase提示，请参见code.build。

J