feat(backend): add sanitized schema generation for analytics views by maidul98 · Pull Request #5251 · Infisical/infisical

maidul98 · 2026-01-23T07:54:43Z

Context

Adds the ability to generate a sanitized PostgreSQL schema (infisical-sanitized) containing read-only views of database tables. This enables external analytics tools to query data without accessing the main schema directly.

How it works:

Controlled by GENERATE_SANITIZED_SCHEMA environment variable
Drops the existing sanitized schema before migrations run (to avoid blocking ALTER TABLE operations)
Recreates the schema with views after migrations complete
Uses advisory locks to ensure only one container executes schema generation in multi-replica deployments
Fails silently without blocking app startup, logging errors with SANITIZED_SCHEMA_ERROR identifier

Screenshots

N/A - Backend only change

Steps to verify the change

Set GENERATE_SANITIZED_SCHEMA=true in .env
Connect to PostgreSQL and verify the infisical-sanitized schema exists with views

Type

Checklist

Title follows the conventional commit format: type(scope): short description (scope is optional, e.g., fix: prevent crash on sync or fix(api): handle null response).
Tested locally
Updated docs (if needed)
Read the contributing guide

## Context Adds the ability to generate a sanitized PostgreSQL schema (`infisical-sanitized`) containing read-only views of database tables. This enables external analytics tools to query data without accessing the main schema directly. **How it works:** - Controlled by `GENERATE_SANITIZED_SCHEMA` environment variable - Drops the existing sanitized schema before migrations run (to avoid blocking `ALTER TABLE` operations) - Recreates the schema with views after migrations complete - Uses advisory locks to ensure only one container executes schema generation in multi-replica deployments - Fails silently without blocking application startup, logging errors with `SANITIZED_SCHEMA_ERROR` identifier ## Screenshots N/A - Backend only change ## Steps to verify the change 1. Set `GENERATE_SANITIZED_SCHEMA=true` in .env 2. Connect to PostgreSQL and verify the `infisical-sanitized` schema exists with views ## Type - [ ] Fix - [x] Feature - [ ] Improvement - [ ] Breaking - [ ] Docs - [ ] Chore ## Checklist - [x] Title follows the [conventional commit](https://www.conventionalcommits.org/en/v1.0.0/#summary) format: `type(scope): short description` (scope is optional, e.g., `fix: prevent crash on sync` or `fix(api): handle null response`). - [ ] Tested locally - [ ] Updated docs (if needed) - [x] Read the [contributing guide](https://infisical.com/docs/contributing/getting-started/overview)

maidul98 · 2026-01-23T07:55:32Z

✅ Snyk checks have passed. No issues have been found so far.

Status	Scanner	Critical	High	Medium	Low	Total (0)
✅	Open Source Security	0	0	0	0	0 issues

💻 Catch issues earlier using the plugins for VS Code, JetBrains IDEs, Visual Studio, and Eclipse.

greptile-apps · 2026-01-23T07:59:06Z

Greptile Overview

Greptile Summary

Adds the ability to generate a sanitized PostgreSQL schema (infisical-sanitized) containing read-only views of database tables for analytics tools. The feature is controlled by the GENERATE_SANITIZED_SCHEMA environment variable and uses advisory locks to prevent conflicts in multi-replica deployments.

Key changes:

Created sanitized-schema.ts with SQL validation to prevent injection attacks
Defined views for 81 database tables in sanitized-schema.yaml
Integrated schema lifecycle management: drop before migrations, recreate after
Added @infisical/pg-view-generator package dependency for SQL generation
Used advisory lock (2026) to coordinate schema generation across instances

Issues found:

The external_certificate_authorities table exposes credentials and configuration columns containing encrypted/sensitive data. This increases attack surface by allowing analytics tools access to encrypted credentials.
Lock acquisition error handling in auto-start-migrations.ts could allow schema drop operations to proceed without proper lock protection.

Confidence Score: 3/5

This PR has security concerns that should be addressed before merging, particularly around exposing encrypted credentials in analytics views.
Score reflects two logic-level issues: (1) exposure of encrypted credentials in the external_certificate_authorities view which increases attack surface, and (2) a race condition where schema operations could proceed without lock protection if lock acquisition fails. The implementation is otherwise well-structured with SQL validation and proper lock coordination.
Pay close attention to backend/src/db/sanitized-schema.yaml for the credentials exposure issue and backend/src/auto-start-migrations.ts for the lock handling logic.

Important Files Changed

Filename	Overview
backend/src/db/sanitized-schema.ts	Implements sanitized schema generation with SQL validation and advisory locks. SQL validation prevents injection but relies on external package for SQL generation.
backend/src/db/sanitized-schema.yaml	Defines views for 81 database tables. Exposes encrypted credentials in `external_certificate_authorities` table which poses a security risk.
backend/src/auto-start-migrations.ts	Added sanitized schema lifecycle management (drop before migrations, recreate after). Lock acquisition error handling could allow operations without lock protection.

greptile-apps

_{2 files reviewed, 2 comments}

_{Edit Code Review Agent Settings | Greptile}

backend/src/db/sanitized-schema.yaml

backend/src/auto-start-migrations.ts

Co-authored-by: greptile-apps[bot] <165735046+greptile-apps[bot]@users.noreply.github.com>

maidul98 requested a review from akhilmhdh January 23, 2026 07:57

greptile-apps bot reviewed Jan 23, 2026

View reviewed changes

backend/src/db/sanitized-schema.yaml Outdated Show resolved Hide resolved

backend/src/auto-start-migrations.ts Outdated Show resolved Hide resolved

maidul98 and others added 4 commits January 23, 2026 03:02

Update backend/src/db/sanitized-schema.yaml

983de3e

Co-authored-by: greptile-apps[bot] <165735046+greptile-apps[bot]@users.noreply.github.com>

fix lint

85617f9

feat: corrected the migration

2bac6ea

add log when skipping schema generation

747fbb6

maidul98 merged commit f515a76 into main Jan 24, 2026
7 of 10 checks passed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat(backend): add sanitized schema generation for analytics views#5251

feat(backend): add sanitized schema generation for analytics views#5251
maidul98 merged 5 commits intomainfrom
add-analytics-schema-generation

maidul98 commented Jan 23, 2026 •

edited

Loading

Uh oh!

maidul98 commented Jan 23, 2026 •

edited

Loading

Uh oh!

greptile-apps bot commented Jan 23, 2026

Uh oh!

greptile-apps bot left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

maidul98 commented Jan 23, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Context

Screenshots

Steps to verify the change

Type

Checklist

Uh oh!

maidul98 commented Jan 23, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

✅ Snyk checks have passed. No issues have been found so far.

Uh oh!

greptile-apps bot commented Jan 23, 2026

Greptile Overview

Greptile Summary

Confidence Score: 3/5

Important Files Changed

Uh oh!

greptile-apps bot left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

maidul98 commented Jan 23, 2026 •

edited

Loading

maidul98 commented Jan 23, 2026 •

edited

Loading