Trino Authorization with Permit PDP

This repository demonstrates how to use Permit.io's Policy Decision Point (PDP) as an authorization service for Trino, featuring a complete Project Management Platform example.

Overview

This example provides a foundation for integrating Permit's PDP with Trino:

• Live Demo Application - Interactive demo showcasing role-based data access
• Trino - Distributed SQL query engine with access control
• Permit PDP - Authorization service providing fine-grained access control
• PostgreSQL - Sample database with realistic project management data

Architecture

sequenceDiagram
  actor User as User
  participant WebApp as Web App
  participant Trino as Trino
  participant PDP as PDP
  participant Postgres as Postgres

  autonumber
  User ->> WebApp: HTTP Request
  WebApp ->> Trino: Execute Query
  Trino ->> PDP: Check Permissions
  PDP ->> Trino: Allow / Deny
  Trino ->> Postgres: Execute Query
  Postgres ->> Trino: Return Result
  Trino ->> WebApp: Pass Result
  WebApp ->> User: HTTP Response

Loading

Key Components

• Web Application: Next.js app that uses Trino JS Client to execute all database queries through Trino
• Trino: Distributed SQL query engine that enforces authorization policies before accessing data
• Permit PDP: Policy Decision Point that evaluates access control policies
• PostgreSQL: The actual database storing the data

🎯 Live Demo: Project Management Platform

The repository includes a full-stack Next.js application that demonstrates database-level authorization in action

Prerequisites

• Docker and Docker Compose
• A Permit.io account and API key (Sign up for free)
• Permit CLI (Install Guide)

Getting Started

1. Clone this repository:

   git clone https://github.com/permitio/trino-authz-example.git
   cd trino-authz-example

2. Create a new environment in Permit.io and copy the PDP API key. See Permit.io documentation for more details.

   Copy .env.example to .env and paste the PDP API key:

   cp .env.example .env
   # Edit .env and paste the PDP API key

3. Start all services:

   docker-compose up -d
   # Or using the Makefile: make start

   Wait for services to be healthy (this may take 1-2 minutes). You can check the status with docker-compose ps.

4. Connect to Trino CLI:

   make connect
   # Or using the Makefile: docker exec -it $$(docker ps -qf "name=trino") trino --user admin

   You can also open the Trino Web UI at http://localhost:8080 to monitor the queries and their authorization decisions. (username: admin)

5. Execute a query to see the authorization in action:

   trino:public> SELECT * FROM projects;

   This should fail with a permission denied error, since we don't have any policies yet. You can check the Audit Log tab in the Permit dashboard to see the denied request.

Create Policies

Using the Permit CLI, we can automatically generate policies based on the Trino database schema.

1. Login to the Permit CLI:

   permit login

2. Disable authorization in Trino:

   make disable-authz

   This is necessary to allow the Permit CLI to fetch the database schema.

3. Generate policies:

   permit env apply trino --url http://localhost:8080 --user admin
   # Or using the Makefile: make apply-policies

   See Permit CLI documentation for more details.

4. Enable authorization in Trino:

   make enable-authz

5. Open the Permit Policy Editor and review the generated resources.

   Create roles, for example admin and viewer, and assign them to some user (e.g. alice). Grant some permissions to the roles, for example SelectFromColumns on the projects table to the viewer role.

   Note that you would need to grant trino_sys#ExecuteQuery and trino_catalog_postgresql#AccessCatalog the viewer role as well.

6. Connect to Trino as the user (e.g. alice):

   make connect alice

   You should be able now to query the projects table as the viewer role.

   SELECT * FROM projects;

   Check the Audit Log tab in the Permit dashboard to see the allowed request.

Web Demo Playground

The web demo at http://localhost:3000 provides an interactive way to explore authorization. It allows you to switch between different users and observe the data changes based on their permissions.

Note: The web application now connects to the database through Trino instead of directly to PostgreSQL. This ensures all queries go through Trino's authorization layer powered by Permit PDP.

Row-filtering Example

Row-filtering is a feature that allows you to inject additional WHERE clauses into the query based on the user's permissions.

1. View or edit the trino-authz.yaml file:

   rowFilters:
      # Define filters for the postgres.public.projects table
      trino_table_postgresql_public_projects:
         - action: only_active
            expression: "status = 'active'"
   
      # Define filters for the postgres.public.tasks table
      trino_table_postgresql_public_tasks:
         - action: exclude_todo
            expression: "status != 'todo'"

   This file configures the row-level security filters for the Trino tables. It is already mounted as a volume to the PDP container in the docker-compose.yml file for this example.

2. Enable row-filtering in Trino by adding the opa.policy.row-filters-uri property to the access-control.properties file.

   opa.policy.row-filters-uri=http://pdp:7000/trino/row-filter

3. Restart the Trino service and the PDP service to apply the changes.

   make restart

4. Open the Permit Policy Editor and create a new action called only_active for the projects table, and a new action called exclude_todo for the tasks table. Assign these actions to the viewer role.

5. Execute a query to see the authorization in action:

   SELECT * FROM projects;

   This should return only the active projects.

   SELECT * FROM tasks;

   This should return only the tasks that are not todo.

Column Masking Example

Column masking is a feature that allows you to mask the values of certain columns in the query result based on the user's permissions.

1. View or edit the trino-authz.yaml file:

   columnMasking:
      # Define column masks for the postgres.public.projects table
      trino_table_postgresql_public_projects:
         columns:
            - column_name: description
               view_expression: "CONCAT(SUBSTRING(description, 1, 10), '...')"
   
      # Define column masks for the postgres.public.tasks table
      trino_table_postgresql_public_tasks:
         columns:
            - column_name: description
               view_expression: "CONCAT(SUBSTRING(description, 1, 10), '...')"

   This file configures the column masking for the Trino tables. It is already mounted as a volume to the PDP container in the docker-compose.yml file for this example.

2. Enable column masking in Trino by adding the opa.policy.column-masking-uri property to the access-control.properties file.

   opa.policy.batch-column-masking-uri=http://pdp:7000/trino/batch-column-masking

3. Restart the Trino service and the PDP service to apply the changes.

   make restart

4. Open the Permit Policy Editor and create a new action called AddColumnMask for the projects table, and a new action called AddColumnMask for the tasks table. Assign these actions to the viewer role.

5. Execute a query to see the authorization in action:

   SELECT * FROM projects;

   This should return the projects with the description masked.

   SELECT * FROM tasks;

   This should return the tasks with the description masked.

🛠️ Contributing

For detailed information about:

• Development setup and tooling (ESLint, Prettier, TypeScript)
• Git workflow and commit conventions
• Pre-commit hooks and CI/CD
• Code style and best practices
• Testing and troubleshooting

See CONTRIBUTING.md