Supabase developer on retainer: pgvector inserts silently ignored — RLS enabled with no policy — every SELECT returns 0 rows — 3 weeks — Supabase pgvector developer on monthly retainer

Supabase PostgreSQL with pgvector • supabase-py 2.4.x • Python 3.11 • documents table • ENABLE ROW LEVEL SECURITY toggled on • zero CREATE POLICY statements • every INSERT via anon key returns data=[] • every SELECT returns data=[] • no error raised • service_role key works fine — masks the gap • 3 weeks • fix: CREATE POLICY allow_all ON documents FOR ALL USING (true) WITH CHECK (true) • 2.0h retainer engagement

Northfield SaaS Platform Ltd. built a document semantic search feature in September 2026 using pgvector on Supabase. The ingestion pipeline called supabase-py's .insert() method, received a 201 response, and moved on. The vector similarity search endpoint always returned an empty list. The developer spent three weeks checking the .rpc() call syntax, vector dimensions, embedding model output, and pgvector index configuration. Every call returned an empty result set. They engaged a Supabase retainer developer.

The ingestion and search code at the time of the engagement:

# vector_store.py
# Northfield SaaS Platform Ltd.
# supabase-py 2.4.x, Python 3.11, Supabase PostgreSQL + pgvector

from supabase import create_client, Client

SUPABASE_URL = "https://xyzcompanyid.supabase.co"
SUPABASE_ANON_KEY = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."

def get_client() -> Client:
    # Uses the anon key — maps to the 'anon' PostgreSQL role
    # The anon role is subject to Row Level Security (RLS)
    # If RLS is enabled on a table with no policies, anon gets DENY ALL
    return create_client(SUPABASE_URL, SUPABASE_ANON_KEY)

def insert_documents(documents: list[dict]) -> int:
    client = get_client()
    response = client.table("documents").insert([
        {
            "id": doc["id"],
            "title": doc["title"],
            "content": doc["content"],
            "embedding": doc["embedding"],   # list[float], 1536 dims
        }
        for doc in documents
    ]).execute()
    # response.status_code == 201  ← HTTP success: the request reached Supabase
    # response.data == []          ← 0 rows inserted: RLS blocked the INSERT silently
    # No exception raised. No error message. The developer read status_code=201 and moved on.
    return len(response.data)      # ← returns 0, not len(documents)

def search_documents(query_embedding: list[float], top_k: int = 10) -> list[dict]:
    client = get_client()
    response = client.rpc(
        "match_documents",
        {
            "query_embedding": query_embedding,
            "match_count": top_k,
        }
    ).execute()
    # response.data == []   ← empty: documents table has 0 rows visible to anon role
    return response.data

# The stored function called by .rpc():
#
# CREATE OR REPLACE FUNCTION match_documents(
#     query_embedding vector(1536),
#     match_count int
# )
# RETURNS TABLE (id uuid, title text, content text, similarity float)
# LANGUAGE sql SECURITY INVOKER AS $$
#     SELECT id, title, content,
#            1 - (embedding <=> query_embedding) AS similarity
#     FROM documents
#     ORDER BY embedding <=> query_embedding
#     LIMIT match_count;
# $$;
#
# SECURITY INVOKER means the function runs as the calling user (anon role)
# RLS applies to the SELECT inside the function
# anon role + RLS enabled + no policies = 0 rows returned
# No error is raised by PostgreSQL — the query is valid, it just returns nothing

# Internal test that always passed:
#
# def test_insert_and_search():
#     # Developer used SUPABASE_SERVICE_ROLE_KEY for tests
#     admin_client = create_client(SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY)
#     # service_role key bypasses RLS entirely
#     admin_client.table("documents").insert([{"id": ..., "embedding": [...]}]).execute()
#     # INSERT succeeds: service_role is exempt from RLS
#     results = admin_client.rpc("match_documents", {...}).execute()
#     # SELECT succeeds: service_role bypasses RLS
#     assert len(results.data) > 0   # passes — service_role sees all rows
#     # Bug: production uses anon key, not service_role key

Why RLS with no policy silently blocks everything

PostgreSQL's Row Level Security is an additive permission system. When you enable RLS on a table (ALTER TABLE documents ENABLE ROW LEVEL SECURITY or the Supabase dashboard "Enable RLS" toggle), you are telling PostgreSQL to check policies before every DML operation. The critical detail: if no policies exist, PostgreSQL's default is DENY ALL for any role other than the table owner or a superuser.

Supabase maps the anon key to the anon PostgreSQL role and authenticated user JWTs to the authenticated role. Both roles are subject to RLS. With RLS enabled and no policies on the documents table, every INSERT from supabase-py using the anon key inserts zero rows. PostgreSQL does not raise an error — it silently filters out all rows. The supabase-py client receives an HTTP 201 response (the HTTP request was valid), but the data field is an empty array.

The deception compounds because the Supabase service_role key is designed to bypass RLS entirely, allowing administrative operations. Any test that uses the service_role key will succeed even when RLS would block the anon key — the two keys operate in completely separate security contexts. A test suite that uses service_role for setup and anon for assertions can pass while the production application (using only the anon key) gets nothing back.

# Diagnosing the RLS gap — SQL queries to run in the Supabase SQL editor

-- Check which tables have RLS enabled
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;
-- documents | t    ← RLS is on

-- Check which policies exist on the documents table
SELECT policyname, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'documents' AND schemaname = 'public';
-- (0 rows)   ← No policies! RLS on + 0 policies = DENY ALL for anon/authenticated roles

-- Verify the anon role sees nothing
SET ROLE anon;
SELECT COUNT(*) FROM documents;
-- count: 0   ← confirmed: anon role blocked by RLS with no policies
RESET ROLE;

-- Verify the service_role sees everything
SET ROLE service_role;
SELECT COUNT(*) FROM documents;
-- count: 500   ← service_role bypasses RLS, sees all rows
RESET ROLE;
-- This explains why the admin test passed and production failed

The fix: CREATE POLICY for the intended access pattern

-- Option A: Open access policy (for public search, no user scoping)
-- Allows anon and authenticated roles to SELECT, INSERT, UPDATE, DELETE all rows
-- Appropriate for: public document search where all documents are visible to everyone

CREATE POLICY allow_all ON documents
    FOR ALL
    USING (true)
    WITH CHECK (true);

-- After adding this policy, the anon key can INSERT and SELECT freely.
-- USING (true): SELECT filter — return all rows (no restriction)
-- WITH CHECK (true): INSERT/UPDATE check — allow all rows to be written

-- Verify:
SELECT policyname, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'documents' AND schemaname = 'public';
-- allow_all | ALL | true | true   ← policy exists

SET ROLE anon;
SELECT COUNT(*) FROM documents;
-- count: 500   ← anon role now sees all rows
-- Option B: User-scoped policy (for multi-tenant SaaS — each user sees only their documents)
-- Documents table must have a user_id column

CREATE POLICY user_owns_documents ON documents
    FOR ALL
    USING (auth.uid() = user_id)
    WITH CHECK (auth.uid() = user_id);

-- auth.uid() returns the UUID of the authenticated user from the JWT
-- With this policy, each user can only SELECT, INSERT, UPDATE, DELETE their own rows
-- The anon role gets nothing (auth.uid() returns null for unauthenticated requests)

-- For the pgvector similarity search function with user scoping:
CREATE OR REPLACE FUNCTION match_documents(
    query_embedding vector(1536),
    match_count int
)
RETURNS TABLE (id uuid, title text, content text, similarity float)
LANGUAGE sql SECURITY INVOKER AS $$
    -- SECURITY INVOKER: runs as the calling user; RLS applies
    -- The user_owns_documents policy filters to rows where user_id = auth.uid()
    SELECT id, title, content,
           1 - (embedding <=> query_embedding) AS similarity
    FROM documents
    ORDER BY embedding <=> query_embedding
    LIMIT match_count;
$$;
# vector_store.py — FIXED
# supabase-py 2.4.x, Python 3.11, Supabase PostgreSQL + pgvector

from supabase import create_client, Client

SUPABASE_URL = "https://xyzcompanyid.supabase.co"
SUPABASE_ANON_KEY = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."

def get_client() -> Client:
    return create_client(SUPABASE_URL, SUPABASE_ANON_KEY)

def insert_documents(documents: list[dict]) -> int:
    client = get_client()
    response = client.table("documents").insert([
        {
            "id": doc["id"],
            "title": doc["title"],
            "content": doc["content"],
            "embedding": doc["embedding"],
        }
        for doc in documents
    ]).execute()
    # After adding CREATE POLICY allow_all ON documents FOR ALL USING (true) WITH CHECK (true):
    # response.status_code == 201
    # response.data == [{"id": ..., "title": ..., ...}, ...]   ← 500 rows returned
    if len(response.data) == 0:
        raise RuntimeError(
            "Insert returned 0 rows — check RLS policies on documents table. "
            "Run: SELECT policyname FROM pg_policies WHERE tablename = 'documents';"
        )
    return len(response.data)

# RLS smoke test: run at application startup
def assert_rls_policy_allows_anon_key(supabase_url: str, anon_key: str) -> None:
    """
    Inserts a canary row using the anon key, then retrieves it with a new client.
    Fails if RLS is blocking the anon key (policy missing or too restrictive).
    """
    import uuid
    canary_id = str(uuid.uuid4())
    client_a = create_client(supabase_url, anon_key)

    # Insert canary row
    insert_resp = client_a.table("documents").insert({
        "id": canary_id,
        "title": "_rls_canary_",
        "content": "_startup_check_",
        "embedding": [0.0] * 1536,
    }).execute()

    if len(insert_resp.data) == 0:
        raise RuntimeError(
            "RLS smoke test FAILED: INSERT returned 0 rows using the anon key. "
            "Likely cause: RLS is enabled on 'documents' table with no permissive policy. "
            "Fix: CREATE POLICY allow_all ON documents FOR ALL USING (true) WITH CHECK (true);"
        )

    # Retrieve canary row using a fresh client
    client_b = create_client(supabase_url, anon_key)
    select_resp = client_b.table("documents").select("id").eq("id", canary_id).execute()

    # Clean up canary row
    client_b.table("documents").delete().eq("id", canary_id).execute()

    if len(select_resp.data) == 0:
        raise RuntimeError(
            "RLS smoke test FAILED: canary row not found in SELECT using anon key. "
            "Check pg_policies for 'documents' table."
        )

    print("[OK] RLS smoke test passed: anon key can INSERT and SELECT on documents table.")

# Call at application startup
assert_rls_policy_allows_anon_key(SUPABASE_URL, SUPABASE_ANON_KEY)

Using SECURITY DEFINER for public vector search

For a document search feature where all documents should be publicly readable (no user scoping), the simplest production pattern combines an open SELECT policy with a SECURITY DEFINER stored function for the vector similarity search. The SECURITY DEFINER attribute makes the function run as the function owner (typically the Supabase postgres superuser), bypassing RLS entirely for that function's query — useful when the RLS policy is complex or when you want to expose a read-only search endpoint without granting the anon role broad table access.

-- SECURITY DEFINER function for public search (bypasses RLS for this query only)
-- Use when: documents are all publicly searchable, but you don't want to open
-- a broad SELECT policy on the table for other queries

CREATE OR REPLACE FUNCTION match_documents_public(
    query_embedding vector(1536),
    match_count int DEFAULT 10
)
RETURNS TABLE (id uuid, title text, content text, similarity float)
LANGUAGE sql
SECURITY DEFINER   -- runs as the function owner, not the calling user
SET search_path = public
AS $$
    SELECT id, title, content,
           1 - (embedding <=> query_embedding) AS similarity
    FROM documents
    ORDER BY embedding <=> query_embedding
    LIMIT match_count;
$$;

-- Grant execute to the anon role (required — anon must be able to call the function)
GRANT EXECUTE ON FUNCTION match_documents_public(vector, int) TO anon;

-- The anon role can now call this function and get results
-- even without a SELECT policy on the documents table.
-- For INSERT, UPDATE, DELETE: still need explicit policies.

-- Trade-off: SECURITY DEFINER bypasses all RLS for the function's query.
-- Never use SECURITY DEFINER for user-scoped functions — it would let every user
-- see every other user's documents.

Validation

# Validation log — October 31 2026
# supabase-py 2.4.x, Python 3.11, Supabase PostgreSQL, pgvector

# ---- Before fix ----
# pg_policies for documents: 0 rows   ← no policies
# insert_documents() with 500 docs via anon key: response.data = []   ← 0 rows inserted
# search_documents() via anon key: response.data = []   ← 0 rows visible
# Same operations via service_role key: all 500 rows inserted and returned   ← masked the gap

# ---- After fix: CREATE POLICY allow_all ON documents FOR ALL USING (true) WITH CHECK (true) ----
# pg_policies for documents: 1 row (allow_all, ALL, true, true)
# assert_rls_policy_allows_anon_key(): [OK] RLS smoke test passed
# insert_documents() with 500 docs via anon key: response.data = [{...} x 500]   ← 500 rows inserted
# search_documents() via anon key: response.data = [{...} x 10]   ← top 10 results returned
# SECURITY DEFINER match_documents_public(): response.data = [{...} x 10]   ← also works

# ---- Hour breakdown ----
# Triage: 0.5h — ran pg_policies query; confirmed 0 policies on documents table
# Root cause: 0.25h — confirmed RLS enabled on table with no permissive policy for anon role
# Fix: 0.75h — CREATE POLICY allow_all; update stored function to SECURITY DEFINER; add RLS smoke test
# Validation: 0.5h — insert 500 docs; search returns results; canary smoke test passes at startup
# Total: 2.0h

# Retainer engagement cost: 2.0h (estimated $110-220 at mid-level rates)

Related retainer posts

Track retainer hours without a client portal

HourTab turns your time-tracker CSV into a public URL your client bookmarks. No login. No portal. CSV in, URL out.

See pricing →