Tagged postgres
8 pieces
- · 9 min read
A Prompt Is Not a Lock: Three Ways Row-Level Security Leaks Even When the Rule Is Written Correctly
When an assistant answers questions about live records, the engineering is the permission boundary, not the retrieval. In a Postgres demonstrator with a correctly written row-level security policy, 3 of 8 permission checks still returned another person's full record. All three leaks came from below the policy: an owner-login exemption, a view running as its definer, and a session variable surviving in a pooled connection.
- · 7 min read
Read-Only Is Not a Safety Boundary: It Protects Your Data From the Agent, Not Your Users From It
Giving an agent a read-only database role stops it writing. It does nothing about what it can see, because Postgres owner roles and the pg_read_all_data role bypass row-level security by default. If your product's promise is that one party cannot see another party's identity until they release it, that promise has to be enforced in the database, and read-only does not enforce it.
- · 7 min read
Your Hybrid Search Is Silently Dense-Only, and Reciprocal Rank Fusion Will Not Tell You
The widely copied hybrid search recipe pairs a vector index with Postgres full-text search and fuses the two with reciprocal rank fusion. Run a Chinese query against it and the full-text side returns zero rows, because there is no Chinese-capable text search configuration. Fusion then produces the dense-only ordering, with no error and no warning, and every test that checks for results still passes.
- · 11 min read
The RLS Trap That Passes Every Test: A Postgres View Runs as Its Definer Unless You Say Otherwise
A Postgres view executes with its definer's privileges unless security_invoker is set, and that setting is off by default. Every view created before somebody thought about row-level security is therefore in the wrong mode, and the usual test, that a user sees their own data, passes identically in both modes. This explains the semantics, the migration problem, and the audit query that tells you which of your views are routing around the policy.
- · 8 min read
You Asked for 40 Rows and Got 4: What a Filter Does to an HNSW Vector Search
Add a WHERE clause to a vector search and the index does not search harder to compensate. It walks its usual neighbourhood, most of what it finds fails your filter, and you get a fraction of the rows you asked for. At a 10 percent selective filter on default ef_search, a reproduction on pgvector returned 3.93 rows on average against a request for 40. No error, no warning, just a short list that looks like a thin corpus.
- · 7 min read
Two Postgres Queues, Opposite Failures, Same Drill: Know Which Way Yours Breaks
Kill a worker mid-job and the two most common database-backed job queues behave in opposite ways. One re-runs the job after a fixed interval whether the worker is alive or dead, which duplicates work on a slow task. The other holds a dead worker's job for hours, which stalls it. Neither default is wrong, and inheriting one without knowing which you have is how a queue becomes an outage.
- · 11 min read
Concurrency Belongs at the Write, Not at the Read
Under ordinary database isolation levels two concurrent requests can both read the same available balance, both find it sufficient, and both commit, leaving the balance negative. Checking availability before the write cannot prevent it, because the check and the write are separate decisions taken against a stale view. Enforce the constraint where the write lands: a uniqueness constraint, a conditional update, or explicit locking.
- · 11 min read
The Column List Matters as Much as the Rule: Row Policies Do Not Constrain Which Fields a User Can Write
A row-level security policy that lets a user update their own row is correct for a display name and catastrophic for a plan tier or a role. The policy decides which rows a write may touch. The damage comes from which columns. Self-escalation is a class of defect rather than a bug, and the controls that address it are column-level grants, a schema split between user-editable and system-owned fields, and an audit that fails when a new column appears.