Add policy based on data in another table

[tawjaw]

Hello,

How can I add policy based on data in another policy?

For example, there is the Auth roles and I have the application roles. The application roles are defined in the users table and it's role 1,2,3

I have a table Todo that I want:

Policy 1: only the users with role 1 to be able to select all from it.
Policy 2: users with role 1 to be able to insert, update, delete only the rows with their uuid match.

So the policy on the Todo table needs to check from the users table the application_role if it is 1.

I am assuming I can do SELECT from Table inside the policy?

Thanks

[silentworks]

@tawjaw yes you should be able to do that, I haven't tested it but a policy like the below should work:

CREATE POLICY user_role_1_select ON todo FOR SELECT
    USING (1 = (SELECT application_role FROM users WHERE id = auth.uid));

[tawjaw]

Thanks! I'll test it out