I want to create RLS were gyms can only see their own members

This is the Policy I wrote

-- create the policy
CREATE POLICY gym_members_policy ON members
FOR SELECT
USING (gym_id = current_setting('jwt.claims.gym_id')::integer)
WITH CHECK (gym_id = current_setting('jwt.claims.gym_id')::integer);

-- grant the gym_managers role permission to use the policy
GRANT USAGE ON POLICY gym_members_policy TO gym_managers;

I keep getting this error
Error adding policy: failed to create pg.policies: syntax error at or near "CREATE"

garyaustin•12/6/22, 1:48 PM

I don’t know that it would cause an error, but select does not use with check.
Also I would think a different error, but does the policy exist already?

mcdaded•12/6/22, 9:29 PM

~~Do you need to have quotes around the policy name "gym_members_policy" also~~ yeah i dont think you need the with check just the using

mcdaded•12/6/22, 9:30 PM

Alternative idea as well is to use the auth.uid() to look up the user in a profiles table to see what gym the belong to.

mcdaded•12/6/22, 9:31 PM

This is one example that I have that is similar to that

CREATE policy
  "Accounts can only be viewed by users within that org." on accounts FOR
SELECT
  USING (
    EXISTS (
      SELECT
        1
      FROM
        profiles
      WHERE
        profiles.organization_id = accounts.organization_id
        AND profiles.id = auth.uid()
    )
  );

CREATE policy
  "Accounts can only be viewed by users within that org." on accounts FOR
SELECT
  USING (
    EXISTS (
      SELECT
        1
      FROM
        profiles
      WHERE
        profiles.organization_id = accounts.organization_id
        AND profiles.id = auth.uid()
    )
  );

garyaustin•12/6/22, 9:34 PM

You only need quotes if it is not a valid postgres name. So in ops case no, in your case yes.

I want to create RLS were gyms can only see their own members

Similar Threads

Similar Threads

Similar Threads