Dev.to AI 🤖 Ai 👁 0 📖 5 min read

A Reader Found a Silent Failure in My Postgres Memory Library. Here's the Fix

Last month I wrote about keeping AI agents' shared memory apart with PostgreSQL row-level security, and released adk-memshield, an open-source memory service that does it. The pitch was simple: if the app forgets its WHE

Last month I wrote about keeping AI agents' shared memory apart with PostgreSQL row-level security, and released adk-memshield, an open-source memory service that does it. The pitch was simple: if the app forgets its WHERE clause, the database still refuses to hand you someone else's rows.

Then a reader, Sunnatulla, tested that pattern the way production runs it, and found a failure I hadn't tested for.

What the reader found

Their setup: PostgreSQL 17 with pgvector, 100,000 memories across three tenants, an HNSW vector index, and a light user who could see only 30 of the 33,000 rows in their tenant. A plain query was exact and fast. Then they ran the same search through prepared statements, which your driver turns on for you: psycopg 3 prepares a statement after five executions, and asyncpg prepares everything from the start.

After a few executions, Postgres can switch a prepared statement to a cached generic plan. For a nearest-neighbour ORDER BY embedding <=> ... LIMIT ..., that generic plan was the HNSW index scan with the whole security policy applied afterwards as a filter. HNSW hands back about 40 candidates by default. For a user who can see 30 rows out of 100,000, the policy throws all 40 away. In their run, 45 of 50 searches returned nothing.

Nothing leaked (the policy did its job), but the memory went silent. An empty result doesn't look like a bug. It looks like "no memories yet", and nobody files a ticket for that.

They followed up with the detail that mattered most: the generic plan sticks to the connection. On one prepared connection, the first five searches came back full and every one after that came back empty. And SET LOCAL plan_cache_mode = force_custom_plan overrode the cached plan at once.

Where it hit my library

memshield's built-in search adds its own filter on top of the policy, and in my measurements it kept full recall either way. The exposed path was the one that matters most for the promise: SQL with no filter of its own, relying on row-level security alone. That covers any custom SQL a user runs through memshield's transaction helper, and it is exactly the "forgot the WHERE clause" case the library exists for.

Every query already runs inside that one helper, which binds the caller's identity for the transaction. So the fix went in there, in the same statement as the identity binding: every transaction now pins plan_cache_mode to force_custom_plan, and no search path can skip it. That was the reader's suggestion too, and they were right.

I measured it at 100,000 rows on PostgreSQL 16 and 17: 20 searches on one pinned connection, for a light user, on that no-filter path. Before the fix, 10 of 20 came back empty and recall@10 was 0.50. After it, 0 of 20 were empty and recall was 1.00 on both versions.

It has a speed penalty. On PostgreSQL 16 the median went from 21 ms to 42 ms (19 to 36 ms on 17), because every search now gets the exact scan the planner picks for this user instead of a fast index scan that was returning nothing. I'll take correct answers at 42 ms over a faster median where half the searches come back empty, but you should know the trade before you copy the fix.

The test that would have lied

Sunnatulla also warned me about the test that would have lied. The obvious regression test opens a connection, runs a search and checks the results. It passes with or without the fix, because a fresh connection always gets a custom plan. The bug only appears after the same statement has run several times on the same connection.

So the test pins a pool to one connection, runs the light user well past the threshold, and asserts every result is complete. It also runs a negative control that switches the fix off inside the transaction and must see the collapse. A test that cannot fail proves nothing (and I'm a better writer of release notes than of regression tests, so I'm glad someone spelled this one out).

The second empty result nobody reported

adk-leak-arena, the companion test bench, now has a prepared-connection mode that repeats searches on one pinned connection. That mode found a second way to get the same silence. With an HNSW index at 100,000 rows, the light user's search on an unprotected table with a correct filter, and memshield's no-filter path, returned nothing from the very first execution, in 20 of 20 searches, under a custom plan. The plan logs show why: the index scan returned 40 candidates and the filter removed all 40. Plan pinning can't help there, because the planner chose that plan on purpose. memshield's built-in search got an exact plan on this data and was fine, but that's the planner's choice for this data, not a guarantee.

pgvector's iterative index scan (hnsw.iterative_scan = relaxed_order) does help in this case: it keeps walking the index until enough rows survive the filter, and with it every one of those searches came back complete. It's not a cure for the generic-plan case, where the reader measured recall of only 0.30 with it. Since it hit a plain table with a correct WHERE clause too, it's a property of filtered HNSW search, not of row-level security. memshield 0.2.1's README now recommends turning it on whenever you add an HNSW index, as a connection startup option or a role default so the pool's reset on check-in keeps it (the details and numbers). It isn't on by default, because it changes results and ordering for every query on the connection.

What else came out of one comment thread

Another reader, on LinkedIn, asked whether a caller's identity could survive on a pooled connection after a timeout or a rolled-back transaction. For rollback and timeout it couldn't, because the binding only lives for the transaction, except for a session-level setting slipped in through custom SQL. "By design" isn't a test either, so 0.2.0 resets every connection in the pools it creates when it goes back, and tests rollback, a statement timeout, a killed connection and that session-level setting. The reset adds about 0.6 ms per checkout.

The release also added signed caller identity, department-level compartments and a refusal to run on a database login that can bypass row security. adk-leak-arena now runs the same attacks against several memory stores in one run and can fail a CI build when a protected store leaks or its recall drops.

If you put vector search behind row-level security

Test the way production runs, not the way a demo runs: prepared statements, a pooled connection, the same query many times, and a user who can see almost nothing. Measure recall, not just leaks, because a security layer that returns empty results fails quietly. And when someone finds a hole in your project in public, fix it in public and put their name on it. Sunnatulla's name is in the README and the changelog, which is the least a stranger who did my testing for free deserves.

Both projects are open source: adk-memshield and adk-leak-arena.

📰 Read the original article on Dev.to AI

Originally published by Dev.to AI. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.