英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论