This page describes how row-level-access can be implemented in an AI Agent that uses SQL queries to retrieve data, such as when using Text-to-SQL and RAG.

Example setup

In order to apply permissions, the user id must be captured from the chat. Here’s an example setup in n8n:

• Parent webpage with login screen
• After login, send user_id into n8n Chat Embed Widget
• Retrieve user_id in n8n workflow
• Execute SQL query (for Text-to-SQL and RAG) via a Peliqan custom API endpoint
• Make sure the user_id is also sent to the API endpoint
• Peliqan applies permissions (row level access) in the API handler script
• Peliqan executes the query and sends the result back to the AI Agent

Applying row-level permissions in queries

Row level access is implemented by providing the AI Agent with a set of views, making sure each view has a fixed column on which permissions can be applied (e.g. user_role).

The views are invoked with a filter (e.g. filter on user_role) and then prepended to the SQL query that the AI Agent wants to execute.

In the below example, we assume that users can have multiple roles, user roles have permissions on departments and that all resources are linked to one department.

Example description of the Data model in the System prompt of the AI Agent:

* Table invoices:     id, amount, date, department etc.
* Table customers:    id, name, department etc.
* Table transactions: id, amount, department etc.
* etc.

Example table to store permissions:

Example table to store user roles:

Example views to apply permissions:

CREATE VIEW invoices_view AS
   SELECT 
       invoices.*,
       permissions.allowed_role
   FROM invoices
   INNER JOIN permissions p ON invoices.department = permissions.department;

CREATE VIEW customers_view AS ...
CREATE VIEW transactions_view AS ...

Example original query from AI Agent:

SELECT SUM(amount) FROM invoices;

Final query with all views prepended and permissions applied:

WITH invoices AS (SELECT * FROM invoices_view WHERE allowed_role IN ({user_roles}))
WITH customers AS (SELECT * FROM customers_view WHERE allowed_role IN  {user_roles})
WITH transactions AS (SELECT * FROM transactions_view WHERE allowed_role IN {user_roles})
SELECT SUM(amount) FROM invoices;