Building a Database Interface and PostgreSQL Proxy Using Elixir

Michael St Clair, during his talk at ElixirConf 2023, illustrates a solution for developers needing auditable access to production databases without compromising security. The project leverages the power of Elixir along with Ecto and LiveView to craft a functional and user-friendly database GUI. Additionally, St Clair explores the use of gen_tcp for establishing a PostgreSQL proxy, which includes handling binaries within the Elixir context to work with the PostgreSQL message protocol. This GUI not only provides a view of the database but also incorporates features for data update requests and data protection, specifically by analyzing and rewriting queries involving sensitive data. St Clair also presents Protuff messages over WebSockets for binary communication, thus reflecting Elixir's versatility in managing different data transmission formats. The talk delves into the architectural components, including the backend (Elixir, Phoenix, LiveView) and Dynamic supervisors with Ecto for database connections, as well as an optional agent for WebSocket communication. Furthermore, it covers the PostgreSQL protocol's complexities regarding SSL request handling, connection establishment, authentication flows, and query processing. St Clair plans to extend the features of this proxy, considering similar solutions for MySQL and potentially creating a desktop application.