PHPカンファレンス福岡2024のトークを見て、自分も同じようなRDBMSライクなTCPサーバを作ってみたくなりました。

　
そこで、psql とメッセージを交換できるPostgreSQL風TCPサーバをRubyで作ってみたことから、メモを残します。

ちなみに、作成するにあたり以下の記事がとても参考になりました。ありがとうございました。
PostgreSQL Client から自作 DBMS に接続する - goropikariの備忘録

環境
Rubyで何のRDBMS風TCPサーバを作るか検討
psql と PostgreSQL の通信をのぞいてみる
- 準備
  - DockerでPostgreSQLを準備
  - psql のインストール
- 通信内容の確認
Rubyで数値・文字列とバイナリ文字列間の変換をする方法について
RubyでPostgreSQL風TCPサーバを作る
- Start-upフェーズ
- SimpleQueryフェーズ
動作確認
- psqlでの動作確認
- ngrepでの通信内容確認
ソースコード

環境

WSL2
Ruby 3.3.6
psql 14.15
PostgreSQL 17.2
- 対象のPostgreSQLのプロトコルバージョンは 3.0
  - 参考：第55章フロントエンド/バックエンドプロトコル

Rubyで何のRDBMS風TCPサーバを作るか検討

前回に引き続き、今回もRubyでTCPサーバを作ることにします。
RubyのSocket.tcp_server_loopでechoサーバを作り、その通信を ngrepで確認してみた - メモ的な思考的な

　
続いて、何のRDBMS風のTCPサーバにするかを考えます。

今回はOSSのRDBMSが良さそうと考えて、

のどちらかにしようと考えました。

　
では、MySQLやPostgreSQLのプロトコルはどんな感じだろうと、

MySQL
- 冒頭のPHPカンファレンスのスライド
- 技術書典で販売していた書籍
  - MySQLの通信仕様をGo言語で理解する本：taumu
PostgreSQL
- PostgreSQL Client から自作 DBMS に接続する - goropikariの備忘録

を読んだところ、PostgreSQLのプロトコルの方が容易そうに見えました。

そこで、PostgreSQL風TCPサーバを作ることにしました。

　
また、サーバが正しく作成できているかも知りたいため、

psql
RubyのPostgreSQL向けのgem pg

のどちらかをクライアントとして、サーバ・クライアント間での通信が可能にしたいと考えました。

　
PostgreSQLのドキュメントを読んだところ、 psql は

通常のpsql操作とは異なり、拡張問い合わせプロトコル(55.1.2を参照)を使用します。通常は、簡易問い合わせプロトコルを使用します

https://www.postgresql.jp/document/16/html/app-psql.html

とあり、簡易問い合わせプロトコルを実装しておけば通信できそうでした。

一方、 pg のようなライブラリでは、簡易問い合わせに加えて拡張問い合わせプロトコルを使っている可能性もありそうでした。

そこで今回は、サーバで簡易問い合わせプロトコルだけ実装すれば通信が可能になる psql をクライアントに選びました。

　
他に、PostgreSQL風TCPサーバは簡易的な実装とするため、

SSL接続
認証

まわりを省略することにしました。

psql と PostgreSQL の通信をのぞいてみる

まずは psql と PostgreSQL の間でどんな通信をしているのか、手元の環境で確かめてみます。

準備

DockerでPostgreSQLを準備

PostgreSQL公式のDockerイメージが提供されています。
postgres - Official Image | Docker Hub

また、そこには Docker Compose でPostgreSQLで起動する方法も記載されています。

　
ただ、 Compose 仕様での書き方とは異なっていたことから、一部修正して Docker Compose で起動できるような compose.yml ファイルを用意しました。

ちなみに、今回セキュリティは考慮せず、 POSTGRES_HOST_AUTH_METHOD: 'trust' としています。

services:
  db:
    image: postgres:17.2
    container_name: postgres_17_2
    ports:
      - 15432:5432
    environment:
      POSTGRES_USER: 'postgres'
      POSTGRES_PASSWORD: 'postgres'
      POSTGRES_HOST_AUTH_METHOD: 'trust'

　
あとは、 docker compose up -d などを実行し、PostgreSQLのコンテナを起動しておきます。

psql のインストール

psqlはPostgreSQLをインストールすれば付属してきます。ただ、今回のPostgreSQLはDockerで用意したため、 psql は単体でインストールしたいです。

今回はWSL2 (Ubuntu) なことから、 postgresql-client をインストールすることで psql が使えるようになります。
postgresql - How to install psql without Postgres? - Ask Ubuntu

通信内容の確認

準備ができたので通信内容を確認していきます。

今回は以下の順番で操作したときにどんな通信内容になるのかを確認します。

psqlにてPostgreSQLへ接続
psqlにて以下の順番でSQLを実行し、各結果を確認
- create table apples (id int, name varchar(255));
- insert into apples values (1, 'shinano_gold'), (2, 'fuji');
- select * from apples;
psql で切断

　
なお、今回は ngrep を使って通信内容を確認するので、事前に起動しておきます。

$ sudo ngrep -x -q -d lo '' 'port 15432'
interface: lo (127.0.0.0/255.0.0.0)
filter: ( port 15432 ) and ((ip || ip6) || (vlan && (ip || ip6)))

psqlにてPostgreSQLへ接続

psqlで実行します。

$ psql postgres postgres -h localhost -p 15432
psql (14.15 (Ubuntu 14.15-0ubuntu0.22.04.1), server 17.2 (Debian 17.2-1.pgdg120+1))
WARNING: psql major version 14, server major version 17.
         Some psql features might not work.
Type "help" for help.

　
ngrepのターミナルを確認すると、SSL Request が流れてきました。
https://www.postgresql.jp/document/16/html/protocol-flow.html#PROTOCOL-FLOW-SSL

T 127.0.0.1:38518 -> 127.0.0.1:15432 [AP] #4
  00 00 00 08 04 d2 16 2f                               ......./

T 127.0.0.1:15432 -> 127.0.0.1:38518 [AP] #6
  4e                                                    N

　
続いて、

psql から開始メッセージを送信
PostgreSQLから以下の順で送信
- AuthenticationOk
- ParameterStatus
- ReadyForQuery

が確認できました。
https://www.postgresql.jp/document/16/html/protocol-flow.html#PROTOCOL-FLOW-START-UP

T 127.0.0.1:38518 -> 127.0.0.1:15432 [AP] #8
  00 00 00 54 00 03 00 00    75 73 65 72 00 70 6f 73    ...T....user.pos
  74 67 72 65 73 00 64 61    74 61 62 61 73 65 00 70    tgres.database.p
  6f 73 74 67 72 65 73 00    61 70 70 6c 69 63 61 74    ostgres.applicat
  69 6f 6e 5f 6e 61 6d 65    00 70 73 71 6c 00 63 6c    ion_name.psql.cl
  69 65 6e 74 5f 65 6e 63    6f 64 69 6e 67 00 55 54    ient_encoding.UT
  46 38 00 00                                           F8..

T 127.0.0.1:15432 -> 127.0.0.1:38518 [AP] #10
  52 00 00 00 08 00 00 00    00 53 00 00 00 17 69 6e    R........S....in
  5f 68 6f 74 5f 73 74 61    6e 64 62 79 00 6f 66 66    _hot_standby.off
  00 53 00 00 00 19 69 6e    74 65 67 65 72 5f 64 61    .S....integer_da
  74 65 74 69 6d 65 73 00    6f 6e 00 53 00 00 00 15    tetimes.on.S....
  54 69 6d 65 5a 6f 6e 65    00 45 74 63 2f 55 54 43    TimeZone.Etc/UTC
  00 53 00 00 00 1b 49 6e    74 65 72 76 61 6c 53 74    .S....IntervalSt
  79 6c 65 00 70 6f 73 74    67 72 65 73 00 53 00 00    yle.postgres.S..
  00 14 69 73 5f 73 75 70    65 72 75 73 65 72 00 6f    ..is_superuser.o
  6e 00 53 00 00 00 1a 61    70 70 6c 69 63 61 74 69    n.S....applicati
  6f 6e 5f 6e 61 6d 65 00    70 73 71 6c 00 53 00 00    on_name.psql.S..
  00 26 64 65 66 61 75 6c    74 5f 74 72 61 6e 73 61    .&default_transa
  63 74 69 6f 6e 5f 72 65    61 64 5f 6f 6e 6c 79 00    ction_read_only.
  6f 66 66 00 53 00 00 00    1a 73 63 72 61 6d 5f 69    off.S....scram_i
  74 65 72 61 74 69 6f 6e    73 00 34 30 39 36 00 53    terations.4096.S
  00 00 00 17 44 61 74 65    53 74 79 6c 65 00 49 53    ....DateStyle.IS
  4f 2c 20 4d 44 59 00 53    00 00 00 23 73 74 61 6e    O, MDY.S...#stan
  64 61 72 64 5f 63 6f 6e    66 6f 72 6d 69 6e 67 5f    dard_conforming_
  73 74 72 69 6e 67 73 00    6f 6e 00 53 00 00 00 23    strings.on.S...#
  73 65 73 73 69 6f 6e 5f    61 75 74 68 6f 72 69 7a    session_authoriz
  61 74 69 6f 6e 00 70 6f    73 74 67 72 65 73 00 53    ation.postgres.S
  00 00 00 19 63 6c 69 65    6e 74 5f 65 6e 63 6f 64    ....client_encod
  69 6e 67 00 55 54 46 38    00 53 00 00 00 32 73 65    ing.UTF8.S...2se
  72 76 65 72 5f 76 65 72    73 69 6f 6e 00 31 37 2e    rver_version.17.
  32 20 28 44 65 62 69 61    6e 20 31 37 2e 32 2d 31    2 (Debian 17.2-1
  2e 70 67 64 67 31 32 30    2b 31 29 00 53 00 00 00    .pgdg120+1).S...
  19 73 65 72 76 65 72 5f    65 6e 63 6f 64 69 6e 67    .server_encoding
  00 55 54 46 38 00 4b 00    00 00 0c 00 00 00 53 a0    .UTF8.K.......S.
  5d 6e f3 5a 00 00 00 05    49                         ]n.Z....I

　
ここまで終えると、 psql で簡易問い合わせができる状態になっています。
https://www.postgresql.jp/document/16/html/protocol-flow.html#PROTOCOL-FLOW-SIMPLE-QUERY

各SQLの実行

引き続き、各SQLを psql で実行していきます。

まずは CREATE TABLE を実行します。

postgres=# create table apples (id int, name varchar(255));
CREATE TABLE

　
ngrepのターミナルにて通信を確認できました。

T 127.0.0.1:38518 -> 127.0.0.1:15432 [AP] #192
  51 00 00 00 35 63 72 65    61 74 65 20 74 61 62 6c    Q...5create tabl
  65 20 61 70 70 6c 65 73    20 28 69 64 20 69 6e 74    e apples (id int
  2c 20 6e 61 6d 65 20 76    61 72 63 68 61 72 28 32    , name varchar(2
  35 35 29 29 3b 00                                     55));.

T 127.0.0.1:15432 -> 127.0.0.1:38518 [AP] #194
  43 00 00 00 11 43 52 45    41 54 45 20 54 41 42 4c    C....CREATE TABL
  45 00 5a 00 00 00 05 49                               E.Z....I

　
続いて、INSERT を実行します。

postgres=# insert into apples values (1, 'shinano_gold'), (2, 'fuji');
INSERT 0 2

　
これも、ngrepのターミナルにて通信を確認できました。

T 127.0.0.1:38518 -> 127.0.0.1:15432 [AP] #397
  51 00 00 00 40 69 6e 73    65 72 74 20 69 6e 74 6f    Q...@insert into
  20 61 70 70 6c 65 73 20    76 61 6c 75 65 73 20 28     apples values (
  31 2c 20 27 73 68 69 6e    61 6e 6f 5f 67 6f 6c 64    1, 'shinano_gold
  27 29 2c 20 28 32 2c 20    27 66 75 6a 69 27 29 3b    '), (2, 'fuji');
  00                                                    .

T 127.0.0.1:15432 -> 127.0.0.1:38518 [AP] #399
  43 00 00 00 0f 49 4e 53    45 52 54 20 30 20 32 00    C....INSERT 0 2.
  5a 00 00 00 05 49                                     Z....I

　
次は SELECT です。

postgres=# select * from apples;
 id |     name     
----+--------------
  1 | shinano_gold
  2 | fuji
(2 rows)

　
今まで同様、ngrepのターミナルにて通信を確認できました。

T 127.0.0.1:38518 -> 127.0.0.1:15432 [AP] #471
  51 00 00 00 1a 73 65 6c    65 63 74 20 2a 20 66 72    Q....select * fr
  6f 6d 20 61 70 70 6c 65    73 3b 00                   om apples;.

T 127.0.0.1:15432 -> 127.0.0.1:38518 [AP] #473
  54 00 00 00 32 00 02 69    64 00 00 00 40 04 00 01    T...2..id...@...
  00 00 00 17 00 04 ff ff    ff ff 00 00 6e 61 6d 65    ............name
  00 00 00 40 04 00 02 00    00 04 13 ff ff 00 00 01    ...@............
  03 00 00 44 00 00 00 1b    00 02 00 00 00 01 31 00    ...D..........1.
  00 00 0c 73 68 69 6e 61    6e 6f 5f 67 6f 6c 64 44    ...shinano_goldD
  00 00 00 13 00 02 00 00    00 01 32 00 00 00 04 66    ..........2....f
  75 6a 69 43 00 00 00 0d    53 45 4c 45 43 54 20 32    ujiC....SELECT 2
  00 5a 00 00 00 05 49                                  .Z....I

通信の終了

これで一通りのSQLを実行し終わったので、psql で通信を終了します。
https://www.postgresql.jp/document/16/html/protocol-flow.html#PROTOCOL-FLOW-TERMINATION

postgres=# exit

　
ngrepのターミナルにて通信を確認できました。

T 127.0.0.1:38518 -> 127.0.0.1:15432 [AP] #483
  58 00 00 00 04                                        X....

　
ここで見た一連の流れについて、psql と PostgreSQL風TCPサーバの間で通信できればよさそうです。

Rubyで数値・文字列とバイナリ文字列間の変換をする方法について

次に、Rubyで通信を実現するための方法を調べます。

今回、Socketを使ってデータをやり取りするため、Rubyの数値・文字列をバイナリ文字列へ変換する必要があります。

そこで、以下の方針で変換します。

Rubyの数値・文字列をバイナリ文字列にする場合、 Array#pack を使う
- https://docs.ruby-lang.org/ja/latest/method/Array/i/pack.html
バイナリ文字列をRubyの数値・文字列にする場合、 Strint#unpack1 を使う
- https://docs.ruby-lang.org/ja/latest/method/String/i/unpack1.html

　
例えば、Rubyの文字列をバイナリ文字列にする場合は、テンプレート A* を使って変換します。

def sb(value)
  # 文字列をバイナリ文字列にする
  [value].pack('A*')
end

　
また、Rubyの文字列にNULL終端文字列を追加して送信する場合は、テンプレート Z* を使います。

def sbn(value)
  # 文字列の末尾にNULL終端文字列を追加してバイナリ文字列にする
  [value].pack('Z*')
end

　
続いてRubyの数値についてです。

Rubyの数値をPostgreSQLのInt32という、ビッグエンディアン・32ビット符号つき整数としてバイナリ文字列にしたい場合は、 l> というテンプレートを使います。

def i32b(value)
  # ビッグエンディアン、32ビット符号つき整数としてバイナリ文字列にする
  [value].pack('l>')
end

　
また、Rubyの数値をPostgreSQLのInt16のバイナリ文字列にする場合は、 s> というテンプレートを使います。

def i16b(value)
  # ビッグエンディアン、16ビット符号つき整数としてバイナリ文字列にする
  [value].pack('s>')
end

　
逆に、バイナリ文字列からRubyの数値・文字列へ変換する場合には、packで使ったときのテンプレートは変更せず、 String#unpack1 を使います。

例えば、以下はバイナリ文字列をRubyの数値へと変換しています。

def b32i(value)
  # バイナリ文字列を 32ビット符号つき整数 にする
  value.unpack1('l>')
end

　
以上でRubyでメッセージを送受信できそうです。

RubyでPostgreSQL風TCPサーバを作る

RubyでTCPサーバを作るには、以前と同じく Socket.tcp_server_loop を使えばよさそうです。
RubyのSocket.tcp_server_loopでechoサーバを作り、その通信を ngrepで確認してみた - メモ的な思考的な

それに加え、PostgreSQLのフォーマットに従ってメッセージを送受信できれば、PostgreSQL風TCPサーバが完成しそうです。

　
メッセージをどの順番で送受信すればよいかについては、PostgreSQLの公式ドキュメントに記載されています。
https://www.postgresql.jp/document/16/html/protocol-flow.html

それを参考に、今回は

Start-upフェーズ
- https://www.postgresql.jp/document/16/html/protocol-flow.html#PROTOCOL-FLOW-START-UP
SimpleQueryフェーズ
- https://www.postgresql.jp/document/16/html/protocol-flow.html#PROTOCOL-FLOW-SIMPLE-QUERY
  - 今回実装するのは簡易問い合わせなので、 SimpleQuery というフェーズ名にした

をそれぞれ実装していきます。

　
PostgreSQL風TCPサーバの全体像はこんな感じです。

Socket.tcp_server_loop(POSTGRESQLISH_SERVER_PORT) do |sock, addr_info|
  puts 'Start flow'

  startup_phase(sock)

  loop do
    break unless simple_query_phase(sock)
  end

ensure
  sock.close

  puts 'End flow'
end

Start-upフェーズ

まずはStart-upフェーズである start_up_phase 関数を実装します。

SSL Requestの送受信

psqlとPostgreSQL間の通信では、最初にSSL Requestが流れていました。
https://www.postgresql.jp/document/16/html/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-SSLREQUEST

T 127.0.0.1:38518 -> 127.0.0.1:15432 [AP] #4
  00 00 00 08 04 d2 16 2f                               ......./

T 127.0.0.1:15432 -> 127.0.0.1:38518 [AP] #6
  4e                                                    N

　
今回はSSL対応しないことから、参考にした記事同様、

psqlから受信したデータに対しては何も行わない
psqlへ N という文字を送信する

として ssl_request 関数に実装します。

なお、Rubyで N だけ送信したい場合は、 socket#write の引数として N というリテラル文字を渡せばOKでした。

def ssl_request(sock)
  # SSL Request で受け取った値は捨てて良い
  # 厳密には検証したほうが良さそう
  sock.recvmsg

  # SSL接続が不可なので、 'N' を返す
  sock.write 'N'
end

StartupMessageの受信

psqlとPostgreSQL間の通信では、SSL Requestの後、psqlからPostgreSQLへStartupMessageが流れています。
https://www.postgresql.jp/document/16/html/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-STARTUPMESSAGE

T 127.0.0.1:38518 -> 127.0.0.1:15432 [AP] #8
  00 00 00 54 00 03 00 00    75 73 65 72 00 70 6f 73    ...T....user.pos
  74 67 72 65 73 00 64 61    74 61 62 61 73 65 00 70    tgres.database.p
  6f 73 74 67 72 65 73 00    61 70 70 6c 69 63 61 74    ostgres.applicat
  69 6f 6e 5f 6e 61 6d 65    00 70 73 71 6c 00 63 6c    ion_name.psql.cl
  69 65 6e 74 5f 65 6e 63    6f 64 69 6e 67 00 55 54    ient_encoding.UT
  46 38 00 00                                           F8..

　
今回は簡易的な実装とすることから、サーバはStartupMessageの内容を使いません。

そこで、参考にした記事同様、Socketから読み込むもののすぐに捨てるよう、 startup_message 関数に実装します。

def startup_message(sock)
  # Startup Message では、クライアントからパケットが送られてくるが、すべて捨てて良い
  sock.recvmsg
end

AuthenticationOk・ParameterStatus・ReadyForQueryの送信

StartupMessageの後は、AuthenticationOK・ParameterStatus・ReadyForQueryが一括でサーバからクライアントへ送信されています。

T 127.0.0.1:15432 -> 127.0.0.1:38518 [AP] #10
  52 00 00 00 08 00 00 00    00 53 00 00 00 17 69 6e    R........S....in
  5f 68 6f 74 5f 73 74 61    6e 64 62 79 00 6f 66 66    _hot_standby.off
  00 53 00 00 00 19 69 6e    74 65 67 65 72 5f 64 61    .S....integer_da
  74 65 74 69 6d 65 73 00    6f 6e 00 53 00 00 00 15    tetimes.on.S....
  54 69 6d 65 5a 6f 6e 65    00 45 74 63 2f 55 54 43    TimeZone.Etc/UTC
  00 53 00 00 00 1b 49 6e    74 65 72 76 61 6c 53 74    .S....IntervalSt
  79 6c 65 00 70 6f 73 74    67 72 65 73 00 53 00 00    yle.postgres.S..
  00 14 69 73 5f 73 75 70    65 72 75 73 65 72 00 6f    ..is_superuser.o
  6e 00 53 00 00 00 1a 61    70 70 6c 69 63 61 74 69    n.S....applicati
  6f 6e 5f 6e 61 6d 65 00    70 73 71 6c 00 53 00 00    on_name.psql.S..
  00 26 64 65 66 61 75 6c    74 5f 74 72 61 6e 73 61    .&default_transa
  63 74 69 6f 6e 5f 72 65    61 64 5f 6f 6e 6c 79 00    ction_read_only.
  6f 66 66 00 53 00 00 00    1a 73 63 72 61 6d 5f 69    off.S....scram_i
  74 65 72 61 74 69 6f 6e    73 00 34 30 39 36 00 53    terations.4096.S
  00 00 00 17 44 61 74 65    53 74 79 6c 65 00 49 53    ....DateStyle.IS
  4f 2c 20 4d 44 59 00 53    00 00 00 23 73 74 61 6e    O, MDY.S...#stan
  64 61 72 64 5f 63 6f 6e    66 6f 72 6d 69 6e 67 5f    dard_conforming_
  73 74 72 69 6e 67 73 00    6f 6e 00 53 00 00 00 23    strings.on.S...#
  73 65 73 73 69 6f 6e 5f    61 75 74 68 6f 72 69 7a    session_authoriz
  61 74 69 6f 6e 00 70 6f    73 74 67 72 65 73 00 53    ation.postgres.S
  00 00 00 19 63 6c 69 65    6e 74 5f 65 6e 63 6f 64    ....client_encod
  69 6e 67 00 55 54 46 38    00 53 00 00 00 32 73 65    ing.UTF8.S...2se
  72 76 65 72 5f 76 65 72    73 69 6f 6e 00 31 37 2e    rver_version.17.
  32 20 28 44 65 62 69 61    6e 20 31 37 2e 32 2d 31    2 (Debian 17.2-1
  2e 70 67 64 67 31 32 30    2b 31 29 00 53 00 00 00    .pgdg120+1).S...
  19 73 65 72 76 65 72 5f    65 6e 63 6f 64 69 6e 67    .server_encoding
  00 55 54 46 38 00 4b 00    00 00 0c 00 00 00 53 a0    .UTF8.K.......S.
  5d 6e f3 5a 00 00 00 05    49                         ]n.Z....I

　
これはPostgreSQLのドキュメント

AuthenticationOkを受け取った後、フロントエンドはさらにサーバからのメッセージを待機する必要があります。この段階で、バックエンドプロセスが起動し、このフロントエンドは単なる関心を有する第三者となります。開始試行が失敗（ErrorResponse）するか、サーバが要求されたマイナープロトコルバージョンを拒否する（NegotiateProtocolVersion）可能性がまだありますが、通常、バックエンドは何らかのParameterStatusメッセージ、BackendKeyData、そして最後にReadyForQueryを送信します。

55.2. メッセージの流れ

という記載に一致します。

　
今回の実装では

「AuthenticationOkを受け取った後、フロントエンドはさらにサーバからのメッセージを待機する必要があります。」より、まずはAuthenticationOkのみをサーバから送信する
「何らかのParameterStatusメッセージ、BackendKeyData」より、左記はなくてもよさそうなので、サーバからは送信しない
「最後にReadyForQueryを送信」より、最後にReadyForQueryをサーバから送信する

とします。

つまり、

AuthenticationOk
ReadyForQuery

の順でサーバから送信します。

AuthenticationOkについて

参考記事同様、DockerのPostgreSQLを POSTGRES_HOST_AUTH_METHOD=trust で起動していたことから、最初に AuthenticationOk の 52 00 00 00 08 00 00 00 00 があります。
https://www.postgresql.jp/document/16/html/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-AUTHENTICATIONOK

　
今回は認証は不要とすることから、常に「認証が成功した」となるよう、

1バイトで、文字の R
Int32 (4バイト) で、数値の 8
Int32 (4バイト) で、数値の 0

を送信する send_authentication_ok 関数を実装します。

Rubyでは、バイト文字列化した各項目を + で連結することで、メッセージ全体になります。後はそれを送信すれば良さそうです。

def send_authentication_ok(sock)
  msg = sb('R') + i32b(8) + i32b(0)
  sock.write msg
end

今回は

1バイトで、文字の Z
Int32 (4バイト) で、数値の 5
Int32 (4バイト) で、数値の I
- 今回はトランザクションを扱わないため、「トランザクションブロックにない状態」で良さそう

を送信する ready_for_query_message 関数を実装します。

def send_ready_for_query(sock)
  sock.write sb('Z') + i32b(5) + sb('I')
end

SimpleQueryフェーズ

続いて、SimpleQueryフェーズです。

ここでは

フロントエンドがQueryメッセージをバックエンドに送信することで、簡易問い合わせサイクルが開始されます。このメッセージには、テキスト文字列で表現されたSQLコマンド（またはコマンド）が含まれます。そうすると、バックエンドは、問い合わせコマンド文字列の内容に応じて1つ以上の応答を送信し、最終的にReadyForQueryを応答します。 ReadyForQueryは、新しいコマンドを安全に送信できることをフロントエンドに知らせます。

https://www.postgresql.jp/document/16/html/protocol-flow.html#PROTOCOL-FLOW-SIMPLE-QUERY

という処理の流れを実装します。

Queryの受信

今回の実装では、SimpleQueryフェーズでクライアントからメッセージを受信した時

Queryメッセージなのかどうかを確認
- Queryメッセージの場合、各SQLコマンドの処理へ進む
- Queryメッセージでない場合、終了とみなしてSocketを閉じる

という処理を行うようにします。

　
まず、Queryメッセージのフォーマットを見てみます。
https://www.postgresql.jp/document/16/html/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-QUERY

すると、最初の1バイトが Q であればQueryメッセージとみなそうです。そこで、1バイトだけ読み込む関数 receive_tag を用意します。

def receive_tag(sock)
  sock.readbyte
end

　
読み込んだ値を確認し、

Q でない場合は、処理を終了する
Q の場合は、メッセージ内容を取得する

とします。

tag = receive_tag(sock)

# 簡易問い合わせでない場合、処理を終了する
# なお、 sock.recv したときには数値になっているので、文字コード変換が必要
return false if tag.chr != 'Q'

# メッセージ内容を取得する
sql = receive_message_contents(sock)

　
メッセージ内容はこんな感じで取得します。

# 自身を含むメッセージ内容のバイト単位の長さ (定義的にはInt32なので、4バイト)
LENGTH_OF_MESSAGE_CONTENTS = 4

def receive_message_contents(sock)
  length = sock.read LENGTH_OF_MESSAGE_CONTENTS

  # lengthには、メッセージ内容の長さ + バイト単位の長さ が設定されている
  # すでにバイト単位の長さの情報は recv しているので、それ以外の長さをメッセージ内容の長さと考えて recv する
  # なお、length はバイナリ文字列なので、32bit整数へと変換してから演算する
  sql = sock.read(b32i(length) - LENGTH_OF_MESSAGE_CONTENTS)

  # SQL文字列を受信した想定だが、末尾に NULL終端文字列 が入っている可能性がある
  # そこで、 unpack('A*') で NULL終端文字列 を削除した後、再度 pack('A*') して、SQL文字列を取得している
  # また、SQLに大文字・小文字が混在していると取り扱いが手間かもしれないため、小文字だけにしておく
  sql.unpack('A*').pack('A*').downcase
end

CommandCompleteの送信

続いて、Queryメッセージに対する、サーバからの応答を送信します。

今回は常に「SQLコマンドが正常に終了」とするため、主にCommandCompleteメッセージを使って正常終了情報を送信します。
https://www.postgresql.jp/document/16/html/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-COMMANDCOMPLETE

送信するCommandCompleteメッセージはSQLごとに異なります。今回は

CREATE TABLE
INSERT
SELECT

のみ対応します。

CREATE TABLEの場合

CREATE TABLEについては、メッセージフォーマットには記載されていません。

そこで、psqlとPostgreSQLの通信内容を参照すると、

T 127.0.0.1:15432 -> 127.0.0.1:38518 [AP] #194
  43 00 00 00 11 43 52 45    41 54 45 20 54 41 42 4c    C....CREATE TABL
  45 00 5a 00 00 00 05 49                               E.Z....I

のうちの、 43 00 00 00 11 43 52 45 41 54 45 20 54 41 42 4c 45 00 がCommandCompleteメッセージと分かります。

これより、クライアントには「CREATE TABLE という文字列の後にNULL終端文字列を加えたもの」を送信すれば良さそうです。

　
これらをもとに、関数 send_command_complete_of_create_table を実装します。

# 自身を含むメッセージ内容のバイト単位の長さ (定義的にはInt32なので、4バイト)
LENGTH_OF_MESSAGE_CONTENTS = 4

def send_command_complete_of_create_table(sock)
  value = 'CREATE TABLE'
  value_length = value.bytesize + 1 # NULL終端文字列の分も長さとして計算する

  # CommandCompleteのStringタグにはNULL終端文字列が必要
  msg = sb('C') + i32b(value_length + LENGTH_OF_MESSAGE_CONTENTS) + sbn(value)
  sock.write msg
end

INSERTの場合

メッセージフォーマットの定義を見ると

INSERTコマンドの場合、タグはINSERT oid rowsです。ここでrowsは挿入された行数です。かつてoidは、rowsが1、かつ、対象テーブルがOIDを持つ場合、挿入された行のオブジェクトIDでしたが、もはやOID列はサポートされていません。ですからoidは常に0です。

です。

そのため、CREATE TABLEと似たような関数 send_command_complete_of_insert を実装します。

# 自身を含むメッセージ内容のバイト単位の長さ (定義的にはInt32なので、4バイト)
LENGTH_OF_MESSAGE_CONTENTS = 4

# PostgreSQLのOID列の値。ただしOID列はサポートされていないため、固定値を返すことになる
OID = 0

def send_command_complete_of_insert(sock, sql)
  # 今回のINSERTは以下のSQL
  # insert into apples values (1, 'shinano_gold'), (2, 'fuji');
  # そのため、簡易的な実装として、 `(` の数 == 行数とみなす
  column_count = sql.count('(')
  value = "INSERT #{OID} #{column_count}"

  value_length = value.bytesize + 1 # NULL終端文字列の分も長さとして計算する

  # CREATE TABLE同様
  msg = sb('C') + i32b(value_length + LENGTH_OF_MESSAGE_CONTENTS) + sbn(value)
  sock.write msg
end

SELECTの場合

PostgreSQLのドキュメントによると

SELECT問い合わせ（あるいは、EXPLAINやSHOWなどの行集合を返す他の問い合わせ）に対する応答は、通常、RowDescription、0個以上のDataRowメッセージ、そしてその後のCommandCompleteから構成されます。

https://www.postgresql.jp/document/16/html/protocol-flow.html#PROTOCOL-FLOW-SIMPLE-QUERY

とあります。

SELECTではCommandComplete以外にも送信するメッセージがあることから、それぞれ見ていきます。

RowDescriptionの送信

公式ドキュメントによると、RowDescriptionは

このメッセージには、行の列レイアウトに関する説明が含まれます。このメッセージの後に、フロントエンドに返される各行に対するDataRowメッセージが続きます。

https://www.postgresql.jp/document/16/html/protocol-flow.html#PROTOCOL-FLOW-SIMPLE-QUERY

という役割があります。

また、RowDescriptionメッセージフォーマットは以下に記載されています。
https://www.postgresql.jp/document/16/html/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-ROWDESCRIPTION

　
psql・PostgreSQLの通信内容や参考記事を参照したところ、こんな感じで設定すれば良さそうでした。

String: フィールド名
- 列名をセット
- NULL終端文字列が必要
Int32: テーブルのオブジェクトID
- 任意の値をセット
Int16: 列の属性番号
- 1始まりの連番
Int32: データ型のオブジェクトID
- オブジェクトID(OID)の値はドキュメントには記載されていないため、ソースコードを確認
  - int型 (int4) の場合、OIDは 23
    - https://github.com/postgres/postgres/blob/da256a4a7fdcca35fe7ca808686ad3de6ee22306/src/include/catalog/pg_type.dat#L72-L76
  - varchar型の場合、OIDは 1043
    - https://github.com/postgres/postgres/blob/da256a4a7fdcca35fe7ca808686ad3de6ee22306/src/include/catalog/pg_type.dat#L281-L287
Int16: データ型の大きさ(pg_type.typlen)
- pg_type.typelen の値はドキュメントに記載されていないため、こちらも同ソースコードを確認
  - int型の場合 4
  - varchar型の場合 -1
Int32: 型修飾子
- int型の場合、長さを指定しないため -1
- varchar型の場合、定義した長さを指定する。ただし、定義した長さ + 4 とする
  - +4 が必要な理由は不明...
Int16: 書式コード
- 常に 0 でよさそう

　
今回は create table apples (id int, name varchar(255)); というSQLでテーブルを定義と仮定して実装します。

この場合、 id 列の場合は次のような実装となります。

def col_id
  field_name = sbn('id')  # field name には NULL終端文字列 が必要
  object_id_of_table = 16385 # 適当な値
  column_id = 1

  # pg_type を参照する項目
  # https://www.postgresql.jp/document/16/html/catalog-pg-type.html
  pg_type_oid = i32b(23)
  pg_type_typlen = i16b(4)

  # pg_attribute を参照する項目
  # https://www.postgresql.jp/document/16/html/catalog-pg-attribute.html
  pg_attribute_attypmod = i32b(-1)
  format_code = i16b(0)

  field_name + i32b(object_id_of_table) + i16b(column_id) + pg_type_oid + pg_type_typlen + pg_attribute_attypmod + format_code
end

　
また、 name 列の場合は以下です。

def col_name
  field_name = sbn('name')  # field name には NULL終端文字列 が必要
  object_id_of_table = 16385 # 適当な値
  column_id = 2

  # pg_type を参照する項目
  # https://www.postgresql.jp/document/16/html/catalog-pg-type.html
  pg_type_oid = i32b(1043)
  pg_type_typlen = i16b(-1)  # 可変長なので、 -1

  # pg_attribute を参照する項目
  # https://www.postgresql.jp/document/16/html/catalog-pg-attribute.html
  pg_attribute_attypmod = i32b(255+4)  # 指定された最大長に 4 を加える => 今回は 255 なので、それに4を加える
  format_code = i16b(0)

  field_name + i32b(object_id_of_table) + i16b(column_id) + pg_type_oid + pg_type_typlen + pg_attribute_attypmod + format_code
end

　
これらを合わせて、RowDescriptionメッセージとして送信します。

def send_row_description(sock)
  contents = col_id + col_name

  # length_of_message_contentsはInt32なので4バイト、number_of_columnはInt16なので2バイト
  # 上記2つに各列の内容を合わせたものが、メッセージ全体の長さ
  bytesize_for_length_of_message_contents = 4
  bytesize_for_number_of_column = 2
  message_bytesize = bytesize_for_length_of_message_contents + bytesize_for_number_of_column + contents.length

  # 今回のデータは2列
  number_of_column = 2

  msg = sb('T') + i32b(message_bytesize) + i16b(number_of_column) + contents
  sock.write(msg)
end

DataRowの送信

続いて、DataRowメッセージを送信します。

公式ドキュメントによると、DataRowは

SELECTやFETCHなどの問い合わせで返される行の集合の1つです。

https://www.postgresql.jp/document/16/html/protocol-flow.html#PROTOCOL-FLOW-SIMPLE-QUERY

とのことです。メッセージフォーマットに従い、1行で1DataRowメッセージを送信すれば良さそうです。
https://www.postgresql.jp/document/16/html/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-DATAROW

　
今回は insert into apples values (1, 'shinano_gold'), (2, 'fuji'); で登録したデータを全件取得すると仮定します。

まずは、フィールドのペアを作る data_column 関数です。

なお、列の値については、文字列が設定される場合であってもNULL終端文字列は不要です。

def data_column(column_number, column_value)
  i32b(column_value.to_s.length) + sb(column_value.to_s)
end

　
続いて、1つの行を作る data_row 関数です。

def data_row(col1_value, col2_value)
  # 1列目(id)の値
  content_of_col_id = data_column(1, col1_value)
  # 2列目(name)の値
  content_of_col_name = data_column(2, col2_value)
  # メッセージ内容は、1行目と2行目を連結させたもの
  contents = content_of_col_id + content_of_col_name

  # length_of_message_contentsはInt32なので4バイト、number_of_columnはInt16なので2バイト
  # 上記2つに各列の内容を合わせたものが、メッセージ全体の長さ
  bytesize_for_length_of_message_contents = 4
  bytesize_for_number_of_column = 2
  message_bytesize = bytesize_for_length_of_message_contents + bytesize_for_number_of_column + contents.length

  # 今回のデータは2列
  number_of_column = 2

  sb('D') + i32b(message_bytesize) + i16b(number_of_column) + contents
end

　
最後に、DataRowメッセージを送信する関数 send_data_row です。

def send_data_row(sock)
  # 1行目のデータ
  data_row_of_shinano_gold = data_row(1, 'shinano_gold')
  # 2行目のデータ
  data_row_of_fuji = data_row(2, 'fuji')

  msg = data_row_of_shinano_gold + data_row_of_fuji
  sock.write msg
end

CommandCompleteの送信

SELECTの場合、CommandCompleteメッセージは

SELECTまたはCREATE TABLE ASの場合、タグはSELECT rowsとなります。ここでrowsは取り込んだ行数です。

https://www.postgresql.jp/document/16/html/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-COMMANDCOMPLETE

を送信すれば良さそうです。

CommandCompleteのメッセージフォーマットは CREATE TABLE などと同じなことから、実装も同じ感じになります。

def send_command_complete_of_select(sock)
  # 結果行数は、今回固定で 2 とする
  value = "SELECT 2"

  value_length = value.bytesize + 1 # NULL終端文字列の分も長さとして計算する

  # CREATE TABLE同様
  msg = sb('C') + i32b(value_length + LENGTH_OF_MESSAGE_CONTENTS) + sbn(value)
  sock.write msg
end

ReadyForQueryの送信

CREATE TABLE、INSERT、SELECTで CommandCompleteメッセージを送信後、最後にReadyForQueryメッセージを送信し、クライアントに制御を戻します。

ReadyForQueryメッセージを送信する実装は、Start-upフェーズと同じため、ここでは省略します。

　
以上で、PostgreSQL風TCPサーバの実装が終わりました。

ソースコード全体は、後述のリポジトリにある postgresqlish_server.rb を参照ください。

動作確認

psqlでの動作確認

では、PostgreSQL風TCPサーバを起動し、実際に psql を使って接続してコマンドを入力してみます。

# psqlでの接続
$ psql postgres postgres -h localhost -p 25432
psql (14.15 (Ubuntu 14.15-0ubuntu0.22.04.1), server 0.0.0)
Type "help" for help.


# CREATE TABLEの実行
postgres=> create table apples (id int, name varchar(255));
CREATE TABLE


# INSERTの実行
postgres=> insert into apples values (1, 'shinano_gold'), (2, 'fuji');
INSERT 0 2


# SELECTの実行
postgres=> select * from apples;
 id |     name     
----+--------------
  1 | shinano_gold
  2 | fuji
(2 rows)


# 終わり
postgres=> exit

　
いずれもPostgreSQLと同じような挙動になりました。

ngrepでの通信内容確認

psqlを実行したときの通信内容は以下です。

いずれも良さそうです。

T 127.0.0.1:58042 -> 127.0.0.1:25432 [AP] #1782
  00 00 00 08 04 d2 16 2f                               ......./

T 127.0.0.1:25432 -> 127.0.0.1:58042 [AP] #1784
  4e                                                    N

T 127.0.0.1:58042 -> 127.0.0.1:25432 [AP] #1786
  00 00 00 54 00 03 00 00    75 73 65 72 00 70 6f 73    ...T....user.pos
  74 67 72 65 73 00 64 61    74 61 62 61 73 65 00 70    tgres.database.p
  6f 73 74 67 72 65 73 00    61 70 70 6c 69 63 61 74    ostgres.applicat
  69 6f 6e 5f 6e 61 6d 65    00 70 73 71 6c 00 63 6c    ion_name.psql.cl
  69 65 6e 74 5f 65 6e 63    6f 64 69 6e 67 00 55 54    ient_encoding.UT
  46 38 00 00                                           F8..

T 127.0.0.1:25432 -> 127.0.0.1:58042 [AP] #1788
  52 00 00 00 08 00 00 00    00                         R........

T 127.0.0.1:25432 -> 127.0.0.1:58042 [AP] #1790
  5a 00 00 00 05 49                                     Z....I

T 127.0.0.1:58042 -> 127.0.0.1:25432 [AP] #1792
  51 00 00 00 35 63 72 65    61 74 65 20 74 61 62 6c    Q...5create tabl
  65 20 61 70 70 6c 65 73    20 28 69 64 20 69 6e 74    e apples (id int
  2c 20 6e 61 6d 65 20 76    61 72 63 68 61 72 28 32    , name varchar(2
  35 35 29 29 3b 00                                     55));.

T 127.0.0.1:25432 -> 127.0.0.1:58042 [AP] #1794
  43 00 00 00 11 43 52 45    41 54 45 20 54 41 42 4c    C....CREATE TABL
  45 00                                                 E.

T 127.0.0.1:25432 -> 127.0.0.1:58042 [AP] #1796
  5a 00 00 00 05 49                                     Z....I

T 127.0.0.1:58042 -> 127.0.0.1:25432 [AP] #1798
  51 00 00 00 40 69 6e 73    65 72 74 20 69 6e 74 6f    Q...@insert into
  20 61 70 70 6c 65 73 20    76 61 6c 75 65 73 20 28     apples values (
  31 2c 20 27 73 68 69 6e    61 6e 6f 5f 67 6f 6c 64    1, 'shinano_gold
  27 29 2c 20 28 32 2c 20    27 66 75 6a 69 27 29 3b    '), (2, 'fuji');
  00                                                    .

T 127.0.0.1:25432 -> 127.0.0.1:58042 [AP] #1800
  43 00 00 00 0f 49 4e 53    45 52 54 20 30 20 32 00    C....INSERT 0 2.

T 127.0.0.1:25432 -> 127.0.0.1:58042 [AP] #1802
  5a 00 00 00 05 49                                     Z....I

T 127.0.0.1:58042 -> 127.0.0.1:25432 [AP] #1804
  51 00 00 00 1a 73 65 6c    65 63 74 20 2a 20 66 72    Q....select * fr
  6f 6d 20 61 70 70 6c 65    73 3b 00                   om apples;.

T 127.0.0.1:25432 -> 127.0.0.1:58042 [AP] #1806
  54 00 00 00 32 00 02 69    64 00 00 00 40 01 00 01    T...2..id...@...
  00 00 00 17 00 04 ff ff    ff ff 00 00 6e 61 6d 65    ............name
  00 00 00 40 01 00 02 00    00 04 13 ff ff 00 00 01    ...@............
  03 00 00                                              ...

T 127.0.0.1:25432 -> 127.0.0.1:58042 [AP] #1808
  44 00 00 00 1b 00 02 00    00 00 01 31 00 00 00 0c    D..........1....
  73 68 69 6e 61 6e 6f 5f    67 6f 6c 64 44 00 00 00    shinano_goldD...
  13 00 02 00 00 00 01 32    00 00 00 04 66 75 6a 69    .......2....fuji

T 127.0.0.1:25432 -> 127.0.0.1:58042 [AP] #1810
  43 00 00 00 0d 53 45 4c    45 43 54 20 32 00          C....SELECT 2.

T 127.0.0.1:25432 -> 127.0.0.1:58042 [AP] #1812
  5a 00 00 00 05 49                                     Z....I

T 127.0.0.1:58042 -> 127.0.0.1:25432 [AP] #1814
  58 00 00 00 04                                        X....

ソースコード

GitHubに上げました。
https://github.com/thinkAmi-sandbox/tcp_servers_by_ruby

今回のプルリクはこちら。
https://github.com/thinkAmi-sandbox/tcp_servers_by_ruby/pull/2

環境

Rubyで何のRDBMS風TCPサーバを作るか検討

psql と PostgreSQL の通信をのぞいてみる

準備

DockerでPostgreSQLを準備

psql のインストール

通信内容の確認

psqlにてPostgreSQLへ接続

各SQLの実行

通信の終了

Rubyで数値・文字列とバイナリ文字列間の変換をする方法について

RubyでPostgreSQL風TCPサーバを作る

Start-upフェーズ

SSL Requestの送受信

StartupMessageの受信

AuthenticationOk・ParameterStatus・ReadyForQueryの送信

AuthenticationOkについて

ParameterStatusについて

BackendKeyDataについて

ReadyForQueryについて

SimpleQueryフェーズ

Queryの受信

CommandCompleteの送信

CREATE TABLEの場合

INSERTの場合

SELECTの場合

RowDescriptionの送信

DataRowの送信

CommandCompleteの送信

ReadyForQueryの送信

動作確認

psqlでの動作確認

ngrepでの通信内容確認

ソースコード