Skip to main content

SQL Authentication & Authorization: Superset, Trino, and Ranger

This page covers the mechanics of how a user's identity flows from the browser/API calls all the way to a query executing against data, and how access policies are enforced at each step.

For a higher-level overview of the token exchange model, see Token Exchange and Data Access.

End-to-End Flow​

Step 1: Token Exchange — BFF to Superset​

When the user makes any request through the Cogrion UI, the BFF API intercepts it and performs a token exchange with Keycloak before forwarding to Superset.

  • The user's original session token is never forwarded to Superset.
  • Keycloak issues a new JWT scoped specifically to the Superset OAuth client.
  • The exchanged token carries the user's identity claims: sub, email, preferred_username, and realm roles.

Superset validates the incoming JWT using its Flask app mutator — it checks the signature against Keycloak's public key, verifies the audience matches the Superset client ID, and maps the preferred_username claim to a Superset user.

Step 2: Identity Propagation — Superset to Trino​

When Superset dispatches a query to Trino, it passes the authenticated user's identity via the X-Trino-User HTTP header. This is set by the DATABASE_CONNECTION_MUTATOR configured on the superset_config.py.

Trino receives the username and, if JWT authentication is enabled on the Trino coordinator, also validates the forwarded token. The username extracted here is the identity that Ranger will evaluate policies against.

info

Superset does not pass the full JWT to Trino by default. The username is passed as a plain header. JWT validation at the Trino layer is an additional hardening step — it ensures Trino rejects requests where the claimed username cannot be verified.

Step 3: Policy Enforcement — Trino to Ranger​

Every query that reaches Trino passes through the Ranger system access control plugin before execution. The plugin is loaded via access-control.properties:

access-control.name=ranger

For each query, Ranger evaluates:

What Ranger checksHow it's resolved
User identityUsername from X-Trino-User header
ResourceCatalog → Schema → Table → Column
Access typeselect, show, execute, impersonate
Policy matchFirst matching policy wins (allow or deny)

If no policy matches, Ranger denies by default. The user sees a permission error from Trino.

Row filters and column masking​

If a matching policy includes a row filter or column mask, Ranger rewrites the query transparently — the user never sees the restricted data and receives no error.

What Happens When a Policy is Missing​

If a user has no matching Ranger policy for the resource they are querying:

  • Trino returns a permission denied error.
  • Superset surfaces this as a query execution error in SQL Lab.
  • The Ranger Audit log (if enabled) records the denied access with the username, resource, and timestamp.

To diagnose:

  1. Check the Ranger Audit UI → Audit → Access → filter by service trino and the username.
  2. If nothing appears, the Ranger plugin may not be reaching the Ranger Admin — check Trino startup logs for RangerSystemAccessControl initialized.
  3. Confirm the trino service definition exists in Ranger Admin → Services.

Ranger Service Setup​

The Ranger trino service must be registered before any policies can be evaluated. This is handled by the ranger-seed-policy job deployed as part of the Ranger bundle. The job:

  1. Waits for Ranger Admin to be ready.
  2. Creates the trino service definition if it does not already exist.
  3. Seeds the default trinouser impersonation and queryid execution policies.
  4. Creates the information-schema-reader policy so all users can read schema metadata.

If the seed job fails (check with kubectl logs job/ranger-seed-policy -n ranger), no service definition exists and the Trino plugin falls back to allow-all — all queries succeed regardless of policies.

Ranger User Sync​

For Ranger policies to match incoming Trino usernames, the user must exist in Ranger and belong to the appropriate Ranger role. This is handled by the Keycloak → Ranger role sync — a CronJob deployed as part of the Ranger bundle.

The sync runs every 15 minutes and mirrors Keycloak realm roles into Ranger:

  • Each Keycloak realm role (e.g. data_analyst) becomes a Ranger role named kc_realm_data_analyst.
  • All Keycloak users assigned to that realm role are added as members of the corresponding Ranger role.
  • Realm roles removed from Keycloak are disabled in Ranger (members cleared, isEnabled: false).

Policies in Ranger reference these kc_realm_* roles rather than individual users. A user who is assigned data_analyst in Keycloak automatically gains access to any Ranger policy that grants kc_realm_data_analyst.

For the full architecture and operational details, see Keycloak → Ranger Role Sync.

Keycloak → Superset Role Mapping​

When a user logs in, Superset reads their Keycloak realm roles from the JWT and maps them to internal Superset roles. This controls what features (SQL Lab, dashboards, admin UI) the user can access inside Superset.

This mapping is configured in the Superset bundle and can vary per deployment. As an example, a data_analyst realm role might map to sql_editor_user and dashboard_editor Superset roles.

Superset role controls feature access; Ranger policy controls data access. Both must allow an operation for a query to succeed — a user with SQL Lab access can still be denied by Ranger at query time.

The authoritative role mapping for a workspace is defined in the Superset bundle under roles_mapping.

Go Deeper​