A simple tcp server that speaks postgres, ported from https://gist.github.com/eatonphil/079f8654174b9b9c1953b76008d024ae

psql_wire_protocol.exs

"""
Elixir server that partially speaks the PG wire protocol. Run:

```
mix run psql_wire_protocol.exs
```

and run psql at a separate terminal prompt:

```
PGPASSWORD=111 psql -h localhost -p 55432
```

- Note 1: This just hardcodes a set of results to return, but you could encode a set of results more dynamically.
- Note 2: This supports a tiny subset of the protocol, just connecting, issuing queries, and exiting
- Note 3: Password connection only
- Note 4: Only 1 client at a time, but that would be easy to fix
- Note 5: I couldn't find a good list of field type oids in the postgres docs, but I found this:

  https://hackage.haskell.org/package/postgresql-simple-0.0.2/docs/src/Database-PostgreSQL-Simple-BuiltinTypes.html

  So 20 for int8, 23 for int4, 25 for text, and 1043 for varchar..
- Note 6: Don't forget to terminate strings with a null byte

"""

defmodule WireProtocol do

  @fields [
    %{
      name: "a",
      table_id: 0,
      column_id: 0,
      type_id: 23,
      type_size: 4,
      type_modifier: -1
    },
    %{
      name: "b",
      table_id: 0,
      column_id: 0,
      type_id: 23,
      type_size: 4,
      type_modifier: -1
    }
  ]


  def read_startup_message(client) do
    {:ok, data} = :gen_tcp.recv(client, 0)

    # ssl request
    <<msgsize::(8*4), sslcode::(8*4), data::binary>> = data
    
    # if these don't match then an unsupported request has been made
    8 = msgsize
    80877103 = sslcode

    <<msgsize::(8*4), version::(8*4), data::binary>> = data

    ["user", user, "database", database | rest] = String.split(data, <<0>>)

    IO.puts("""
    Connection request:
    User: #{user}
    Database: #{database}
    """)
  end

  def read_ssl_request(client) do
    {:ok, data} = :gen_tcp.recv(client, 0)
    IO.inspect(data, label: "SSL request")
  end

  def send_authentication_request(client) do
    :gen_tcp.send(client, 'R\x00\x00\x00\x08\x00\x00\x00\x03')
  end

  def read_authentication(client) do
    {:ok, _type_code} = :gen_tcp.recv(client, 1)

    {:ok, _msglen} = :gen_tcp.recv(client, 4)

    {:ok, data} = :gen_tcp.recv(client, 0)
    
    _password = String.trim_trailing(data, <<0>>) |> IO.inspect(label: "password")
    
    # we could check the password by making an assertion here
  end

  def send_notice(client) do
    :gen_tcp.send(client, "N") |> IO.inspect(label: "SSL request")
  end

  def send_authentication_ok(client) do
    :gen_tcp.send(client, 'R\x00\x00\x00\x08\x00\x00\x00\x00')
  end

  def send_ready_for_query(client) do
    :gen_tcp.send(client, 'Z\x00\x00\x00\x05I')
  end

  def loop(client) do
    {:ok, type_code} = :gen_tcp.recv(client, 1)
    case type_code do
      "X" ->
        IO.puts("received cancel request")
        nil
      "Q" ->
        {:ok, msglen} = :gen_tcp.recv(client, 4)
        {:ok, data} = :gen_tcp.recv(client, 0)
        query = String.trim_trailing(data, <<0>>)
        IO.puts("Query: #{query}")

        send_row_description(client)
        send_row_data(client)
        send_command_complete(client)

        send_ready_for_query(client)
        loop(client)
      other ->
        IO.puts("received something else :(")
        send_ready_for_query(client)
        loop(client)
    end
  end

  def send_row_description(client) do
    buf = Enum.reduce(@fields, <<>>, fn field, acc ->
      acc <> field_description(field)
    end)

    msg = <<"T",(6 + byte_size(buf))::32, (length(@fields))::16>> <> buf

    :gen_tcp.send(client, msg)
  end

  def send_row_data(client) do
    rows = [[1, 2], [3, 4], [5, 6]]
    for row <- rows do
      buf = for field <- row, reduce: <<>> do
        acc ->
          string = "#{field}"
          acc <> << byte_size(string)::32, string::binary >>
      end

      msg = <<"D", (6 + byte_size(buf))::32, length(row)::16 >> <> buf

      :gen_tcp.send(client, msg)
    end
  end

  def send_command_complete(client) do
    msg = <<"C", 11::32>> <> List.to_string('SELECT\x00')
    :gen_tcp.send(client, msg)
  end

  defp field_description(field) do
    <<field.name::binary, 0::8, # zero-terminated string
      field.table_id::32,
      field.column_id::16,
      field.type_id::32,
      field.type_size::16,
      field.type_modifier::32,
      0::16>> # text format code
  end

  defp int32(v), do: <<v>>
end

defmodule PSQLServer do
  @moduledoc """
  A simple Postgres server that a ccepts connections and prints queries to the console.

  To try it:

  1. Paste the contents of this file (this module and the `WireProtocol` module) into iex.
  2. Run `PSQLServer.accept(55432)`
  3. At another terminal prompt run
    PGPASSWORD=111 psql -h localhost -p 55432
  4. Type queries and see them printed to the console.
  """

  require Logger
  import WireProtocol

  def accept(port) do
    opts = [:binary, packet: 0, active: false, reuseaddr: true]
    {:ok, socket} = :gen_tcp.listen(port, opts)

    loop_acceptor(socket)
  end

  defp loop_acceptor(socket) do
    {:ok, client} = :gen_tcp.accept(socket)

    send_notice(client)
    read_startup_message(client)

    send_authentication_request(client)
    read_authentication(client)
    send_authentication_ok(client)

    send_ready_for_query(client)
    loop(client)
  end

  defp write_line(line, socket) do
    :gen_tcp.send(socket, line)
  end
end

PSQLServer.accept(55432)