File and retrieval access

What you will be able to do

Keep the organization attached to the data when it leaves the tables. That means file links, the similar-ticket search, document citations and cached answers. Then prove that a guessed ID opens nothing.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

• The four checks of Authorization in the API and the two locks of Tenant-safe queries.
• Signed URLs, from Object storage patterns, and the vector search with a filter, from Vector storage.

Your project

• ~/projects/ticket-api at the snapshot end-of-m04. The provider, the API and Azurite are running. Get tokens as in the first lesson of this module: I for Ines, S for Sam, O for Omar, C for Camille.

I=$(python -m scripts.login --user usr-ines --print-access-token)

On Windows: $I = python -m scripts.login --user usr-ines --print-access-token, and type curl.exe. Make the other three tokens the same way.

A concert ticket, a library and a pigeonhole

Three everyday situations, one for each part of this lesson.

A concert ticket gets you in, whoever you are. The door staff check that it is real and for tonight. They do not check your name. So the box office must decide who gets a ticket, because after that, the ticket decides.

Two schools share one library catalogue. A pupil of the first school searches for “chemistry”. If the catalogue does not filter by school, the best match may be on the other school’s shelf. The pupil cannot borrow that book.

A post room has pigeonholes labelled only “answer to question 7”. The first person who asks question 7 gets an answer, and a copy stays in the pigeonhole. The next person who asks question 7 gets that copy, even when the right answer for them is different.

Each situation is a place where data leaves the protected tables. The API makes a file link, runs a search, cites a document and keeps a cached answer. Each one needs the organization too.

File links: check first, then sign

The databases course stores files in object storage, and the API hands out signed URLs: links with a signature and an expiry time. The storage checks only the signature and the time. Like the concert ticket, the link works for whoever holds it.

So what happens if a route signs before it checks? The leak demo builds that wrong route on purpose:

python -m scripts.leak_demo file

3. A signed download link is a key on its own (constructed: the link is made for
   Bramble Books' receipt, as a route would make it if it signed before checking):
   GET http://127.0.0.1:10000/devstoreaccount1/attachments/tickets/T-40001/e7e54964-6167-47fa-8c59-b0b14b2d7e4a/receipt-BB-310729.pdf?...  (no token, no session)
   -> 200, 724 bytes. Whoever has the link has the file until 06:34:14 UTC.

The demo fetched Bramble’s receipt with no token and no session: 200, 724 bytes. Your expiry time differs. Nobody can take the link back. It works for 5 minutes, for anyone who sees it in a log, a chat or a forwarded email.

The API’s download route does three things, in this order. It runs the four checks for file.read. It looks up the file inside the organization: WHERE tenant_id = %s AND attachment_id = %s. Only then does it sign a link, for 5 minutes. Try it with the real ID of Bramble’s receipt:

curl -H "Authorization: Bearer $I" http://127.0.0.1:8000/v1/tenants/bramble/attachments/e7e54964-6167-47fa-8c59-b0b14b2d7e4a/download

{"url":"http://127.0.0.1:10000/devstoreaccount1/attachments/tickets/T-40001/e7e54964-6167-47fa-8c59-b0b14b2d7e4a/receipt-BB-310729.pdf?se=2026-10-10T06%3A35%3A08Z&sp=r&sv=2026-10-06&sr=b&rscd=…&sig=…","expires_at":"2026-10-10T06:35:08Z"}

Ines is the owner, so she gets a link. Sam knows the same ID. He asks through his own organization:

curl -H "Authorization: Bearer $S" http://127.0.0.1:8000/v1/tenants/larkfield/attachments/e7e54964-6167-47fa-8c59-b0b14b2d7e4a/download

{"error":{"code":"not_found","message":"Attachment e7e54964-6167-47fa-8c59-b0b14b2d7e4a does not exist.","request_id":"d0f4e73a9a6c","fields":[]}}

The API makes no link. Through bramble, he gets the outsider’s Not found. of the first lesson.

A signed link is a key on its own. The API checks the caller and finds the file inside the organization before it signs; after that, only the expiry time protects the file.

Text description of the diagram

Ines asks for a download. The API checks her membership and role. It looks up the file with Bramble’s organization in the query. Only then does it sign a 5-minute link. The storage checks the signature and the time, nothing else. So anyone who gets the link can download the 724 bytes without a token, for example from a forwarded email. Sam’s request for the same ID through Larkfield never reaches the signing step: the lookup finds nothing, and the answer is 404.

New files go under a path with the organization: tenants/bramble/tickets/T-40002/<id>/photo.png. The files of the loaded data keep the databases course’s paths, tickets/T-40001/…, as the link above shows. The path does not protect anything, because the check is in the API. It helps people: a backup, a deletion or an audit can work on one organization’s folder.

The similar-ticket search: filter in the same query

Ines asks for tickets similar to her T-40001, “Charged twice for order BB-310729”. Larkfield has “Charged twice” tickets too, and their vectors are close. The demo runs the vector search without the organization, then with it:

python -m scripts.leak_demo search

1. Similar tickets of Bramble Books' T-40001, WITHOUT the organization filter (wrong):
   T-40030  0.036  bramble: Charged twice for order BB-310465
   T-40007  0.037  bramble: Charged twice for order BB-310771
   T-40004  0.042  bramble: Charged twice for order BB-310181
   T-30050  0.048  larkfield: Charged twice for order LK-710804
   T-30076  0.051  larkfield: Charged twice for order LK-140806
   WITH the filter (the API's code):
   T-40030  0.036  bramble: Charged twice for order BB-310465
   T-40007  0.037  bramble: Charged twice for order BB-310771
   T-40004  0.042  bramble: Charged twice for order BB-310181
   T-40002  0.061  bramble: Charged twice for my book order
   T-40034  0.068  bramble: Charged twice for my book order

The numbers are distances: smaller means closer in meaning. Without the filter, 2 of the 5 results are Larkfield’s. Their distances are 0.048 and 0.051. A help desk page of similar tickets would show Ines a Larkfield customer’s order. With the filter, the fourth and fifth places go to Bramble’s own tickets, which are a little further away.

The filter is in the same SQL statement as the search: e.tenant_id = %(tenant_id)s, next to the team and status filters of the databases course. Do not filter afterwards in Python. A search for 5 results that throws away 2 returns only 3. The approximate index (HNSW) finds candidates of every organization. pgvector’s iterative scan then reads on until 5 rows pass the filter. Through the API, Ines gets the five Bramble tickets:

curl -H "Authorization: Bearer $I" "http://127.0.0.1:8000/v1/tenants/bramble/tickets/T-40001/similar?k=5"

The ticket_id values are T-40030, T-40007, T-40004, T-40002 and T-40034.

Documents and citations: the organization and the access level

The help documents have two permissions. Each document belongs to one organization. And some are staff-only, such as Larkfield’s refund approval limits. The route GET /v1/tenants/{tenant}/documents?q= searches them and returns each result with its doc_id and version. Those two values are a citation: a pointer that a person or an AI assistant can quote, and that a reader can open.

Search for “refund approval” as Omar and as Sam:

curl -H "Authorization: Bearer $O" "http://127.0.0.1:8000/v1/tenants/larkfield/documents?q=refund%20approval"
curl -H "Authorization: Bearer $S" "http://127.0.0.1:8000/v1/tenants/larkfield/documents?q=refund%20approval"

The answers are long, so here are their citations:

Caller	access_level	Results (doc_id, version, access)
Omar, read_only	public	returns-policy v4 public, refunds-and-payments v3 public
Sam, staff	staff	refund-approval-procedure v2 staff, returns-policy v4 public, refunds-and-payments v3 public, warranty-claims-procedure v3 staff

Omar’s answer has no staff-only document, so it cannot cite one. This matters more than it seems. An AI assistant that writes a reply from these results can quote only what the caller may open. Imagine that the search returned the staff-only procedure. Then the assistant could leak “an agent may approve up to 100 dollars” in a sentence, without any link.

Both shops have a document called returns-policy. The organization is part of the document’s key, so they never mix. Ines searches for “returns” and gets Bramble’s returns-policy version 1, “Returning a book”. She never gets Larkfield’s version 4.

Cache keys: everything that changes the answer

The API keeps answers of the similar-ticket search and the document search for 60 seconds (CACHE_SECONDS). A cached answer skips the query, and so it skips the query’s filter. So the cache key, the label under which an answer is kept, must hold everything that changes the answer.

The demo writes two wrong caches on purpose:

python -m scripts.leak_demo cache

2a. A cache key without the organization (wrong): key = ('similar', ticket, k)
   Ines gets: ['T-30050 Charged twice for order LK-710804', 'T-30168 Charged twice for order LK-919420', 'T-30064 Charged twice for order LK-167846']
   With the organization in the key, Ines's key misses: None
2b. A key without the access level (wrong): Sam (staff) asks first, then Omar
   Omar (read-only) gets: [('refund-approval-procedure', 'staff'), ('returns-policy', 'public'), ('refunds-and-payments', 'public'), ('warranty-claims-procedure', 'staff')]

In 2a, Grace asks for tickets similar to T-30002, and her answer is kept under ('similar', 'T-30002', 3). Ines then asks for the same ticket ID through Bramble Books. She passes the membership check for her own organization. The cache answers before the query runs, and she gets Larkfield’s three tickets. In 2b, Omar gets Sam’s staff answer, with two staff-only documents.

A cache key holds everything that changes the answer: the organization first, then the access level, then the request's own values.

Text description of the diagram

Without the organization in the key, Grace’s answer and Ines’s question share one key. So Ines gets Larkfield’s tickets. With the organization first, they are two keys. Ines’s lookup misses, and her query runs inside Bramble Books. The document search adds the access level to its key, so the staff answer and the public answer stay apart.

The project’s ticket_api/cache.py makes every key with one function:

def cache_key(tenant_id: str, name: str, *parts) -> tuple:
    if not tenant_id:
        raise ValueError("a cache key of an organization's data needs the organization")
    return (tenant_id, name, *parts)

The similar-ticket route calls cache_key(caller.tenant_id, "similar", ticket_id, k, team, status, version). The document route calls cache_key(caller.tenant_id, "documents", level, q.lower()), where level is staff or public, from the caller’s role. Two more rules come with the cache. The cache comes after the four checks, so a person who loses access gets 404 even while an answer is still cached. And each answer lives only 60 seconds.

This cache lives inside one API process. A shared cache, such as Redis, follows the same rule for keys. The course Scaling APIs and AI Workloads adds one.

The whole path of a permission

Here is the whole module in one picture. The permission starts in the request, and it must reach every place that data comes from.

Permission propagation: the checked caller, with its organization and role, goes into every query, file link, search, citation and cache key. Each red box is a leak that this course measured when the organization was left out.

Text description of the diagram

A request names the organization in its path. The API checks the identity, the membership and the role and scope, and makes one checked caller: Ines, Bramble Books, owner. That caller goes into five places. The row queries carry tenant_id and row-level security. The file route looks up the file in the organization before it signs a link. The vector search filters by organization in the same query. The document search uses the organization and the access level. The cache key starts with the organization. Under each place is the leak that the course saw without it. The list had 77 of 100 tickets from Larkfield. The receipt opened for anyone with the link. 2 of 5 similar tickets came from Larkfield. Omar got two staff-only documents. A shared cache entry gave Ines Larkfield’s tickets.

Your turn: try to cross the line

You saw the worked examples. Now attack your own practice API as Camille and Omar, with made-up records. Predict each answer before you send it.

1. Camille is staff at Larkfield. They know the ID of Bramble’s receipt. Ask through Larkfield:

   curl -H "Authorization: Bearer $C" http://127.0.0.1:8000/v1/tenants/larkfield/attachments/e7e54964-6167-47fa-8c59-b0b14b2d7e4a/download

   Expected: 404, Attachment e7e54964-… does not exist. Through bramble, where they are read-only, they get a link, because read_only may file.read.

2. Camille writes a message on Bramble’s T-40001 through Larkfield, where they are staff. Expected: 404. Their staff role is for Larkfield, and T-40001 does not exist there.

3. Omar uploads a file to Larkfield’s T-30002:

   curl -X POST -H "Authorization: Bearer $O" -H "Content-Type: application/json" -d '{"file_name": "photo.png", "content_type": "image/png", "size_bytes": 1200}' http://127.0.0.1:8000/v1/tenants/larkfield/tickets/T-30002/attachments

   Expected: 403, Your role may not do this (read_only may not file.upload). The API makes no upload link.

4. Camille searches Bramble’s documents for “wholesale price”. Ines gets supplier-terms, “Wholesale terms (staff only)”. Predict Camille’s answer first: they are staff at Larkfield.

   Expected: {"access_level":"public","results":[]}. The access level comes from their role in this organization, and at Bramble Books they are read-only.

When to use it, and what it costs

Use these rules for every place that data leaves the database: files, search indexes, caches, exports and AI prompts. The cost is small. A file link waits for one more lookup. The vector search reads a few more candidates. A cache keeps one entry per organization and access level, so it hits less often. That is the right trade: a cache miss costs milliseconds, and a cache leak costs a customer’s trust.

A short link expiry also has a cost. A person on a slow connection may need a new link. Five minutes is enough to start a download, and short enough that a leaked link soon stops working.

Common mistakes

Common mistake

The download link stops working after a few minutes

Symptom: a link that worked now gets an error from the storage instead of the file. The author asked for Ines’s link at 07:32:00 UTC, and curl fetched it at once: 200 724. The author kept the url of the API’s answer in URL and fetched the same link again at 07:37:06 (your times differ; on Windows, type curl.exe):

curl -s -w '\n%{http_code}\n' "$URL"

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Error>
  <Code>AuthorizationFailure</Code>
  <Message>Server failed to authenticate the request. Make sure the value of the Authorization header is formed correctly including the signature.
RequestId:2a19621e-…
Time:2026-10-10T07:37:06.469Z</Message>
</Error>
403

Cause: the link expired at 07:37:00 (expires_at in the API’s answer). That is by design: it lives 5 minutes. The message talks about a header, but the request had none. Only the expiry time in the link had passed. This answer is from Azurite; real Azure Storage may word it differently (not tested).

Fix: ask the API for a new link. The API runs the checks again, so a person who lost access gets 404 instead of a link. In the same run, Ines asked again at 07:37:06 and got a new link, "expires_at":"2026-10-10T07:42:06Z", and curl fetched it: 200 724.

Common mistake

Fewer similar tickets than you asked for

Symptom: a search for 5 similar tickets returns 3, but the organization has more tickets of that kind.

Cause: a filter applied after the search. The search found 5 neighbours, and the code threw away the ones of another organization.

Fix: put the organization in the same SQL statement as the vector search, as repository.similar_tickets does. Then the database reads on until enough rows pass the filter.

Common mistake

A cache key built by hand

Symptom: a new cached route works in every test with one shop. With two shops, one person sometimes sees another organization’s answer, but only within the cache’s 60 seconds.

Cause: the key was a tuple built by hand, such as ("export", ticket_id), with no organization.

Fix: build every key with cache_key(caller.tenant_id, …), and add the access level when the role changes the answer. cache_key("", …) raises ValueError, so a missing organization fails loudly.

Reset. The demos change nothing. The API refuses step 3, so it adds nothing. If you uploaded files of your own, run python -m scripts.load --reset.

See it end to endAudit access control in an open-source multi-tenant appCase study

The case study looks for these same leaks in another application: files, search and caches.

Knowledge check

Check your understanding

Explain why the API must check the caller before it signs a download link, and not only when the link is used.

Show the answer

The storage checks only the signature and the expiry time of a link, not the person. Once the API signs a link, anyone who has it can download the file until it expires, without a token. The demo fetched Bramble’s receipt with no token: 200, 724 bytes. So the decision must happen before the signature: the four checks, then a lookup of the file inside the organization.

Check your understanding

Predict: the document route's cache key is (tenant, 'documents', level, query). Omar searches Larkfield's documents for 'refund approval' 10 seconds after Sam did. Does Omar get a cache hit? What does he get?

Show the answer

No hit. Sam’s answer is under ('larkfield', 'documents', 'staff', 'refund approval'). Omar is read-only, so his key is ('larkfield', 'documents', 'public', 'refund approval'). The API runs his search and returns 2 public documents, returns-policy and refunds-and-payments.

Check your understanding

Module check: prove that access is denied even when a valid record ID is guessed. Use a real ticket ID and a real attachment ID of Bramble Books, and Sam's token. Which requests do you send, what does each answer, and which tests check the same thing?

Show the answer

The IDs T-40001 and e7e54964-6167-47fa-8c59-b0b14b2d7e4a are real Bramble records.

• GET /v1/tenants/larkfield/tickets/T-40001: 404, “Ticket T-40001 does not exist.”, the same as for the missing T-49999.
• GET /v1/tenants/bramble/tickets/T-40001: 404, “Not found.”, the same as for a made-up organization.
• GET /v1/tenants/larkfield/attachments/e7e54964-…/download: 404, “Attachment … does not exist.”, and the API makes no link.
• python -m scripts.attempts ends with 0 of 10 attempts got what they should not get.
• python -m pytest tests/test_access_control.py tests/test_tenant_queries.py tests/test_row_security.py passes 25 tests, among them test_another_organizations_ticket_looks_like_a_ticket_that_does_not_exist. The database adds a second proof: as ticket_app for Larkfield, SELECT count(*) FROM tickets WHERE ticket_id = 'T-40001' is 0.

Recap

• A signed link works for anyone until it expires. So the API checks the caller and finds the file inside the organization before it signs. New files go under tenants/<organization>/.
• The similar-ticket search filters by organization in the same SQL statement. Without it, 2 of 5 results for Bramble’s T-40001 were Larkfield’s.
• Document results carry the organization and the access level, and their citations (doc_id, version) point only to documents that the caller may open.
• A cache key holds everything that changes the answer: organization, access level, then the request. The cache comes after the checks.

Module recap. Every route is under its organization and runs four checks: identity, membership, role and scope, and the record in the organization. Outsiders get the same 404 for real and made-up organizations; members get 403. Every query takes the organization as a required argument, and row-level security is a second lock. Files, search, citations and caches carry the organization too. scripts.attempts: 0 of 10. Your project matches end-of-m04.

Next, the work that runs later: background jobs must remember who asked, for which organization, and check both again when they run.

Next: Job identity context