3

I have a postgres table which has a policy enforced on it, like so (extra columns redacted for brevity):

create table live_specs (
  catalog_name          catalog_name not null,
  spec_type             catalog_spec_type not null,
);

create policy "Users must be read-authorized to the specification catalog name"
  on live_specs as permissive for select
  using (auth_catalog(catalog_name, 'read'));

create index idx_live_specs_spec_type on live_specs (spec_type);
create index idx_live_specs_catalog_name on live_specs (catalog_name);

The auth_catalog function cannot be indexed because it's not immutable, so it's hard to optimise this function.

I have a view that I query, which in turn queries this table with the policy:

create view live_specs_ext as
select
  l.*,
  c.id as connector_id,
from live_specs l
left outer join connectors c on c.image_name = l.connector_image_name;

Now, I'm running a query against this view, filtering on spec_type which is an indexed field, however I can see that postgres seems to do a full table scan when enforcing the policy and doesn't utilise the index of spec_type (extra lines of explain omitted for brevity):

EXPLAIN SELECT * FROM live_specs_ext WHERE spec_type = 'capture' LIMIT 10;
.
.
Filter: (auth_catalog((catalog_name)::text, 'read'::grant_capability) AND (spec_type = 'capture'::catalog_spec_type))
.
.

From reading CREATE POLICY page of postgres doc I understand that:

Generally, the system will enforce filter conditions imposed using security policies prior to qualifications that appear in user queries, in order to prevent inadvertent exposure of the protected data to user-defined functions which might not be trustworthy. However, functions and operators marked by the system (or the system administrator) as LEAKPROOF may be evaluated before policy expressions, as they are assumed to be trustworthy.

However, if I understand this correctly, it means the spec_type = 'capture' qual, which uses the built-in = function, is not being run before the policy because = is not leakproof. Is that a correct understanding?

Is there any way for me to ask Postgres to run my spec_type = 'capture' qual before the policy?

Mahdi Dibaiee
  • 885
  • 1
  • 7
  • 20
  • The builtin `=` is leakproof, so I'd assume that there is some other reason why postgres is not using the index here. Maybe it does expect most of the rows from the `live_specs` table to be used in the join, are your statistics up to date? – Bergi Oct 24 '22 at 16:14
  • 1
    "*The `auth_catalog` function cannot be indexed because it's not immutable*" - did you mark it as `STABLE` though? – Bergi Oct 24 '22 at 16:15
  • @Bergi I tried marking it as `STABLE`, but it doesn't have an effect here, the query is still not using the index – Mahdi Dibaiee Oct 24 '22 at 16:24
  • @Bergi what do you mean by "are your statistics up to date"? – Mahdi Dibaiee Oct 24 '22 at 16:24
  • Marking the `auth_catalog()` function as [`STABLE`](https://www.postgresql.org/docs/current/xfunc-volatility.html) should enable you to build an index with a call to this function. You can update table statistics using [`analyze table_name;`](https://www.postgresql.org/docs/current/sql-analyze.html) – Zegarek Oct 24 '22 at 16:29
  • There is really very little you can do with a function. Perhaps you can express the condition without a function. If you cannot, it may be that your case is just too complicated for good performance. Perhaps you have to simplify something. – Laurenz Albe Oct 24 '22 at 17:24
  • @Zegarek I tried `alter function auth_catalog stable` and `analyze live_specs`, but the explanation of the query still points to a `Filter` rather than an index scan – Mahdi Dibaiee Oct 25 '22 at 11:45
  • We have concluced in our general query there are other parts that can be optimised to give us an acceptable query time and we are working on those parts, but I still would like to know if there is a way out of here to ask postgres to allow us to run an indexed query before the table's policy. – Mahdi Dibaiee Oct 25 '22 at 13:33

0 Answers0