supabase中基于角色的身份验证

发布于 2025-02-08 12:37:40 字数 323 浏览 3 评论 0原文

我正在尝试在supabase中制定策略，其中具有管理员角色的用户只能获取“代理”角色的员工列表

，其中有一个“用户”表，我正在尝试添加以下策略

"(auth.email() in (select users.email from users where users.role = 'admin')) and (role = 'agent')

用户表具有以下列

firstName | lastname |角色|电子邮件| 但是，密码

我会在用户表格上获得无限的递归。

我如何在这里创建基于角色的政策？提前致谢！

原文

I am trying to make policy in supabase where a user with admin role can only get list of employees whose role are "agent"

There is a "users" table and I am trying to add following policy

"(auth.email() in (select users.email from users where users.role = 'admin')) and (role = 'agent')

User table has following columns

firstname | lastname | role | email | password

However I am getting Infinite recursion on users table mesage.

How can I create a role based policy here?
Thanks in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

失眠症患者 2025-02-15 12:37:40

当在表上查询RLS时，这是一个已知的问题，因为策略查找也受策略的约束。您需要将查询移至安全定义器函数中，并在策略中调用该功能，以避免无限递归。

CREATE OR REPLACE FUNCTION admin_only(email string)
  returns boolean AS
$
  EXISTS (select users.email from users 
  where users.role = 'admin'
  and users.email = email)
$ stable language sql security definer;

然后，在您的策略中，

admin_only(auth.email())

我对您要应用的策略感到有些困惑，因为您正在检查users.role is admin，但与此同时还要检查角色是否也是代理，这是否意味着可以为用户分配多个角色？

This is a known issue when doing a query on a table that the RLS will be set on because the policy lookup is subject to the policy too. You will need to move the query into a security definer function and call the function in the policy instead to avoid infinite recursion.

CREATE OR REPLACE FUNCTION admin_only(email string)
  returns boolean AS
$
  EXISTS (select users.email from users 
  where users.role = 'admin'
  and users.email = email)
$ stable language sql security definer;

Then in your policy add

admin_only(auth.email())

I am a little confused by the policy you are trying to apply as you are checking if the users.role is admin but at the same time you are checking if the role is agent too, does this mean a user can be assigned multiple roles?

回复收藏 0 原文

~没有更多了~