在Supabase Postgres RLS中的条件性插入。

huangapple go评论99阅读模式
英文:

Conditional INSERT in supabase Postgres RLS

问题

我问题的前提很简单,我有三个表:

  • users(user_id)
  • records_users(record_id,user_id)
  • records(record_id,created_by)

我希望只有当用户的ID(来自users表的user_id列)存在于记录表的created_by字段中时,用户才能向records_users表中插入数据。

换句话说: 我希望用户只能插入与他们在记录表中通过created_by字段关联的实际记录的行。

我尝试过的:

  • 已经查看了RLS选项,但似乎找不到如何基于正在插入的内容集成条件的方法。是否存在类似这样的东西?
CREATE POLICY "Enable insert on linked records"
ON public.records_users
FOR INSERT USING (
  record_id_being_inserted IN (SELECT record_id FROM records WHERE created_by = auth.uid()) 
);
  • 我已经创建了一个RPC Postgres函数来应用该逻辑,但它被应用于records_users表的RLS策略所阻止,我不希望在没有这个逻辑的情况下向所有用户开放插入权限。

任何帮助将不胜感激。

英文:

The premise of my question is simple, I have three tables :

  • users (user_id)
  • records_users (record_id, user_id)
  • records (record_id, created_by)

I would like users to be able to INSERT into the records_users table only if their id (column user_id from the users table) is in the created_by field of the records table.

In other words : I would like users to only be able to insert rows for which the record_id is an actual record that is tied to them in the record table through the created_by field.

What I have tried :

  • Have looked through RLS options but I cannot seem to find how to integrate a condition based on what is being inserted. Does something like this exist ?

     CREATE POLICY "Enable insert on linked records"
     ON public.records_users
         FOR INSERT USING (
           record_id_being_inserted IN (SELECT record_id FROM records WHERE created_by                 = auth.uid()) 
         );
    
  • I have created an RPC Postgres function which applies that logic, but it is blocked by the RLS policies applied to the records_users table and I do not wish to open inserts to all users without having this logic mandatory.

Any help would be appreciated.

答案1

得分: 0

根据 @andrew smith 的说法,答案非常明确。您可以通过使用它们的列名引用插入的字段。例如:

CREATE POLICY "Enable insert on linked records"
  ON public.records_users
      FOR INSERT USING (
   record_id_being_inserted IN (SELECT record_id FROM records WHERE created_by = auth.uid()) 
 );

如果 record_id_being_inserted 是您要检查的列字段的名称,此查询将起作用。

英文:

As stated by @andrew smith, the answer was pretty straight forward. You can simply reference the fields inserted by using their column names. So for example :

 CREATE POLICY "Enable insert on linked records"
 ON public.records_users
     FOR INSERT USING (
       record_id_being_inserted IN (SELECT record_id FROM records WHERE created_by                 = auth.uid()) 
     );

This query would work if record_id_being_inserted is the name of the column field you want to check.

huangapple
  • 本文由 发表于 2023年8月10日 19:48:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/76875461.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定