1

I have a table with an index and a row-level security policy. Due to this problem (more details: 1, 2, 3, 4, 5), the index is not used when the policy applies, which makes my queries unbearably slow.

The workaround I am contemplating would be to create a VIEW with security_invoker = false and security_barrier = false. (If I do enable the security_barrier, the query again doesn't use the index).

The problem I am facing now is that I cannot just change the queries to use FROM my_view AS example instead of FROM my_table AS example, since some of them use functions that are defined to take the my_table composite type. A simplified example:

CREATE TABLE example (
  id int,
  name text,
  is_visible boolean
);
CREATE VIEW test AS SELECT * FROM example WHERE is_visible;
CREATE FUNCTION prop(e example) RETURNS text LANGUAGE SQL AS $$ SELECT e.id::text || ': ' || e.name; $$;

SELECT e.prop FROM example e; -- works
SELECT e.prop FROM test e;    -- ERROR:  column e.prop does not exist

(online demo)

Now the question is how to cast the rows to the expected type? There is this question and I also found a way to do this using the ROW constructor, but I'm not certain how good this is:

SELECT e.prop FROM (SELECT (ROW(test.*)::example).* FROM test) e;

It's nice that I can just use it as a drop-in replacement for the table expression (without changing anything else in the query), and it does work (postgres accepts it and does use my index when I have the respective WHERE clause), but it looks horrible. Are there problems with my approach that I am missing? Is there a better solution?

2
  • Why don't you just include it into the view definition itself?
    – user1822
    Oct 18, 2022 at 11:36
  • @a_horse_with_no_name you mean SELECT *, example.prop? I'm not using the function in all queries that depend on the view, and it's not just one function but many. I wouldn't want to update the view if I were to create a new function. (Also this doesn't work with functions taking multiple parameters)
    – Bergi
    Oct 18, 2022 at 19:00

1 Answer 1

0

You can use two casts via text:

SELECT prop(e::text::example) FROM test e;

Your idea with the row constructor also works, and it is a better solution. You can easily verify that with EXPLAIN (VERBOSE):

EXPLAIN (VERBOSE, COSTS OFF) SELECT test::text::example FROM test;

                    QUERY PLAN                          
══════════════════════════════════════════════════
 Seq Scan on laurenz.test
   Output: ((ROW(test.a, test.b))::text)::example
 Query Identifier: 1322302112480528651
(3 rows)

EXPLAIN (VERBOSE, COSTS OFF) SELECT ROW(test.*)::example FROM test;

               QUERY PLAN                          
════════════════════════════════════════
 Seq Scan on laurenz.test
   Output: ROW(test.a, test.b)::example
 Query Identifier: 3255314901775380938
(3 rows)

The latter version has one type cast less.

But the whole setup seems wrong. If you want to bypass row level security, create a role with BYPASSRLS or create a special “empty” policy. Another solution might be a SECURITY DEFINER function owned by the table owner.

But then, if you bypass row level security, hacky or clean, why do you have it at all?

3
  • I'm not really trying to bypass the security policy, the view has a WHERE condition with the same rule as the policy. I really like to generally have the rls policy when interacting with the table normally, it's just there is a few queries that need to use the index so I was going to change these to select from the view instead.
    – Bergi
    Oct 18, 2022 at 19:11
  • Is converting to text and back really better? I thought prop(ROW(e.*)::example) would be optimised away as a no-op since the binary representation would be identical.
    – Bergi
    Oct 18, 2022 at 19:13
  • I tried to answer that question in my extended answer. Oct 18, 2022 at 21:02

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.