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;

CodePudding user response：

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.

CodePudding user response：

You need to define a function that returns BOOLEAN. Within the function you will determine logic and return TRUE or FALSE. The vital piece is that : USING clause of a policy expects a BOOLEAN value and we can dictate the behavior of the policy (allow/disallow) based on what gets returned by the function - True or False.

Here is the sample FUNCTION to be used in the policy:

create function KeyExist(organization_id int, env_current_tenant text)  returns boolean
language plpgsql
as
$$
declare
    flag boolean;
    keyValue text;
begin
select key into keyValue from organization where id=organization_id;
if not found then
    flag := false;
else
    if keyValue = env_current_tenant then
        flag := true;
    else
        flag := false;
    end if;
end if;
return flag;
end; $$;

Policy:

CREATE POLICY access_tenant_data ON myuser for select
USING (KeyExist(user_organization_id,current_setting('organization.current_tenant')));

Demonstration:

postgres=# create function KeyExist(organization_id int, env_current_tenant text)  returns boolean
postgres-# language plpgsql
postgres-# as
postgres-# $$
postgres$# declare
postgres$# flag boolean;
postgres$# keyValue text;
postgres$# begin
postgres$# select key into keyValue from organization where id=organization_id;
postgres$# if not found then
postgres$# flag := false;
postgres$# else
postgres$# if keyValue = env_current_tenant then
postgres$#
postgres$# flag := true;
postgres$# else
postgres$#
postgres$# flag := false;
postgres$# end if;
postgres$# end if;
postgres$# return flag;
postgres$# end; $$;
CREATE FUNCTION

postgres=# CREATE POLICY access_tenant_data ON myuser for select
postgres-# USING (KeyExist(user_organization_id,current_setting('organization.current_tenant')));
CREATE POLICY

Verification:

postgres=> SET organization.current_tenant = "org1-key";
SET
postgres=> select * from myuser;
 id |    name     | user_organization_id
---- ------------- ----------------------
  1 | org1_agent1 |                    1
(1 row)


postgres=> SET organization.current_tenant = 'org2-key';
SET
postgres=> select * from myuser;
 id |    name     | user_organization_id
---- ------------- ----------------------
  2 | org2_agent1 |                    2
  3 | org2_agent2 |                    2
(2 rows)

Tables:

postgres=# \d myuser
                                   Table "public.myuser"
        Column        |  Type   | Collation | Nullable |              Default
---------------------- --------- ----------- ---------- ------------------------------------
 id                   | integer |           | not null | nextval('myuser_id_seq'::regclass)
 name                 | text    |           |          |
 user_organization_id | integer |           |          |
Indexes:
    "myuser_pkey" PRIMARY KEY, btree (id)
Policies (forced row security enabled):
    POLICY "access_tenant_data" FOR SELECT
      USING (keyexist(user_organization_id, current_setting('organization.current_tenant'::text)))

postgres=# select * from myuser;
 id |    name     | user_organization_id
---- ------------- ----------------------
  1 | org1_agent1 |                    1
  2 | org2_agent1 |                    2
  3 | org2_agent2 |                    2
(3 rows)

postgres=# select * from organization;
 id | name |   key
---- ------ ----------
  1 | org1 | org1-key
  2 | org2 | org2-key
(2 rows)