PostgreSQL RLS Policy – How to Write Queries Properly

postgresqlquery-performancerow-level-security

I'm trying to do a single implicit JOIN inside of an RLS expression. I get a syntax error and have a "hacky" solution using a subquery, but would love to know if there is a better way.

So assuming two tables, projects and objects:

CREATE TABLE app_pub.projects (
  id uuid DEFAULT public.uuid_generate_v4 () NOT NULL,
  owner_id uuid
);

CREATE TABLE app_pub.objects (
  id uuid DEFAULT public.uuid_generate_v4 () NOT NULL,
  project_id uuid
);

The ownership is expressed on the project.owner_id, but the policy of interest will check against objects.project_id. The procedure app_pub.get_current_role_ids () returns an array of role ids for access.

Specifically this query's boolean result tells us if the user has access:

SELECT (p.owner_id = ANY (app_pub.get_current_role_ids ()))
    FROM app_pub.projects p, objects o
    WHERE (p.id = o.project_id)

I'd like to write a policy like so:

CREATE POLICY authenticated_can_select_on_objects
ON app_pub.objects
FOR SELECT
TO authenticated
USING (
SELECT (p.owner_id = ANY (app_pub.get_current_role_ids ()))
    FROM app_pub.projects p
    WHERE (p.id = project_id)
);

But this obviously fails due to syntax error, hence my question…

So I do have my "hacky" solution is to use a subquery and do TRUE IN (subquery):

CREATE POLICY authenticated_can_select_on_objects
ON app_pub.objects
FOR SELECT
TO authenticated
USING (
    (TRUE IN (
        SELECT (p.owner_id = ANY (app_pub.get_current_role_ids ()))
            FROM app_pub.projects p
            WHERE (p.id = project_id)
        )    
    )
);

And YaY "it works!", with this TRUE IN (subquery) which feels wrong. I know I could write a procedure and use that, but was hoping to do something inline in hopes the query planner would be happier.

So, with all that context:

  1. is this "hacky" solution the only/best way?
  2. can I do a clean select or join like my first attempt was trying to accomplish?

Best Answer

The USING clause of the CREATE POLICY command expects an expression inside the parentheses. An SQL query, by itself, is not an expression, but you can make it into one by writing it as a scalar subquery. So you just need another pair of parentheses:

CREATE POLICY ..
USING (
  (SELECT p.owner_id = ANY (app_pub.get_current_role_ids ())
   FROM app_pub.projects p
   WHERE p.id = project_id)
);
Related Question