最近、Go の練習がてら書いていた自作 DBMS に PostgreSQL client で接続できるようになったので、そのやり方を残しておきます。(これから紹介するサンプルコードはすべて Python ですが)
psql --version psql (PostgreSQL) 13.2
pgcon の資料と PostgreSQL の公式 Document、加えて PostgreSQL server と client 間に流れるパケットを眺めると、自作DBMSは client から接続されたときにどういうパケットを返せばいいのかが見えてきます。
- https://www.pgcon.org/2014/schedule/attachments/330_postgres-for-the-wire.pdf
- https://www.postgresql.org/docs/13/protocol-message-formats.html
下記のパケットは psql で接続し、
create table hoge (id int, name varchar(255)); insert into hoge values (1, 'tanaka'), (2, 'suzuki'); select * from hoge;
を実行、接続を閉じた際に流れたパケットです。 このうち、server (172.18.0.4:5432) -> client (172.18.0.2:34058) の方向に流れたパケットを再現する server を構築できれば、client から接続できるようになります。
T 172.18.0.2:34058 -> 172.18.0.4:5432 [AP] #38 00 00 00 08 04 d2 16 2f ......./ T 172.18.0.4:5432 -> 172.18.0.2:34058 [AP] #40 4e N T 172.18.0.2:34058 -> 172.18.0.4:5432 [AP] #42 00 00 00 59 00 03 00 00 75 73 65 72 00 70 6f 73 ...Y....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 53 51 ient_encoding.SQ 4c 5f 41 53 43 49 49 00 00 L_ASCII.. T 172.18.0.4:5432 -> 172.18.0.2:34058 [AP] #44 52 00 00 00 08 00 00 00 00 53 00 00 00 1a 61 70 R........S....ap 70 6c 69 63 61 74 69 6f 6e 5f 6e 61 6d 65 00 70 plication_name.p 73 71 6c 00 53 00 00 00 1e 63 6c 69 65 6e 74 5f sql.S....client_ 65 6e 63 6f 64 69 6e 67 00 53 51 4c 5f 41 53 43 encoding.SQL_ASC 49 49 00 53 00 00 00 17 44 61 74 65 53 74 79 6c II.S....DateStyl 65 00 49 53 4f 2c 20 4d 44 59 00 53 00 00 00 19 e.ISO, MDY.S.... 69 6e 74 65 67 65 72 5f 64 61 74 65 74 69 6d 65 integer_datetime 73 00 6f 6e 00 53 00 00 00 1b 49 6e 74 65 72 76 s.on.S....Interv 61 6c 53 74 79 6c 65 00 70 6f 73 74 67 72 65 73 alStyle.postgres 00 53 00 00 00 14 69 73 5f 73 75 70 65 72 75 73 .S....is_superus 65 72 00 6f 6e 00 53 00 00 00 19 73 65 72 76 65 er.on.S....serve 72 5f 65 6e 63 6f 64 69 6e 67 00 55 54 46 38 00 r_encoding.UTF8. 53 00 00 00 32 73 65 72 76 65 72 5f 76 65 72 73 S...2server_vers 69 6f 6e 00 31 32 2e 36 20 28 44 65 62 69 61 6e ion.12.6 (Debian 20 31 32 2e 36 2d 31 2e 70 67 64 67 31 30 30 2b 12.6-1.pgdg100+ 31 29 00 53 00 00 00 23 73 65 73 73 69 6f 6e 5f 1).S...#session_ 61 75 74 68 6f 72 69 7a 61 74 69 6f 6e 00 70 6f authorization.po 73 74 67 72 65 73 00 53 00 00 00 23 73 74 61 6e stgres.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 15 strings.on.S.... 54 69 6d 65 5a 6f 6e 65 00 45 74 63 2f 55 54 43 TimeZone.Etc/UTC 00 4b 00 00 00 0c 00 00 00 55 e2 ed f9 8e 5a 00 .K.......U....Z. 00 00 05 49 ...I T 172.18.0.2:34058 -> 172.18.0.4:5432 [AP] #46 51 00 00 00 33 63 72 65 61 74 65 20 74 61 62 6c Q...3create tabl 65 20 68 6f 67 65 20 28 69 64 20 69 6e 74 2c 20 e hoge (id int, 6e 61 6d 65 20 76 61 72 63 68 61 72 28 32 35 35 name varchar(255 29 29 3b 00 ));. T 172.18.0.4:5432 -> 172.18.0.2:34058 [AP] #48 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 T 172.18.0.2:34058 -> 172.18.0.4:5432 [AP] #50 51 00 00 00 3a 69 6e 73 65 72 74 20 69 6e 74 6f Q...:insert into 20 68 6f 67 65 20 76 61 6c 75 65 73 20 28 31 2c hoge values (1, 20 27 74 61 6e 61 6b 61 27 29 2c 20 28 32 2c 20 'tanaka'), (2, 27 73 75 7a 75 6b 69 27 29 3b 00 'suzuki');. T 172.18.0.4:5432 -> 172.18.0.2:34058 [AP] #52 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 T 172.18.0.2:34058 -> 172.18.0.4:5432 [AP] #54 51 00 00 00 18 73 65 6c 65 63 74 20 2a 20 66 72 Q....select * fr 6f 6d 20 68 6f 67 65 3b 00 om hoge;. T 172.18.0.4:5432 -> 172.18.0.2:34058 [AP] #56 54 00 00 00 32 00 02 69 64 00 00 00 40 03 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 03 00 02 00 00 04 13 ff ff 00 00 01 ...@............ 03 00 00 44 00 00 00 15 00 02 00 00 00 01 31 00 ...D..........1. 00 00 06 74 61 6e 61 6b 61 44 00 00 00 15 00 02 ...tanakaD...... 00 00 00 01 32 00 00 00 06 73 75 7a 75 6b 69 43 ....2....suzukiC 00 00 00 0d 53 45 4c 45 43 54 20 32 00 5a 00 00 ....SELECT 2.Z.. 00 05 49 ..I T 172.18.0.2:34058 -> 172.18.0.4:5432 [AP] #58 58 00 00 00 04 X....
パケットを追う
SSL request
まずはじめに飛んできたパケットを見てみます。
T 172.18.0.2:34058 -> 172.18.0.4:5432 [AP] #38 00 00 00 08 04 d2 16 2f ......./ T 172.18.0.4:5432 -> 172.18.0.2:34058 [AP] #40 4e N
最初に流れていたパケットは SSL request です。
00 00 00 08 の部分がパケットの長さ(自身を含む。BigEndian)を表し、04 d2 16 2f が ssl code です。
postgres のソースコードを見ると #define NEGOTIATE_SSL_CODE PG_PROTOCOL(1234,5679) という定義がありますが、1234,5679 の部分を BigEndian にすると 04d2162f となり、飛んできた packet と一致します。
#!/usr/bin/env python3 import struct print(struct.pack('>hh', 1234, 5679).hex()) #=> 04d2162f
server は SSL に対応していない場合 N を返します。
参考
- https://github.com/postgres/postgres/blob/REL_13_2/src/include/libpq/pqcomm.h#L205
- https://github.com/postgres/postgres/blob/REL_13_2/src/backend/postmaster/postmaster.c#L2059-L2061
- https://postgresconf.org/system/events/document/000/000/183/pgconf_us_v4.pdf
startup
T 172.18.0.2:34058 -> 172.18.0.4:5432 [AP] #42 00 00 00 59 00 03 00 00 75 73 65 72 00 70 6f 73 ...Y....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 53 51 ient_encoding.SQ 4c 5f 41 53 43 49 49 00 00 L_ASCII.. T 172.18.0.4:5432 -> 172.18.0.2:34058 [AP] #44 52 00 00 00 08 00 00 00 00 53 00 00 00 1a 61 70 R........S....ap 70 6c 69 63 61 74 69 6f 6e 5f 6e 61 6d 65 00 70 plication_name.p 73 71 6c 00 53 00 00 00 1e 63 6c 69 65 6e 74 5f sql.S....client_ 65 6e 63 6f 64 69 6e 67 00 53 51 4c 5f 41 53 43 encoding.SQL_ASC 49 49 00 53 00 00 00 17 44 61 74 65 53 74 79 6c II.S....DateStyl 65 00 49 53 4f 2c 20 4d 44 59 00 53 00 00 00 19 e.ISO, MDY.S.... 69 6e 74 65 67 65 72 5f 64 61 74 65 74 69 6d 65 integer_datetime 73 00 6f 6e 00 53 00 00 00 1b 49 6e 74 65 72 76 s.on.S....Interv 61 6c 53 74 79 6c 65 00 70 6f 73 74 67 72 65 73 alStyle.postgres 00 53 00 00 00 14 69 73 5f 73 75 70 65 72 75 73 .S....is_superus 65 72 00 6f 6e 00 53 00 00 00 19 73 65 72 76 65 er.on.S....serve 72 5f 65 6e 63 6f 64 69 6e 67 00 55 54 46 38 00 r_encoding.UTF8. 53 00 00 00 32 73 65 72 76 65 72 5f 76 65 72 73 S...2server_vers 69 6f 6e 00 31 32 2e 36 20 28 44 65 62 69 61 6e ion.12.6 (Debian 20 31 32 2e 36 2d 31 2e 70 67 64 67 31 30 30 2b 12.6-1.pgdg100+ 31 29 00 53 00 00 00 23 73 65 73 73 69 6f 6e 5f 1).S...#session_ 61 75 74 68 6f 72 69 7a 61 74 69 6f 6e 00 70 6f authorization.po 73 74 67 72 65 73 00 53 00 00 00 23 73 74 61 6e stgres.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 15 strings.on.S.... 54 69 6d 65 5a 6f 6e 65 00 45 74 63 2f 55 54 43 TimeZone.Etc/UTC 00 4b 00 00 00 0c 00 00 00 55 e2 ed f9 8e 5a 00 .K.......U....Z. 00 00 05 49 ...I
次にくるのは startup msg です。ASCII からもなんとなく読み取れると思いますが、user は誰で database は何を使いたいといった情報が client から server に流れています。 先頭4バイトは packet の長さが BigEndian で入っています。
単に自作 DBMS に postgres client から接続させるためだけだったら user 情報などはいらないので client から送られた情報はすべて無視して構いません。
次の server から client に送るパケットからパケットの構造が変わります。 先頭1バイトは msg の tag を表し、続く4バイトがパケットのサイズ、最後にパケットのデータ部分になります。
最初の 52 00 00 00 08 00 00 00 00 は AuthenticationOk パケットです。
このとき、postgres server をPOSTGRES_HOST_AUTH_METHOD=trust で起動しており password なしでログインできる状態だったため、無条件で AuthenticationOk が飛んでいます。
自作 DBMS でも難しいことは避けるために同じ方法を使います。
続くパケットでは S....hoge.piyo といった形式のものがたくさん見られますが、これらは ParameterStatus です。見てわかるとおり、encoding は何を使うや、timezone は何かといった情報が含まれています。単に接続させるためにはこれらの情報はなくてもいいので無視します。(python の psycopg から接続する場合は client_encoding を返してあげる必要があります。)
途中の接続するだけだったらいらない部分は飛ばして最後の 5a 00 00 00 05 49 はReadyForQuery パケットです。これを送ると client からクエリを投げられる状態になります。
まとめると
- client からパケットを受け取る (内容はすべて無視)
- 'N' を送る
- client からパケットを受け取る (内容はすべて無視)
52 00 00 00 08 00 00 00 005a 00 00 00 05 49を client に送る
とすると、client からクエリを受け取る準備ができます。
- PostgreSQL: Documentation: 13: 52.2. Message Flow
- PostgreSQL: Documentation: 13: 52.7. Message Formats
python でのコード例
import socket import struct addr, port = "127.0.0.1", 15432 PACKET_LENGTH = 4 def read_packet(client_socket): size = struct.unpack(">I", client_socket.recv(PACKET_LENGTH))[0] payload = client_socket.recv(size-PACKET_LENGTH) return payload def startup(client_socket): read_packet(client_socket) client_socket.sendall(b"N") read_packet(client_socket) client_socket.sendall(b"\x52\x00\x00\x00\x08\x00\x00\x00\x00") client_socket.sendall(b"\x5a\x00\x00\x00\x05\x49") if __name__ == '__main__': server_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM) server_socket.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, True) server_socket.bind((addr, port)) server_socket.listen() client_socket, (client_address, client_port) = server_socket.accept() startup(client_socket)
query packet
T 172.18.0.2:34058 -> 172.18.0.4:5432 [AP] #46 51 00 00 00 33 63 72 65 61 74 65 20 74 61 62 6c Q...3create tabl 65 20 68 6f 67 65 20 28 69 64 20 69 6e 74 2c 20 e hoge (id int, 6e 61 6d 65 20 76 61 72 63 68 61 72 28 32 35 35 name varchar(255 29 29 3b 00 ));. T 172.18.0.4:5432 -> 172.18.0.2:34058 [AP] #48 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 et
Q から始まるパケットは Query パケットです。
| tag | length | string | から構成されます。このとき、string は null 末端です。
クエリが実行されると server は CommandComplete パケット(tag ’C’) と ReadyForQuery パケットを client に返しています。
CommandComplete パケットは Query パケット同様 | tag | length | string | というレイアウトになっています。string として何でも入れられます。
クエリを投げるフェーズに入ってからの基本的な流れは Query パケットもらって、CommandComplete, ReadyForQuery パケットを返すの繰り返しになります。
あとで説明しますが SELECT の場合はこれに加えてレコードのデータも送ることになります。
CommandComplete の string には何でも入れられると書きましたが、試しに入力したものをそのまま返す echo server を作ってみました。
import socket import struct addr, port = "127.0.0.1", 15432 PACKET_LENGTH = 4 ReadyForQuery = b"\x5a\x00\x00\x00\x05\x49" def read_packet(client_socket): size = struct.unpack(">I", client_socket.recv(PACKET_LENGTH))[0] payload = client_socket.recv(size-PACKET_LENGTH) return payload def startup(client_socket): read_packet(client_socket) client_socket.sendall(b"N") read_packet(client_socket) client_socket.sendall(b"\x52\x00\x00\x00\x08\x00\x00\x00\x00") client_socket.sendall(ReadyForQuery) def read_regular_packet(client_socket): tag = client_socket.recv(1) payload = read_packet(client_socket) return payload def send_packet(client_socket, data): size = struct.pack(">I", len(data)+PACKET_LENGTH) packet = b"C" + size + data client_socket.sendall(packet) client_socket.sendall(ReadyForQuery) if __name__ == '__main__': server_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM) server_socket.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, True) server_socket.bind((addr, port)) server_socket.listen() client_socket, (client_address, client_port) = server_socket.accept() startup(client_socket) while True: query = read_regular_packet(client_socket) send_packet(client_socket, query)
$ psql -h 127.0.0.1 -p 15432 psql (13.2, server 0.0.0) Type "help" for help. arch=> select * from hoge; select * from hoge; arch=> hoge ; hoge ; arch=> piyo arch-> fuga; piyo fuga;
psql で打ったものがそのまま返ってくるというなんとも不思議な体験をすることができます。
自作DBMSで使うときは飛んできた Query を作った DBMS に投げてよしなに使ってください。
SELECT
次に SELECT の結果を表示する部分のパケットを見ていきます。
postgres=# select * from hoge; id | name ----+-------- 1 | tanaka 2 | suzuki (2 rows)
上の
id | name ----+-------- 1 | tanaka 2 | suzuki
の部分です。(2 rows) の部分は psql がよしなに作ってくれます。
T 172.18.0.2:34058 -> 172.18.0.4:5432 [AP] #54 51 00 00 00 18 73 65 6c 65 63 74 20 2a 20 66 72 Q....select * fr 6f 6d 20 68 6f 67 65 3b 00 om hoge;. T 172.18.0.4:5432 -> 172.18.0.2:34058 [AP] #56 54 00 00 00 32 00 02 69 64 00 00 00 40 03 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 03 00 02 00 00 04 13 ff ff 00 00 01 ...@............ 03 00 00 44 00 00 00 15 00 02 00 00 00 01 31 00 ...D..........1. 00 00 06 74 61 6e 61 6b 61 44 00 00 00 15 00 02 ...tanakaD...... 00 00 00 01 32 00 00 00 06 73 75 7a 75 6b 69 43 ....2....suzukiC 00 00 00 0d 53 45 4c 45 43 54 20 32 00 5a 00 00 ....SELECT 2.Z.. 00 05 49 ..I
最初の SELECT の部分は CREATE TABLE のときと同じなので説明は飛ばします。
SELECT の結果はカラム名や型の情報が書かれた tag T の RowDescription パケットと、実際のデータが入っている tag D の DataRow パケットからなります。
まずは RowDescription パケットから見ていきます。
54 00 00 00 32 00 02 69 64 00 00 00 40 03 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 03 00 02 00 00 04 13 ff ff 00 00 01 ...@............ 03 00 00 ...
公式 doc の RowDescription のところを見ながらパケットを分解すると以下のようになります。
| tag | Byte1('T') | 54 | ||
|---|---|---|---|---|
| message length | int32 | 00 00 00 32 | packet の長さ | |
| # field | int16 | 00 02 | カラム数 | |
| field name | string | 69 64 00 (id\0) | 6e 61 6d 65 00 (name\0) | カラム名 |
| object id of the table | int32 | 00 00 40 03 | 00 00 40 03 | テーブルの object id. pg_class テーブル の oid を BigEndian で表示したもの |
| cols id | int16 | 00 01 | 00 02 | カラムのID |
| object id of datatype | int32 | 00 00 00 17 | 00 00 04 13 | pg_type テーブルに書かれている oid に対応 |
| data type size | int16 | 00 04 | ff ff | pg_type テーブルに書かれている typlen に対応 |
| type modifier | int32 | ff ff ff ff | 00 00 01 03 | pg_attribute テーブルに書かれている atttypmod に対応 |
| format code | int16 | 00 00 | 00 00 |
テーブルの object id のところは自作 DBMS にとってはどうでもいい値なので適当な数字を入れていればokです。
object id of datatype / data type size / type modifier は client に server から飛んできた値が何の型になるのかを教えるためにありますが、単に psql で表示させたいだけならば、varchar を表す 00 00 04 13 / ff ff / 00 00 01 03 を常に送っていれば大丈夫です。
次に DataRow パケットを見ていきましょう。
44 00 00 00 15 00 02 00 00 00 01 31 00 D..........1. 00 00 06 74 61 6e 61 6b 61 44 00 00 00 15 00 02 ...tanakaD...... 00 00 00 01 32 00 00 00 06 73 75 7a 75 6b 69 ....2....suzuki
ASCII を見ればわかるように D という文字が2つ見えるので、この中には DataRow が2つ含まれています。
1つ目の DataRow を分解すると以下のようになります。
| tag | Bytel('D') | 44 | |
|---|---|---|---|
| length of message | Int32 | 00 00 00 15 | |
| # cols | Int16 | 00 02 | |
| length of column value | Int32 | 00 00 00 01 | 00 00 00 06 |
| column value | Byten | 31 (ASCII で 1) | 74 61 6e 61 6b 61 (ASCII で tanaka) |
見たままで送りたいデータを文字列化し、byte に変換すれば良いだけです。
2つ目の DataRow に関しても同様です。
このとき NULL を送りたいときは length of column value の部分が ff ff ff ff になり、column value の部分には何も入れません。
最後に CommandComplete と ReadyForQuery を送れば SELECT の内容を送ることができます。
terminate
最後が terminate です。client が切断してきたら socket をclose しましょう。
T 172.18.0.2:34058 -> 172.18.0.4:5432 [AP] #58 58 00 00 00 04 X....
まとめ
最終的にまとめると以下のようになります。
# main.py import socket import struct addr, port = "127.0.0.1", 15432 PACKET_LENGTH = 4 ReadyForQuery = b"\x5a\x00\x00\x00\x05\x49" def read_packet(client_socket): size = struct.unpack(">I", client_socket.recv(PACKET_LENGTH))[0] payload = client_socket.recv(size-PACKET_LENGTH) return payload def startup(client_socket): read_packet(client_socket) client_socket.sendall(b"N") read_packet(client_socket) client_socket.sendall(b"\x52\x00\x00\x00\x08\x00\x00\x00\x00") client_socket.sendall(ReadyForQuery) def read_regular_packet(client_socket): tag = client_socket.recv(1) payload = read_packet(client_socket) return tag, payload def send_packet(client_socket, data): data = data.encode() + b"\x00" size = struct.pack(">I", len(data)+PACKET_LENGTH) packet = b"C" + size + data client_socket.sendall(packet) client_socket.sendall(ReadyForQuery) def do_something(query): query = query.decode().lower() if 'select' in query: return 'select', [['id', 'name'], [[1, 'tanaka'], [2, 'suzuki']]] return 'other', None def send_select(client_socket, result): names = result[1][0] rows = result[1][1] # column description payload = b"" l = len(names) payload += struct.pack('>h', l) for i, name in enumerate(names): payload += name.encode() + b"\x00" payload += b"\x00\x00\x00\x00" # table oid payload += struct.pack('>h', i+1) # col id payload += b"\x00\x00\x04\x13" # oid of datatype payload += b"\xff\xff" # data type size payload += b"\x00\x00\x01\x03" # type modifier payload += b"\x00\x00" # format code desc_size = struct.pack(">I", len(payload) + PACKET_LENGTH) client_socket.sendall(b"T" + desc_size + payload) # data row for row in rows: payload = struct.pack('>h', l) for val in row: s = str(val).encode() ls = len(s) ss = struct.pack('>I', ls) payload += ss + s client_socket.sendall(b"D" + struct.pack('>I', len(payload) + PACKET_LENGTH) + payload) send_packet(client_socket, f"SELECT {len(rows)}") if __name__ == '__main__': server_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM) server_socket.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, True) server_socket.bind((addr, port)) server_socket.listen() client_socket, (client_address, client_port) = server_socket.accept() startup(client_socket) while True: tag, query = read_regular_packet(client_socket) if tag == b"\x58": break result = do_something(query) if result[0] == 'select': send_select(client_socket, result) else: send_packet(client_socket, "OK") client_socket.close() server_socket.close()
# terminal 1 $ python main.py # terminal 2 $ psql -h 127.0.0.1 -p 15432 -U postgres psql (13.2, server 0.0.0) Type "help" for help. postgres=> hoge; OK postgres=> select * from hoge; id | name ----+-------- 1 | tanaka 2 | suzuki (2 rows) postgres=> \q
