Best way to cast a VIEW row type to the underlying TABLE row type

Question

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?

Laurenz Albe · Answer 1 · 2022-10-18T21:02:15.807

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?

Best way to cast a VIEW row type to the underlying TABLE row type

1 Answers1