Handling a raw tcp inside asp.net - Postgresql wire protocol server in c#

[ewinnington]

I'm experimenting with implementing the PgSQL wire protocol (server side) in C#, currently the server side is hosted in a console application with a TCP Listener.

        IPAddress ipAddress = IPAddress.Parse("0.0.0.0");
        int port = 5432;

        TcpListener listener = new TcpListener(ipAddress, port);

        try
        {
            listener.Start();
            Console.WriteLine("Listening for connections on port {0}...", port);

            using TcpClient client = listener.AcceptTcpClient();
            Console.WriteLine("Connection accepted from {0}.", client.Client.RemoteEndPoint);
            NetworkStream stream = client.GetStream();
            bool isRunning = true;
            while (isRunning)
            {
                // Receive data from the client
                byte[] buffer = new byte[1024];
                int bytesRead = stream.Read(buffer, 0, buffer.Length);
                if (bytesRead > 0)
                {
                    string receivedData = System.Text.Encoding.ASCII.GetString(buffer, 0, bytesRead);
                    Console.WriteLine("Received data from client: {0}", receivedData);
[...]

Using the references of https://gavinray97.github.io/blog/postgres-wire-protocol-jdk-21 and the excellent documentation of the Postgresql wire protocol https://www.postgresql.org/docs/current/protocol.html and https://www.postgresql.org/docs/current/protocol-message-formats.html, I implemented here a C# minimal console application that responds to a command line psql query.

How can I put this into ASP.NET? Can I benefit from some of the functionality of asp.net while intercepting the raw tcp data and processing it manually? Is there something I can plug into the pipeline for processing? This would enable hosting this postgresql server endpoint next to my rest apis.

[gfoidl]

You can use a custom ConnectionHandler. An (older) example that shows how to use that type is given in MultiProtocolAspNetCore

Besides that: are you aware of Npgsql?

[ewinnington]

Thank you so much for the link to the connection handler and the example repo. That’s what I needed! Somehow I didn’t find that reference in the docs on my own.

Yes I am aware of Npgsql, that’s a client. I’m implementing the server side (without an underlying PostgreSQL database).

Meaning you can use Npgsql client to connect to my server’s endpoint.

[gfoidl]

Out of interest: is this just for experiments / learning or do you have an actual use case for the custom PG server?

[ewinnington]

Feasibility study.

Actual use case: an SQL endpoint for (department end user) that collects data from multiple sources (in-memory/redis, Cassandra, other rest api, and relational db) and merges and exposes them as a single set of (virtual) relational tables. EndUser has expressed a preference for a SQL endpoint over Rest+Json for integration with existing infra - providing an sql endpoint with our own backing implementation would allow us to integrate data controls that our particular data sources require.

As to why PGSQL, implementing the pgsql wire protocol seems to be much simpler than implementing the mssql server side wire protocol.

[gfoidl]

For the actual use case did you consider PostgreSQL's Foreign data wrappers?

[ewinnington]

FDW would be alternative - if we were actually going down to a DB. Currently we don't even have postgresql in the stack.

What's interesting about the wire protocol is that it is SQL variant independent (meaning in most of client side there is no validation of sql strings - all is passed through directly to the server) and only specifies a format for data transmission - but it can be very easily consumed as an SQL based table result.

Send python down the wire from the client? Get an SQL PgSQL table back.
Send a MSSQL style SQL query? Parse it and return an SQL PgSQL table down the line.

Many many tools consume SQL endpoints, why are DBs the only ones to speak it and not higher level APIs? Not saying this is a perfect solution or even a very smart one, but in my case, it might scratch an itch.