0

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?
1
  • 1
    The query planner is happy with SQL (not PL/pgSQL) functions.
    – CL.
    Jun 15, 2020 at 8:30

1 Answer 1

3

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)
);
1
  • wow, soooooo simple. Thank you. I honestly wouldn't have thought of that... thank you thank you thank you
    – pyramation
    Jun 15, 2020 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.