RAG Role-Based Access Control: Permission-Aware Retrieval in SQL

Objective

A RAG chatbot answers from whatever the vector search returns. Point it at your company docs and an intern who asks "How much does a senior engineer get paid?" gets the HR salary bands, word for word. The model did nothing wrong: retrieval handed it a document the intern was never allowed to read.

RAG role-based access control fixes this at the retrieval step. Every chunk carries the group that may read it, users inherit access through nested groups, and the vector search joins against the user's effective permissions before anything reaches the LLM. In SynapCores the documents, the embeddings, the group graph, the generation call and the audit log live in one database, so the permission check is a single JOIN in the same query that ranks by meaning. It works the same from any framework or agent; see Use it from your agent at the end.

Step 1: Create the knowledge base with an access label on every chunk

Each chunk stores its text, its embedding, and acl_group: the group allowed to read it.

CREATE TABLE IF NOT EXISTS rbac_kb (
  chunk_id   INTEGER PRIMARY KEY,
  doc_ref    TEXT,
  acl_group  TEXT,                                    -- who may read this chunk
  content    TEXT,
  embedding  VECTOR(384)
);

Step 2: Load company documents with different sensitivity

Handbook pages for everyone, an engineering wiki, HR salary bands and case notes, and a board deck.

INSERT INTO rbac_kb (chunk_id, doc_ref, acl_group, content) VALUES
 (1,'Handbook §2','all_staff','Core collaboration hours are 10am to 3pm Eastern; the rest of the day is flexible.'),
 (2,'Handbook §5','all_staff','Engineering career levels run from L1 to L6. Promotion reviews happen every April and October.'),
 (3,'Handbook §7','all_staff','Questions about your own pay go to your manager or the HR helpdesk at hr@acme.example.'),
 (4,'Eng Wiki: Releases','engineering','Production releases ship through the release pipeline every weekday at 2pm. Hotfixes need on-call approval.'),
 (5,'Comp Bands 2026','hr_managers','The 2026 salary band for a Senior Software Engineer (L5) is $182,000 to $228,000 base.'),
 (6,'Comp Bands 2026','hr_managers','The 2026 salary band for a Staff Engineer (L6) is $215,000 to $265,000 base.'),
 (7,'HR Case Notes','hr_managers','Two engineers on the platform team are on performance improvement plans through December.'),
 (8,'Board Deck Q4','finance_exec','The Q4 plan freezes new senior engineering hires until March and cuts the contractor budget by 15%.');

Step 3: Embed the knowledge base

The embedding model runs inside the database. One statement indexes every chunk.

UPDATE rbac_kb SET embedding = EMBED(content);

Step 4: See the leak: plain vector search ignores permissions

This is what most RAG tutorials do. The top two hits for the intern's question are the hr_managers salary bands.

SELECT chunk_id, doc_ref, acl_group,
       COSINE_SIMILARITY(embedding, EMBED('How much does a senior engineer get paid?')) AS relevance
FROM rbac_kb
ORDER BY relevance DESC
LIMIT 3;

Feed those passages to the LLM and it answers with the exact salary range:

CREATE TABLE IF NOT EXISTS rbac_naive_ctx (id INTEGER PRIMARY KEY, sources TEXT);
INSERT INTO rbac_naive_ctx (id, sources)
SELECT 1, GROUP_CONCAT('(' || doc_ref || ') ' || content, ' ')
FROM (SELECT doc_ref, content,
             COSINE_SIMILARITY(embedding, EMBED('How much does a senior engineer get paid?')) AS relevance
      FROM rbac_kb ORDER BY relevance DESC LIMIT 3) AS top3;

SELECT GENERATE(
  'You are the company assistant. Answer using ONLY these sources. If the sources do not contain the answer, say you cannot share that and point to who can help. Sources: ' ||
  sources || ' Question: How much does a senior engineer get paid?') AS answer_to_the_intern
FROM rbac_naive_ctx;

Step 5: Model users and nested groups

rbac_membership holds both kinds of edge: a user in a group, and a group inside a parent group. Priya is an engineering intern, Marcus is an HR manager, Dana is on the finance exec team.

CREATE TABLE IF NOT EXISTS rbac_membership (
  member      TEXT,                                   -- a user or a group
  group_name  TEXT
);
INSERT INTO rbac_membership (member, group_name) VALUES
 ('priya','engineering'),
 ('marcus','hr_managers'),
 ('dana','finance_exec'),
 ('engineering','all_staff'),
 ('hr_managers','hr'),
 ('hr','all_staff'),
 ('finance_exec','finance'),
 ('finance','all_staff');

Step 6: Resolve each user's effective groups with one recursive query

A recursive CTE walks the group hierarchy, so Marcus inherits hr and all_staff from hr_managers. The result is stored in rbac_access, the table every retrieval joins against.

CREATE TABLE IF NOT EXISTS rbac_access (
  user_id  TEXT,
  grp      TEXT
);
INSERT INTO rbac_access (user_id, grp)
WITH RECURSIVE reach(user_id, grp) AS (
  SELECT member, group_name FROM rbac_membership WHERE member IN ('priya','marcus','dana')
  UNION
  SELECT r.user_id, m.group_name FROM rbac_membership m JOIN reach r ON m.member = r.grp
)
SELECT user_id, grp FROM reach;

SELECT user_id, grp AS can_read FROM rbac_access ORDER BY user_id, grp;

Step 7: Permission-aware retrieval: the same search, one JOIN added

The vector search only ranks chunks whose acl_group the user can read. Run it as Priya, then as Marcus.

SELECT k.chunk_id, k.doc_ref, k.acl_group,
       COSINE_SIMILARITY(k.embedding, EMBED('How much does a senior engineer get paid?')) AS relevance
FROM rbac_kb k
JOIN rbac_access a ON k.acl_group = a.grp
WHERE a.user_id = 'priya'
ORDER BY relevance DESC
LIMIT 3;

SELECT k.chunk_id, k.doc_ref, k.acl_group,
       COSINE_SIMILARITY(k.embedding, EMBED('How much does a senior engineer get paid?')) AS relevance
FROM rbac_kb k
JOIN rbac_access a ON k.acl_group = a.grp
WHERE a.user_id = 'marcus'
ORDER BY relevance DESC
LIMIT 3;

Step 8: Generate each user's answer from only what they may read

Freeze each user's permitted top-3, build one context string per user, and call GENERATE() once per row.

CREATE TABLE IF NOT EXISTS rbac_top (
  user_id    TEXT,
  chunk_id   INTEGER,
  doc_ref    TEXT,
  content    TEXT,
  relevance  DOUBLE
);
INSERT INTO rbac_top (user_id, chunk_id, doc_ref, content, relevance)
SELECT a.user_id, k.chunk_id, k.doc_ref, k.content,
       COSINE_SIMILARITY(k.embedding, EMBED('How much does a senior engineer get paid?')) AS relevance
FROM rbac_kb k JOIN rbac_access a ON k.acl_group = a.grp
WHERE a.user_id = 'priya'
ORDER BY relevance DESC
LIMIT 3;
INSERT INTO rbac_top (user_id, chunk_id, doc_ref, content, relevance)
SELECT a.user_id, k.chunk_id, k.doc_ref, k.content,
       COSINE_SIMILARITY(k.embedding, EMBED('How much does a senior engineer get paid?')) AS relevance
FROM rbac_kb k JOIN rbac_access a ON k.acl_group = a.grp
WHERE a.user_id = 'marcus'
ORDER BY relevance DESC
LIMIT 3;

CREATE TABLE IF NOT EXISTS rbac_ctx (user_id TEXT PRIMARY KEY, sources TEXT);
INSERT INTO rbac_ctx (user_id, sources)
SELECT user_id, GROUP_CONCAT('(' || doc_ref || ') ' || content, ' ')
FROM rbac_top
GROUP BY user_id;

SELECT user_id, GENERATE(
  'You are the company assistant. Answer using ONLY these sources. If the sources do not contain the answer, say you cannot share that and point to who can help. Sources: ' ||
  sources || ' Question: How much does a senior engineer get paid?') AS answer
FROM rbac_ctx
ORDER BY user_id;

Step 9: Log what every user was shown

Security teams ask "who saw the salary bands?" The audit trail is one INSERT from the rows you already retrieved.

CREATE TABLE IF NOT EXISTS rbac_audit (
  user_id    TEXT,
  question   TEXT,
  chunk_id   INTEGER,
  doc_ref    TEXT,
  logged_at  TIMESTAMP
);
INSERT INTO rbac_audit (user_id, question, chunk_id, doc_ref, logged_at)
SELECT user_id, 'How much does a senior engineer get paid?', chunk_id, doc_ref, CURRENT_TIMESTAMP
FROM rbac_top;

SELECT user_id, doc_ref, COUNT(*) AS chunks_served
FROM rbac_audit
GROUP BY user_id, doc_ref
ORDER BY user_id, doc_ref;

And the reverse question, "who can read this document?", is the same join turned around:

SELECT DISTINCT a.user_id AS can_read_comp_bands
FROM rbac_access a
JOIN rbac_kb k ON k.acl_group = a.grp
WHERE k.doc_ref = 'Comp Bands 2026';

Step 10: Revoke access, with no re-embedding

Marcus moves from HR management to a general HR role. Update his membership, refresh rbac_access, and his next search no longer returns the salary bands. The embeddings are untouched because permissions live next to the vectors, not inside a copy of them.

DELETE FROM rbac_membership WHERE member = 'marcus' AND group_name = 'hr_managers';
INSERT INTO rbac_membership (member, group_name) VALUES ('marcus','hr');
DELETE FROM rbac_access;
INSERT INTO rbac_access (user_id, grp)
WITH RECURSIVE reach(user_id, grp) AS (
  SELECT member, group_name FROM rbac_membership WHERE member IN ('priya','marcus','dana')
  UNION
  SELECT r.user_id, m.group_name FROM rbac_membership m JOIN reach r ON m.member = r.grp
)
SELECT user_id, grp FROM reach;

SELECT k.chunk_id, k.doc_ref, k.acl_group,
       COSINE_SIMILARITY(k.embedding, EMBED('How much does a senior engineer get paid?')) AS relevance
FROM rbac_kb k
JOIN rbac_access a ON k.acl_group = a.grp
WHERE a.user_id = 'marcus'
ORDER BY relevance DESC
LIMIT 3;

Cleanup (Optional)

DROP TABLE IF EXISTS rbac_kb;
DROP TABLE IF EXISTS rbac_naive_ctx;
DROP TABLE IF EXISTS rbac_membership;
DROP TABLE IF EXISTS rbac_access;
DROP TABLE IF EXISTS rbac_top;
DROP TABLE IF EXISTS rbac_ctx;
DROP TABLE IF EXISTS rbac_audit;

Expected Outcomes

• Step 4 ranks the two Comp Bands 2026 chunks first (relevance about 0.63 and 0.52), and the naive answer quotes the $182,000 to $228,000 band to anyone who asks.
• Step 6 gives Priya engineering + all_staff, Marcus hr_managers + hr + all_staff, and Dana finance_exec + finance + all_staff.
• Step 7 returns only handbook and engineering-wiki chunks for Priya; Marcus still gets the salary bands.
• Step 8 Priya's answer declines and points her to her manager or the HR helpdesk; Marcus gets the L5 band.
• Step 9 the audit shows Marcus was served Comp Bands 2026 twice and Priya never; only marcus can read that document.
• Step 10 after the role change, Marcus's top results are handbook pages only.

Each answer now comes only from documents the person asking is allowed to read.

Use it from your agent (framework-agnostic)

Permission-aware RAG is a labeled chunk table + an access table + one JOIN, so any agent can use it:

• REST / SDK: POST /v1/query/execute from any language, or @synapcores/sdk client.executeQuery(...). Your app passes the signed-in user's id into the Step-7 query; the agent never sees the filter, so a prompt cannot talk its way around it.
• MCP (native, on by default): point any MCP client (Claude Code, Cursor, a custom loop, a voice runtime) at ws://<your-instance>/mcp?token=<jwt>. Issue the token per user so every query tool call runs the permission-aware retrieval for that user.
• Any framework: OpenClaw, LangChain / LlamaIndex retrievers, or a voice agent all call the same query. The database enforces access; the framework is swappable.

Key Concepts Learned

• Put the access label (acl_group) on every chunk so permissions travel with the evidence.
• Resolve nested groups once with WITH RECURSIVE and store the result in an access table.
• Enforce access in the retrieval query with a JOIN, before the LLM sees any text.
• Write an audit row for every chunk served, and answer "who can read this?" with the same join.
• Revoking access is a membership change, not a re-index: the vectors stay where they are.
• Related: RAG with citations and RBAC effective permissions through group inheritance.