This page describes how row-level-access can be implemented in Peliqan, for example in a Data App, in an AI Chatbot, in an MCP Server or in any other client that uses SQL queries to retrieve data (a.k.a. conversational analytics).

Example clients using row-level data access:

• Data apps
• MCP Servers with Text-To-SQL
• AI Chatbots

User authentication

In order to apply row-level permissions, each individual user in the client must be authenticated and known to Peliqan. This means that e.g. a shared API key cannot be used. Instead SSO (Single Sign On) with e.g. oAuth is appropriate.

User authentication in Data Apps

More info:

User authentication in MCP Servers using oAuth

More info:

User authentication in AI Chatbots

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

• 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 client 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. a filter on user_role) and then prepended to the SQL query that the client wants to execute as CTEs.

Example with user mappings

Coming soon.

Example with group mappings

In this example, we assume that users have one single user role, user roles have permissions on zero, one or more departments and all resources are linked to one single department.

Example description of the Data model which is exposed to the client:

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

Example table invoices:

Example table with mapping of permissions:

Example table with users, each user has one user_role:

Example access views to apply permissions:

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

CREATE VIEW customers_view AS ...
CREATE VIEW payments_view AS ...

Example using an MCP Server where the user asks for “Sum of invoices” and the MCP Server converts this using text-to-SQL into the following SQL query:

SELECT SUM(amount) FROM invoices;

Query from the client, with all views prepended and permissions applied:

WITH 
  invoices   AS (SELECT * FROM invoices_view  WHERE allowed_role = '{user_role}'),
  customers  AS (SELECT * FROM customers_view WHERE allowed_role = '{user_role}'),
  payments   AS (SELECT * FROM payments_view  WHERE allowed_role = '{user_role}')
SELECT SUM(amount) FROM invoices;

Final executed query for Bob, with user_role = 'HR Manager':

WITH 
  invoices   AS (SELECT * FROM invoices_view  WHERE allowed_role = 'HR Manager'),
  customers  AS (SELECT * FROM customers_view WHERE allowed_role = 'HR Manager'),
  payments   AS (SELECT * FROM payments_view  WHERE allowed_role = 'HR Manager')
SELECT SUM(amount) FROM invoices;

Result for Bob:

Final executed query for Anne, with user_role = 'Finance Manager':

WITH 
  invoices   AS (SELECT * FROM invoices_view  WHERE allowed_role = 'Finance Manager'),
  customers  AS (SELECT * FROM customers_view WHERE allowed_role = 'Finance Manager'),
  payments   AS (SELECT * FROM payments_view  WHERE allowed_role = 'Finance Manager')
SELECT SUM(amount) FROM invoices;

Result for Anne: