Create Postgres Policy for Row Level Security on foreign key attribute

Question

I'm trying to create a Policy on Postgres to enable Row Level Security but I'm having a bit of trouble. Many examples I've found have a direct FK on the table, but I'm trying to do it on an attribute on the foreign table.

The context here is that I'm trying to have users only be able to see other users from the same organization. The user is set on a configuration parameter organization.current_tenant, which takes on the value of the Organization.key

Setup:

CREATE TABLE organization (id serial primary key, name text, key text);
CREATE TABLE myuser (id serial primary key, name text, user_organization_id integer);

INSERT INTO organization (name, key) values ('org1', 'org1-key');
INSERT INTO organization (name, key) values ('org2', 'org2-key');

INSERT INTO myuser (name, user_organization_id) values ('org1_agent1', 1);
INSERT INTO myuser (name, user_organization_id) values ('org2_agent1', 2);
INSERT INTO myuser (name, user_organization_id) values ('org2_agent2', 2);

ALTER TABLE myuser ENABLE ROW LEVEL SECURITY;
ALTER TABLE myuser FORCE ROW LEVEL SECURITY;

When using a direct FK, organization_id:

CREATE POLICY access_tenant_data ON myuser
USING (user_organization_id::TEXT = current_setting('organization.current_tenant'));

This works since user_organization_id is the foreign key available on the user table.

However, what I want to use is instead something like user.organization.key.

CREATE POLICY access_tenant_data ON myuser
USING (
    key IN (
        SELECT key FROM 
        myuser U INNER JOIN organization O
        ON U.organization_id = O.id
    )::TEXT = current_setting('organization.current_tenant')
);

But this obviously doesn't work. I'm not sure how to proceed from here.

Testing it out:

SET organization.current_tenant = "org1-key";
SELECT * from myuser;

Answer 1

That should be fairly simple:

CREATE POLICY access_tenant_data ON myuser
USING (
   EXISTS (SELECT 1 FROM organization AS o
           WHERE myuser.user_organization_id = o.id
             AND o.key = current_setting('organization.current_tenant')
          )
);

That can be fairly efficient, since PostgreSQL can compute the resulting query using a semi-join.