Skip to content

feat(db): add role-based access grants for analytics schema#5279

Merged
maidul98 merged 5 commits intomainfrom
sanitized-infisical-db-schema
Jan 27, 2026
Merged

feat(db): add role-based access grants for analytics schema#5279
maidul98 merged 5 commits intomainfrom
sanitized-infisical-db-schema

Conversation

@maidul98
Copy link
Collaborator

@maidul98 maidul98 commented Jan 27, 2026

Context

This PR adds the ability to grant read access (USAGE + SELECT) on the analytics schema to a configurable PostgreSQL role after schema generation. This enables external analytics tools to query the sanitized views with a dedicated read-only database role even after each schema re-create.

Workflow

When GENERATE_SANITIZED_SCHEMA=true:

  1. Drop schema (before migrations) - prevents dependency conflicts so that ALTER commands don't conflict with table that has views
  2. Run migrations
  3. Create schema and views
  4. Grant access to role (if SANITIZED_SCHEMA_ROLE is configured)

New Environment Variables

When SANITIZED_SCHEMA_ROLE is defined it will grant read access to the analytics schema for the given role.

Screenshots

N/A - Backend changes only

Type

  • Fix
  • Feature
  • Improvement
  • Breaking
  • Docs
  • Chore

Checklist

@maidul98
feat(db): add role-based access grants for analytics schema
0c14877 
## Context

This PR adds the ability to grant read access (USAGE + SELECT) on the analytics schema to a configurable PostgreSQL role after schema generation. This enables external analytics tools to query the sanitized views with a dedicated read-only database role even after each schema re-create.

### Workflow

When `GENERATE_SANITIZED_SCHEMA=true`:

1. Drop schema (before migrations) - prevents dependency conflicts so that ALTER commands don't conflict with table that has views
2. Run migrations
3. Create schema and views
4. Grant access to role (if `SANITIZED_SCHEMA_ROLE` is configured)

### New Environment Variables

When `SANITIZED_SCHEMA_ROLE` is defined it will grant read access to the analytics schema for the given role.

## Screenshots

N/A - Backend changes only

## 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
- [ ] Tested locally
- [ ] Updated docs (if needed)
- [x] Read the [contributing guide](https://infisical.com/docs/contributing/getting-started/overview)
@maidul98
Copy link
Collaborator Author

maidul98 commented Jan 27, 2026 •
edited
Loading

✅ 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
Copy link
Contributor

greptile-apps bot commented Jan 27, 2026

Greptile Overview

Greptile Summary

This PR adds the ability to grant read-only database access to the analytics schema via a configurable PostgreSQL role (SANITIZED_SCHEMA_ROLE). After schema generation, the system calls generateGrantReadAccessSQL() from the updated @infisical/pg-view-generator package to execute GRANT statements.

Major Changes:

  • Renamed schema from "infisical-sanitized" to "analytics"
  • Added new grantSanitizedSchemaAccess() function in sanitized-schema.ts
  • Integrated grant logic into both migration workflow paths (with/without pending migrations)
  • Added SANITIZED_SCHEMA_ROLE environment variable with zod validation
  • Bumped @infisical/pg-view-generator to v1.1.0

Critical Security Issue:

  • SQL injection vulnerability in grantSanitizedSchemaAccess() - the role parameter is not validated before being used in raw SQL execution

Additional Notes:

  • Environment variable access should use the validated config helper instead of direct process.env access
  • Consider documenting this new feature in the /docs folder for customer discoverability (per custom rule)

Confidence Score: 1/5

  • This PR contains a critical SQL injection vulnerability that must be fixed before merging
  • The grantSanitizedSchemaAccess() function accepts an unvalidated role parameter that is passed directly into raw SQL execution. This creates a severe security vulnerability where anyone who can control the SANITIZED_SCHEMA_ROLE environment variable could execute arbitrary SQL commands, potentially leading to data breaches, data loss, or complete database compromise. The codebase already demonstrates awareness of SQL injection risks (see validateGeneratedSQL() function), but this new function bypasses those protections.
  • Critical attention required for backend/src/db/sanitized-schema.ts - the SQL injection vulnerability must be patched with input validation before this can be merged safely

Important Files Changed

Filename Overview
backend/src/db/sanitized-schema.ts Added grantSanitizedSchemaAccess() function with CRITICAL SQL injection vulnerability - role parameter needs validation before use in raw SQL
backend/src/auto-start-migrations.ts Integrated role grant logic into migration workflow at two execution paths - should use validated config instead of direct env access
backend/src/lib/config/env.ts Added SANITIZED_SCHEMA_ROLE environment variable definition with proper zod schema validation
backend/package.json Bumped @infisical/pg-view-generator from 1.0.0 to 1.1.0 to support generateGrantReadAccessSQL() function

greptile-apps[bot]
greptile-apps bot reviewed Jan 27, 2026
View reviewed changes
Copy link
Contributor

@greptile-apps greptile-apps bot left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

3 files reviewed, 3 comments

Edit Code Review Agent Settings | Greptile

maidul98 and others added 3 commits January 27, 2026 00:32
@maidul98 @greptile-apps
Update backend/src/db/sanitized-schema.ts
1fc4d38 
Co-authored-by: greptile-apps[bot] <165735046+greptile-apps[bot]@users.noreply.github.com>
@maidul98
fix lint
63458f6
@maidul98
switch to re2
050a5df
@maidul98 maidul98 merged commit 2ae0949 into main Jan 27, 2026
10 checks passed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@akhilmhdh akhilmhdh akhilmhdh approved these changes

+1 more reviewer

@greptile-apps greptile-apps[bot] greptile-apps[bot] left review comments

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

@maidul98 @akhilmhdh