_ord_ore domains install on non-superuser Postgres without their opclass, making ORDER BY silently wrong

[coderdan]

Summary

On a non-superuser install (Supabase, or any managed Postgres), the _ord_ore domains are created, but the btree operator class they depend on is not. The result is a surface that looks installed and usable, where ORDER BY eql_v3.ord_term_ore(col) silently returns rows in an order unrelated to the plaintext.

Without eql_v3_internal.ore_block_256_operator_class, ordering was never going to work. So the bug is not the ordering fallback — it is that we install the ORE domains at all in an environment where they cannot be correct.

The DO ... EXCEPTION WHEN insufficient_privilege skip introduced in #375 makes the installer succeed everywhere, but it leaves the ORE domains behind without their ordering machinery. #378 proposes testing that skip path; this issue argues the skip path's end state is itself wrong.

What actually happens

eql_v3.ord_term_ore() returns eql_v3_internal.ore_block_256, which is a composite type (pg_type.typtype = 'c'), not a domain over bytea.

• With the opclass present, ORDER BY resolves it and comparison routes through ore_block_256_lt → the ORE comparator. Correct.
• With the opclass absent, Postgres does not raise could not identify an ordering operator. It falls back to its built-in record comparison, which walks the composite down to the raw bytes field and compares bytewise.

Bytewise order over ORE ciphertext is a random permutation of plaintext order — fixed at encryption time, so stable across queries, and meaningless.

Blast radius

	non-superuser	superuser
_ord_ore domains created	✅ yes	✅ yes
ORE comparison functions	✅ yes (33)	✅ yes
ore_block_256_operator_class	❌ no (pg_opclass count 0)	✅ yes (count 1)
eql_v3.gt / lt / gte / lte	✅ correct	✅ correct
ORDER BY eql_v3.ord_term_ore(col)	❌ silently wrong	✅ correct
btree index on the column	❌ cannot be created	✅ yes

Range filters are unaffected: </>/<=/>= are backed by ore_block_256_lt and operators need no opclass. Only ORDER BY, which resolves an opclass, takes the fallback. So the failure is confined to ordering and indexing — but it is silent.

Reproduction

Measured against supabase/postgres:17.4.1.048, EQL 3.0.0 installed as the non-superuser postgres role via stash eql install --eql-version 3 --supabase --direct.

1. The opclass is missing, the domains are not.

SELECT count(*) FROM pg_opclass o JOIN pg_namespace n ON n.oid = o.opcnamespace
WHERE n.nspname LIKE 'eql_v3%';
-- non-superuser: 0     superuser: 1

SELECT count(*) FROM pg_type t JOIN pg_namespace n ON n.oid = t.typnamespace
WHERE n.nspname = 'public' AND t.typname LIKE '%ord_ore%';
-- both: 9 (integer, smallint, bigint, real, double, numeric, date, timestamp, text)

2. ORDER BY diverges from the ORE comparator ~44% of the time.

WITH pairs AS (
  SELECT
    ROW(ARRAY[ROW(gen_random_bytes(65))::eql_v3_internal.ore_block_256_term])::eql_v3_internal.ore_block_256 AS a,
    ROW(ARRAY[ROW(gen_random_bytes(65))::eql_v3_internal.ore_block_256_term])::eql_v3_internal.ore_block_256 AS b
  FROM generate_series(1, 200)
), cmp AS (
  SELECT ((a).terms[1].bytes < (b).terms[1].bytes)              AS bytewise_lt,  -- what ORDER BY does
         (eql_v3_internal.compare_ore_block_256_terms(a, b) < 0) AS ore_lt        -- what is correct
  FROM pairs
)
SELECT count(*) FILTER (WHERE bytewise_lt = ore_lt)  AS agree,
       count(*) FILTER (WHERE bytewise_lt <> ore_lt) AS disagree
FROM cmp;
-- agree 113 | disagree 87

(65 bytes = the minimum well-formed term, 49*N + 16 with N = 1.)

3. The two installs demonstrably take different code paths. Order a deliberately malformed 1-byte term:

WITH v(label, t) AS (
  VALUES ('A', ROW(ARRAY[ROW('\xff'::bytea)::eql_v3_internal.ore_block_256_term])::eql_v3_internal.ore_block_256),
         ('B', ROW(ARRAY[ROW('\x01'::bytea)::eql_v3_internal.ore_block_256_term])::eql_v3_internal.ore_block_256)
) SELECT label FROM v ORDER BY t;

• non-superuser: returns B, A — record comparison never looks at ORE semantics.
• superuser: ERROR: Malformed ORE term: 1 bytes, raised from ore_block_256_lt.

The superuser install validates its input because ordering goes through the ORE code. The non-superuser install cannot, because it never gets there.

Why this matters

A user on Supabase can declare an eql_v3_integer_ord_ore column, insert, filter with </> correctly, and then ORDER BY it and get a stable, plausible-looking, wrong answer with no error and no NOTICE at query time. Encrypted ordering that silently lies is worse than encrypted ordering that is unavailable.

Suggested fixes

Roughly in order of preference:

1. Do not create the _ord_ore domains when the opclass cannot be created. If ORE ordering is unavailable, the type that exists to provide it should not exist either. A user then gets a clear "type does not exist" at DDL time rather than wrong rows at query time.
2. Fail the install loudly for the ORE portion on non-superuser, and document _ord (OPE) as the managed-Postgres ordering type. This is the same outcome, stated earlier.
3. If the domains must exist for compatibility, make ordering impossible rather than wrong — e.g. define the type so record comparison cannot apply, or ship an opclass whose support function raises unless the real one is present.

Whatever is chosen, _ord / OPE is unaffected: eql_v3_internal.ope_cllw is a domain over bytea, so it orders via the native bytea btree and is correct on every provider without superuser. That is the ordering type managed Postgres should be steered toward.

Related

• fix(v3): install SEM btree operator classes conditionally (Supabase / non-superuser) #375 — introduced the conditional opclass skip.
• Add automated regression test for non-superuser operator-class skip path (Supabase install) #378 — proposes a regression test for the skip path. Worth resolving this issue first: Add automated regression test for non-superuser operator-class skip path (Supabase install) #378 asserts the skip leaves pg_opclass with 0 custom classes and calls that success, which is exactly the end state described above.
• cipherstash/stack — docs/eql-v3-ord-term-ordering-defect.md documents the ordering symptom; the stack integration matrix defers the nine _ord_ore domains for this reason.

[coderdan]

Correction: the premise of this issue is wrong. The bundle already guards ORE. Closing.

I filed this claiming that on a non-superuser install the _ord_ore domains are created without their operator class, leaving ORDER BY eql_v3.ord_term_ore(col) to sort silently wrong. The silent wrongness is unreachable. I should have checked the write path before filing — apologies for the noise.

What actually happens

supabase/postgres:17.4.1.048, EQL 3.0.0 installed as the non-superuser postgres role:

CREATE TABLE ore_probe (id int, v public.eql_v3_integer_ord_ore);
-- CREATE TABLE

INSERT INTO ore_probe VALUES (1, '{"v":3,"i":{"t":"ore_probe","c":"v"},"c":"x","ob":["00"]}'::jsonb);
-- ERROR:  EQL: public.eql_v3_integer_ord_ore cannot be used on this platform:
--         the EQL installer could not create the ORE operator class
--         (requires superuser, unavailable on e.g. cloud-hosted Supabase)
-- HINT:   Use public.eql_v3_integer_eq (equality) or public.eql_v3_integer_ord
--         (ordering) or public.eql_v3_integer_ord_ope (ordering) instead.
-- CONTEXT: PL/pgSQL function eql_v3_internal.ore_domain_unavailable(jsonb,text,text)

The identical INSERT succeeds on plain Postgres as a superuser.

So the domain's CHECK calls ore_domain_unavailable() and fails loudly at write time, with a hint naming the alternatives. No value can ever enter an ORE column on managed Postgres, so no query — ordering or otherwise — can return wrong rows from one. The bundle does exactly the right thing, and the error message is better than most.

Where I went wrong

My ordering evidence used hand-constructed ROW(...)::eql_v3_internal.ore_block_256 values inside a CTE, never a stored column value. That demonstrated a type-level property — ore_block_256 is a composite type, so with no opclass ORDER BY falls back to Postgres's built-in record comparison, which walks to the raw bytes field and is uncorrelated with ORE order — but it is not reachable through any user-visible path, because the write is refused first.

The type-level observation stands and is mildly interesting. It is not a bug anyone can hit.

The one residual point (not worth reviving this issue for)

The nine _ord_ore domains are still created on an install that cannot use them, so \dT public.eql_v3_* advertises types that raise on first write. Failing at CREATE TABLE rather than at INSERT would surface the problem a step earlier. The current behaviour is defensible and the hint is excellent, so if that earlier failure is wanted it deserves its own issue rather than this one.

Downstream

cipherstash/stack defers the nine _ord_ore domains in its EQL v3 integration matrix. The stated reason has been corrected from "ORDER BY silently mis-sorts" to the accurate one: ORE columns cannot hold data on managed Postgres, so a matrix that must pass on both a superuser and a managed database cannot seed them. They get a superuser-only suite.

Separately, and confirming the OPE story from the other direction: eql_v3.ord_term(col) on an OPE-backed _ord column reproduces plaintext order on managed Postgres without superuser, because eql_v3_internal.ope_cllw is a domain over bytea and orders through the native btree.

#378 (a regression test for the opclass skip path) is unaffected and still worth doing.

Closing as invalid — filed on a mis-read of my own evidence.