This page describes how row-level-access can be implemented in an AI Chatbot or MCP Server that uses SQL queries to retrieve data (conversational analytics), such as when using Text-to-SQL and RAG.

User authentication

In order to apply row-level permissions, the user from the AI Chatbot or MCP Server must be authenticated and known to Peliqan.

MCP Server: user authentication using oAuth

AI Chatbot: user authentication in n8n

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 MCP Server or AI Chatbot 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 payments:     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 payments_view AS ...

Example original query from the AI Agent or MCP client:

SELECT SUM(amount) FROM invoices;

Final execute 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 payments   AS (SELECT * FROM payments_view  WHERE allowed_role IN ({user_roles}))
SELECT SUM(amount) FROM invoices;