Skip to content

Read-Only Postgres User Management

Overview

The PostgreSQL Permissions Management system is a crucial component in the data ingestion pipeline that facilitates secure data transfer from PostgreSQL replica databases to BigQuery for analytics and AI/ML processes. This documentation outlines the purpose, implementation, and usage of the permissions management system.

Purpose

In modern data architectures, it's essential to maintain separate database instances for operational and analytical workloads. This system helps establish secure, read-only access to PostgreSQL replica databases, ensuring that:

  • Data can be safely extracted for ingestion into BigQuery
  • Operational databases remain protected from analytical query load
  • Data access is properly controlled and auditable
  • AI/ML processes can access required data without risking operational data integrity

Core Features

  • Automated User Creation: Creates dedicated read-only users for data ingestion processes
  • Comprehensive Access Control: Manages database, schema, and table-level permissions
  • Security Best Practices: Enforces strong password policies and principle of least privilege
  • Multi-Database Support: Handles permissions across multiple databases within a PostgreSQL instance
  • Idempotent Operations: Safely handles repeated execution without creating duplicate users or permissions

Permissions Scope

The created user:

  • Has READ-ONLY access to specified databases
  • Cannot create new objects or modify existing ones
  • Has access limited to non-system schemas
  • Automatically gets access to new tables in permitted schemas