Postgresql Row-level Security Involving Foreign Key With Other Table

I wonder if the following is possible in PostgreSQL using RLS (or any other mechanism). I want a user to be able to get certain rows of a table if its id matches a column in anothe

Solution 1:

If row level security is not working that may be because one of the following applies:

• you didn't enable row level security:

  ALTERTABLE "user" ENABLE ROW LEVEL SECURITY;
  

• the user owns the table

  You can enable row level security for the owner with

  ALTERTABLE "user" FORCE ROW LEVEL SECURITY;
  

• you are a superuser, which is always exempt from RLS

• you are a user defines with BYPASSRLS

• the parameter row_security is set to off

Other than that, you will probably have to join with user_tenant in your policy:

CREATE POLICY tenant_policy ON "user"
   USING (
      EXISTS(SELECT1FROM user_tenant AS ut
             WHERE ut.user_id = "user".id
               AND ut.tenant_id = current_setting('my_user.current_tenant')::uuid
            )
      );