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;

How can you identify the current user in the users table? Once you have that, the rest is simple. — Laurenz Albe
– Laurenz Albe, Commented Feb 9, 2022 at 7:36
At the application level, I am able to tell who the user is based on the request. — Andrew
– Andrew, Commented Feb 9, 2022 at 9:48
You must find a way to pass that information to the database (perhaps also with a placeholder parameter) so that you can use it in the policy definition to identify the user. — Laurenz Albe
– Laurenz Albe, Commented Feb 9, 2022 at 10:08
I'm not entirely sure why I need the current user, isn't the current user's organization all that is required, since that's what I will filter all the users by? — Andrew
– Andrew, Commented Feb 9, 2022 at 10:56

Laurenz Albe · Accepted Answer · 2022-02-09 22:11:11Z

2

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.

edited Feb 9, 2022 at 22:11

answered Feb 9, 2022 at 11:06

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrew Over a year ago

Hm, this doesn't seem to work; despite SET organization.current_tenant = 'org1-key';, a SELECT * FROM myuser; returns all users

Laurenz Albe Over a year ago

It works here. Perhaps you are a superuser or a user created with BYPASSRLS.

rajorshi · Accepted Answer · 2022-02-09 09:06:25Z

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)

Collectives™ on Stack Overflow

Create Postgres Policy for Row Level Security on foreign key attribute

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related