Fast Text Search with Row-Level Security

What you'll learn

• Why includesInsensitive filters can ignore trigram indexes on tables that use row-level security
• How to check whether your service is affected
• How to make those filters use their index with define_indexed_search and the IndexedSearchPluginFactory

The problem

Mosaic services protect their tables with PostgreSQL row-level security (RLS) and speed up text search with trigram indexes created by ax_define.define_like_index. A GraphQL filter such as title: { includesInsensitive: "matrix" } becomes the SQL condition title ILIKE '%matrix%', which a trigram index can answer quickly.

Under RLS, PostgreSQL only evaluates a condition before the row-level security check when the function behind it is marked as LEAKPROOF. The ILIKE function (texticlike) is not leakproof by default, and only a superuser can change that. If the flag is missing, PostgreSQL cannot use the index and checks every row of the table instead. The results stay correct, but the search gets slower as the table grows.

Relation filters are affected the same way. A filter like moviesTags: { some: { name: { includesInsensitive: "drama" } } } checks every tag row instead of using the tag name index.

Do you need to change anything?

Your service is affected if all of these apply:

• A table uses row-level security, e.g. from define_authentication.

• A text column of that table has a trigram index from define_like_index.

• Clients filter that column with includesInsensitive.

• texticlike is not leakproof in your database:

  SELECT proleakproof
  FROM pg_catalog.pg_proc
  WHERE oid = 'pg_catalog.texticlike(text, text)'::regprocedure;

note

The local development setup of the solution templates marks texticlike as leakproof (see migrations/after-reset/install-extensions.sql), so searches can be fast locally and slow in a database that does not allow this. Check the database your service runs on, not only your local one.

If proleakproof is true in every database your service runs on, searches already use their indexes and no change is required. The approach below works either way. Use it if you want search performance that does not depend on database privileges, for example before moving to another PostgreSQL provider.

How it works

For each registered column, a small database function finds the primary keys of the matching rows. It runs with the table owner's rights, so PostgreSQL can use the trigram index. The GraphQL query then selects the rows with those keys and still applies all row-level security policies to them:

-- before: checks every row
WHERE title ILIKE '%matrix%'

-- after: uses the trigram index
WHERE (id) IN (SELECT * FROM app_hidden.movies_title_candidates('%matrix%'))

The GraphQL API does not change. Existing includesInsensitive filters, also nested in and, or, not and relation filters, and bulk operations that use filters, return the same results as before. No frontend changes are needed.

Add indexed search to a column

The Media Service of the media template uses this for all title and original title columns of movies, TV shows, episodes and collections, and for the names of their tags, cast members and production countries. Use it as a reference.

1. Update the packages and define functions

Update @axinom/mosaic-db-common and @axinom/mosaic-graphql-common to a version that includes define_indexed_search and IndexedSearchPluginFactory. Then add the new define functions to your database with the util:define-func-migration script and commit the migration, as described in Database Migrations and ax_define functions.

2. Create the candidate functions

In migrations/current.sql, call define_indexed_search for each column. Use the same read permissions as in the table's define_authentication call:

SELECT ax_define.define_indexed_search('title', 'movies', 'app_public', 'MOVIES_VIEW,MOVIES_EDIT,ADMIN', ':DATABASE_GQL_ROLE');
SELECT ax_define.define_indexed_search('name', 'movies_tags', 'app_public', 'MOVIES_VIEW,MOVIES_EDIT,ADMIN', ':DATABASE_GQL_ROLE');

This creates app_hidden.<table>_<column>_candidates. Only the GraphQL role can call it, and it checks the read permissions before it returns any keys.

• For tables that use define_multitenant_authentication, use define_multitenant_indexed_search with the same parameters. It only returns rows of the current tenant and environment.
• If the table's define_authentication call has an additional RLS check, pass the same check as the optional sixth parameter.

Commit the migration with yarn db:commit.

3. Register the columns

Register the same columns with the IndexedSearchPluginFactory, for example next to the other PostGraphile plugins of the domain:

import { IndexedSearchPluginFactory } from '@axinom/mosaic-graphql-common';

export const AllMoviePlugins = makePluginByCombiningPlugins(
  IndexedSearchPluginFactory([
    { table: 'movies', column: 'title' },
    { table: 'movies_tags', column: 'name' },
  ]),
  // ...other plugins
);

The plugin must be added after the connection filter plugin. It fails on startup if a registered column or table does not exist.

4. Verify

Run your existing filter tests. In addition, check with representative data and the GraphQL role that the searches you care about got faster. The media template has an example in services/media/service/src/tests/movies/indexed-search.db.spec.ts that also checks the query plan.

To remove indexed search from a column, remove it from the plugin and call ax_define.drop_indexed_search('<column>', '<table>') in a migration.

Things to consider for your service

The media template covers common cases. Review these points against your own service and choose what fits it best:

• Custom row-level security. The candidate function applies the read permissions and the optional additional RLS check, like define_authentication. If your table has other policies, for example per-user ownership rules, either pass an equivalent check or do not register the column. The final rows are always filtered by all policies, but the candidate function should not return keys of rows the caller cannot see.
• Which columns to register. Register columns that clients search and that have a trigram index. Small lookup tables and tables without row-level security do not benefit.
• Short search terms. Terms without three consecutive letters or digits cannot use a trigram index. For those, the plugin keeps the original filter.
• Very broad terms. A term that matches a large part of a table returns many keys. For relation filters on large child tables this can be slower than before. Measure the searches that matter to your users.
• Nullable columns. For nullable columns, the plugin adds a condition that keeps the results identical for negated filters. That condition makes these searches gain less than searches on NOT NULL columns.
• Other operators. Only includesInsensitive is optimized. Other operators, such as startsWith or likeInsensitive, keep their original SQL. If your frontend builds its own %term% patterns with likeInsensitive, consider switching to includesInsensitive.