Providing direct Postgres access to customers is risky in a multi-tenant setup because standard roles leak metadata about other tenants.
Customers prefer direct SQL access for their BI tools and data workflows, as REST APIs cannot replicate the power of SQL for joins and aggregations.
Standard Postgres features like roles and row-level security are insufficient for multi-tenancy because they don't hide system catalog metadata, exposing other tenants' information.
A protocol-level proxy creates virtual single tenancy by intercepting and rewriting queries, making a shared database appear isolated to each client without needing a database per customer.
The security model combines native Postgres roles to control data access with a proxy layer that rewrites system catalogs and blocks unsafe operations to control metadata visibility.
The proxy rewrites queries to system catalogs by adding WHERE clauses and subqueries to filter results based on the authenticated customer's schema and object identifiers.
The proxy reassembles and rewrites the multi-part prepared statement protocol (parse, bind, execute) to ensure consistent security across all query types.
The proxy's core security guarantee is its fail-close mechanism, which rejects any query it cannot parse and prove safe, preventing unknown vulnerabilities.
A comprehensive denylist blocks dangerous SQL patterns like privilege escalation, while rate limits and statement timeouts prevent resource abuse on the shared database.
A restrictive native Postgres role provides a final layer of security, enabling a seamless customer experience with a standard connection string for any SQL tool.
The proxy solution has limitations, such as incompatibility with pg_dump and ongoing maintenance for the parser and denylist, but these were acceptable tradeoffs to meet customer needs.
The core takeaway is the reusable pattern of virtual single tenancy, where a proxy rewrites the wire protocol to provide secure access to a shared database.
Jobs that call for the skills explored in this talk.