Tutorial: SQL Data Filtering

This tutorial demonstrates end-to-end data filtering with OPA around a concrete question: whose salaries can a Director see?

You will write an authorization policy, use OPA's partial evaluation to derive a SQL WHERE clause, and apply that filter to a real database query.

Prerequisites

• OPA installed
• sqlite3 (pre-installed on macOS and most Linux distributions)
• curl and jq

Steps

1. Create and populate the database

This tutorial works with the following dataset:

name	department	role	salary
Alice	engineering	director	130000
Bob	engineering	engineer	90000
Carol	engineering	engineer	85000
Dave	marketing	director	120000
Eve	marketing	manager	95000

Save the following SQL to a file named employees.sql:

employees.sql

CREATE TABLE employees (name TEXT, department TEXT, role TEXT, salary INTEGER);
INSERT INTO employees VALUES ('Alice', 'engineering', 'director', 130000);
INSERT INTO employees VALUES ('Bob',   'engineering', 'engineer',  90000);
INSERT INTO employees VALUES ('Carol', 'engineering', 'engineer',  85000);
INSERT INTO employees VALUES ('Dave',  'marketing',   'director', 120000);
INSERT INTO employees VALUES ('Eve',   'marketing',   'manager',   95000);

Then create the database by loading that file:

sqlite3 company.db < employees.sql

2. Write the policy

The rule is: Directors may see the salaries of employees in their own department.

input.employees is declared as unknown — it represents database rows that OPA has not seen yet. input.user is known at query time and its values will be substituted during partial evaluation.

Save the following Rego code to a file named policy.rego:

policy.rego

# METADATA
# scope: package
# compile:
#   unknowns: [input.employees]
package filters

include if {
    input.user.role == "director"
    input.employees.department == input.user.department
}

3. Start OPA

opa run --server policy.rego

OPA is now listening on http://localhost:8181.

4. Ask OPA for a SQL filter

In another terminal, call the compile endpoint with the logged-in user as input. Alice is a Director in Engineering:

curl -s -X POST http://localhost:8181/v1/compile/filters/include \
  -H "Content-Type: application/json" \
  -H "Accept: application/vnd.opa.sql.sqlite+json" \
  -d '{"input": {"user": {"name": "alice", "role": "director", "department": "engineering"}}}'

OPA partially evaluates the policy:

• input.user.role == "director" — both sides are known; the condition is true, so it is consumed.
• input.employees.department == input.user.department — the left hand side is unknown; the known right hand side ("engineering") is substituted, yielding the SQL condition.

The response:

{
  "result": {
    "query": "WHERE employees.department = 'engineering'"
  }
}

5. Query the database

Extract the filter and use it in a SQL query:

FILTER=$(curl -s -X POST http://localhost:8181/v1/compile/filters/include \
  -H "Content-Type: application/json" \
  -H "Accept: application/vnd.opa.sql.sqlite+json" \
  -d '{"input": {"user": {"name": "alice", "role": "director", "department": "engineering"}}}' \
  | jq -r '.result.query')

sqlite3 company.db "SELECT name, salary FROM employees $FILTER;"

Output — Alice sees all Engineering salaries:

name	salary
Alice	130000
Bob	90000
Carol	85000

Dave is a Director in Marketing, so he gets a different filter from the same policy:

FILTER=$(curl -s -X POST http://localhost:8181/v1/compile/filters/include \
  -H "Content-Type: application/json" \
  -H "Accept: application/vnd.opa.sql.sqlite+json" \
  -d '{"input": {"user": {"name": "dave", "role": "director", "department": "marketing"}}}' \
  | jq -r '.result.query')

sqlite3 company.db "SELECT name, salary FROM employees $FILTER;"

Output — Dave sees all Marketing salaries:

name	salary
Dave	120000
Eve	95000

6. Non-Directors are denied

Bob is an Engineer, not a Director. The input.user.role == "director" condition is known and false, so no rule body can ever be satisfied — the policy unconditionally denies:

curl -s -X POST http://localhost:8181/v1/compile/filters/include \
  -H "Content-Type: application/json" \
  -H "Accept: application/vnd.opa.sql.sqlite+json" \
  -d '{"input": {"user": {"name": "bob", "role": "engineer", "department": "engineering"}}}'

Response — the query key is absent:

{}

An absent query means unconditional deny. The application should return zero rows without issuing a database query.

Ensure safe defaults

OPA returns the filter — it does not enforce it. The application is responsible to use it as intended.

In this example, if the user is not a Director, no rule body can be satisfied and OPA returns an unconditional deny — represented as a missing query key in the result — meaning the application should safely return zero rows.

What partial evaluation did

OPA evaluated the policy with input.user fully known. The expressions that involved only known values (input.user.role == "director") were fully evaluated and consumed — they do not appear in the output. Only expressions involving the unknown input.employees survived as residual conditions, which OPA then translated into SQL.

The application never needs to know how the policy decides which salaries are visible. It sends user context and receives a SQL filter (or a deny) to act on.

Handling unconditional results

OPA response	Meaning	Application action
{ "query": "WHERE ..." }	Conditional allow	Append filter to SQL query
{ "query": "" }	Unconditional allow	Run query with no WHERE
{}	Unconditional deny	Return zero rows, skip query

Clean up

Stop the OPA server with Ctrl+C in the terminal where it is running, then remove the files created during this tutorial:

rm employees.sql policy.rego company.db

Next steps

• Evaluating a Data Filter Policy — a step-by-step walkthrough of partial evaluation
• Writing valid Data Filtering Policies — which Rego constructs are supported as filter conditions
• Language SDKs — in a production setup, using a language SDK is recommended over raw curl calls. The ecosystem page lists SDKs for Go, Java, Python, JavaScript, and more, all of which provide typed clients for the compile API used in this tutorial.