最近、Go の練習がてら書いていた自作 DBMS に PostgreSQL client で接続できるようになったので、そのやり方を残しておきます。(これから紹介するサンプルコードはすべて Python ですが)
github.com
psql --version
psql (PostgreSQL) 13.2
pgcon の資料と PostgreSQL の公式 Document、加えて PostgreSQL server と client 間に流れるパケットを眺めると、自作DBMSは client から接続されたときにどういうパケットを返せばいいのかが見えてきます。
下記のパケットは 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 と一致します。
import struct
print(struct.pack('>hh', 1234, 5679).hex())
server は SSL に対応していない場合 N
を返します。
参考
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 00
5a 00 00 00 05 49
を client に送る
とすると、client からクエリを受け取る準備ができます。
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....
まとめ
最終的にまとめると以下のようになります。
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]
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"
payload += struct.pack('>h', i+1)
payload += b"\x00\x00\x04\x13"
payload += b"\xff\xff"
payload += b"\x00\x00\x01\x03"
payload += b"\x00\x00"
desc_size = struct.pack(">I", len(payload) + PACKET_LENGTH)
client_socket.sendall(b"T" + desc_size + payload)
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
自作DBMSと連携させるときは do_something
の部分をよしなに DBMS とつないでください。
PostgreSQL: Documentation: 13: 52.7. Message Formats