PostgreSQL 协议数据样例

Startup Message

Startup Message 缺少用户名

响应数据

0000   45 00 00 00 79 53 46 41 54 41 4c 00 56 46 41 54   E...ySFATAL.VFAT
0010   41 4c 00 43 32 38 30 30 30 00 4d 6e 6f 20 50 6f   AL.C28000.Mno Po
0020   73 74 67 72 65 53 51 4c 20 75 73 65 72 20 6e 61   stgreSQL user na
0030   6d 65 20 73 70 65 63 69 66 69 65 64 20 69 6e 20   me specified in 
0040   73 74 61 72 74 75 70 20 70 61 63 6b 65 74 00 46   startup packet.F
0050   70 6f 73 74 6d 61 73 74 65 72 2e 63 00 4c 32 32   postmaster.c.L22
0060   31 37 00 52 50 72 6f 63 65 73 73 53 74 61 72 74   17.RProcessStart
0070   75 70 50 61 63 6b 65 74 00 00                     upPacket..

响应数据

PostgreSQL
    Type: Error
    Length: 121
    Severity: FATAL
    Text: FATAL
    Code: 28000
    Message: no PostgreSQL user name specified in startup packet
    File: postmaster.c
    Line: 2217
    Routine: ProcessStartupPacket

Startup Message 缺少 Database

startupPacket 删除了 database 的 key-value，没有返回错误信息，继续进行认证。

请求数据：

0000   2a ca 99 b3 a5 ce ce 19 d8 bd 2a f8 08 00 45 00   *.........*...E.
0010   00 76 2c a3 40 00 40 06 3e 12 c0 a8 03 e9 0a f0   .v,.@.@.>.......
0020   00 4c 9f ea 15 38 b2 d7 bb e5 88 d3 54 bc 80 18   .L...8......T...
0030   02 00 d0 35 00 00 01 01 08 0a 43 67 be 76 0f a3   ...5......Cg.v..
0040   ca bf 00 00 00 42 00 03 00 00 75 73 65 72 00 70   .....B....user.p
0050   6f 73 74 67 72 65 73 00 61 70 70 6c 69 63 61 74   ostgres.applicat
0060   69 6f 6e 5f 6e 61 6d 65 00 70 73 71 6c 00 63 6c   ion_name.psql.cl
0070   69 65 6e 74 5f 65 6e 63 6f 64 69 6e 67 00 55 54   ient_encoding.UT
0080   46 38 00 00                                       F8..

Startup Message 指定不存在的数据库

响应数据

0000   45 00 00 00 5d 53 46 41 54 41 4c 00 56 46 41 54   E...]SFATAL.VFAT
0010   41 4c 00 43 33 44 30 30 30 00 4d 64 61 74 61 62   AL.C3D000.Mdatab
0020   61 73 65 20 22 68 65 6c 6c 6f 5f 73 73 22 20 64   ase "hello_ss" d
0030   6f 65 73 20 6e 6f 74 20 65 78 69 73 74 00 46 70   oes not exist.Fp
0040   6f 73 74 69 6e 69 74 2e 63 00 4c 38 39 30 00 52   ostinit.c.L890.R
0050   49 6e 69 74 50 6f 73 74 67 72 65 73 00 00         InitPostgres..

响应数据

PostgreSQL
    Type: Error
    Length: 93
    Severity: FATAL
    Text: FATAL
    Code: 3D000
    Message: database "hello_ss" does not exist
    File: postinit.c
    Line: 890
    Routine: InitPostgres

用户不存在 or 密码错误

响应数据

0000   45 00 00 00 6a 53 46 41 54 41 4c 00 56 46 41 54   E...jSFATAL.VFAT
0010   41 4c 00 43 32 38 50 30 31 00 4d 70 61 73 73 77   AL.C28P01.Mpassw
0020   6f 72 64 20 61 75 74 68 65 6e 74 69 63 61 74 69   ord authenticati
0030   6f 6e 20 66 61 69 6c 65 64 20 66 6f 72 20 75 73   on failed for us
0040   65 72 20 22 6e 6f 74 5f 65 78 69 73 74 73 22 00   er "not_exists".
0050   46 61 75 74 68 2e 63 00 4c 33 33 33 00 52 61 75   Fauth.c.L333.Rau
0060   74 68 5f 66 61 69 6c 65 64 00 00                  th_failed..

响应数据

PostgreSQL
    Type: Error
    Length: 106
    Severity: FATAL
    Text: FATAL
    Code: 28P01
    Message: password authentication failed for user "not_exists"
    File: auth.c
    Line: 333
    Routine: auth_failed

需要密码的时候给了别的内容

响应数据

0000   ce 19 d8 bd 2a f8 2a ca 99 b3 a5 ce 08 00 45 00   ....*.*.......E.
0010   00 a3 b5 68 40 00 40 06 b5 1f 0a f0 00 4c c0 a8   ...h@.@......L..
0020   03 e9 15 38 e8 3e 82 53 dc 47 a5 69 67 71 80 18   ...8.>.S.G.igq..
0030   01 f6 d0 62 00 00 01 01 08 0a 0f 9b db 36 43 5f   ...b.........6C_
0040   ce c8 45 00 00 00 6e 53 46 41 54 41 4c 00 56 46   ..E...nSFATAL.VF
0050   41 54 41 4c 00 43 30 38 50 30 31 00 4d 65 78 70   ATAL.C08P01.Mexp
0060   65 63 74 65 64 20 70 61 73 73 77 6f 72 64 20 72   ected password r
0070   65 73 70 6f 6e 73 65 2c 20 67 6f 74 20 6d 65 73   esponse, got mes
0080   73 61 67 65 20 74 79 70 65 20 38 30 00 46 61 75   sage type 80.Fau
0090   74 68 2e 63 00 4c 36 39 32 00 52 72 65 63 76 5f   th.c.L692.Rrecv_
00a0   70 61 73 73 77 6f 72 64 5f 70 61 63 6b 65 74 00   password_packet.
00b0   00                                                .

响应数据

PostgreSQL
    Type: Error
    Length: 110
    Severity: FATAL
    Text: FATAL
    Code: 08P01
    Message: expected password response, got message type 80
    File: auth.c
    Line: 692
    Routine: recv_password_packet

查询请求

0000   f2 d7 e6 d2 f4 b4 fe 68 13 c8 7c b1 08 00 45 00   .......h..|...E.
0010   00 d4 0d f2 40 00 40 06 5c 17 c0 a8 03 e9 0a f0   ....@.@.\.......
0020   00 9a a5 ea 15 38 81 e2 13 d4 bc 83 a8 75 80 18   .....8.......u..
0030   01 00 d0 e1 00 00 01 01 08 0a db 59 1c 76 db 69   ...........Y.v.i
0040   c7 f0 50 00 00 00 5a 53 5f 31 00 73 65 6c 65 63   ..P...ZS_1.selec
0050   74 20 2a 20 66 72 6f 6d 20 6a 6f 62 5f 65 78 65   t * from job_exe
0060   63 75 74 69 6f 6e 5f 6c 6f 67 20 77 68 65 72 65   cution_log where
0070   20 6a 6f 62 5f 6e 61 6d 65 20 3d 20 24 31 20 61    job_name = $1 a
0080   6e 64 20 69 70 20 3d 20 24 32 20 6c 69 6d 69 74   nd ip = $2 limit
0090   20 31 00 00 02 00 00 04 13 00 00 04 13 42 00 00    1...........B..
00a0   00 2e 00 53 5f 31 00 00 02 00 00 00 00 00 02 00   ...S_1..........
00b0   00 00 06 73 69 6d 70 6c 65 00 00 00 0d 31 39 32   ...simple....192
00c0   2e 31 36 38 2e 33 2e 32 33 33 00 00 44 00 00 00   .168.3.233..D...
00d0   06 50 00 45 00 00 00 09 00 00 00 00 00 53 00 00   .P.E.........S..
00e0   00 04                                             ..

Frame 23: 226 bytes on wire (1808 bits), 226 bytes captured (1808 bits) on interface vethf15970ba, id 0
Ethernet II, Src: fe:68:13:c8:7c:b1 (fe:68:13:c8:7c:b1), Dst: f2:d7:e6:d2:f4:b4 (f2:d7:e6:d2:f4:b4)
Internet Protocol Version 4, Src: 192.168.3.233, Dst: 10.240.0.154
Transmission Control Protocol, Src Port: 42474, Dst Port: 5432, Seq: 319, Ack: 463, Len: 160
PostgreSQL
    Type: Parse
    Length: 90
    Statement: S_1
    Query: select * from job_execution_log where job_name = $1 and ip = $2 limit 1
    Parameters: 2
PostgreSQL
    Type: Bind
    Length: 46
    Portal: 
    Statement: S_1
    Parameter formats: 2
    Parameter values: 2
    Result formats: 0
PostgreSQL
    Type: Describe
    Length: 6
    Portal: 
PostgreSQL
    Type: Execute
    Length: 9
    Portal: 
    Returns: all rows
PostgreSQL
    Type: Sync
    Length: 4

Prepared Statement

Close and try getting closed one

Request

PostgreSQL
    Type: Close
    Length: 9
    Statement: S_1
PostgreSQL
    Type: Bind
    Length: 15
    Portal: 
    Statement: S_1
    Parameter formats: 0
    Parameter values: 0
    Result formats: 0
PostgreSQL
    Type: Execute
    Length: 9
    Portal: 
    Returns: all rows
PostgreSQL
    Type: Bind
    Length: 57
    Portal: 
    Statement: S_2
    Parameter formats: 3
    Parameter values: 3
    Result formats: 0
PostgreSQL
    Type: Execute
    Length: 9
    Portal: 
    Returns: 1 rows
PostgreSQL
    Type: Sync
    Length: 4

0000   0a 5e 0e eb df 75 9e 93 c0 0e 3c b6 08 00 45 00   .^...u....<...E.
0010   00 a1 84 26 40 00 40 06 e5 b1 c0 a8 03 e9 0a f0   ...&@.@.........
0020   00 fe 97 38 15 38 b6 8a 4d af 37 c3 e7 0a 80 18   ...8.8..M.7.....
0030   01 00 d1 12 00 00 01 01 08 0a 9a 0c 90 5f 1f 49   ............._.I
0040   84 5a 43 00 00 00 09 53 53 5f 31 00 42 00 00 00   .ZC....SS_1.B...
0050   0f 00 53 5f 31 00 00 00 00 00 00 00 45 00 00 00   ..S_1.......E...
0060   09 00 00 00 00 00 42 00 00 00 39 00 53 5f 32 00   ......B...9.S_2.
0070   00 03 00 01 00 01 00 01 00 03 00 00 00 08 25 f2   ..............%.
0080   f7 dc 0a 5f 28 93 00 00 00 08 00 00 00 00 00 00   ..._(...........
0090   27 10 00 00 00 08 00 00 00 00 00 00 27 10 00 00   '...........'...
00a0   45 00 00 00 09 00 00 00 00 01 53 00 00 00 04      E.........S....

Response

PostgreSQL
    Type: Close completion
    Length: 4
PostgreSQL
    Type: Error
    Length: 107
    Severity: ERROR
    Text: ERROR
    Code: 26000
    Message: prepared statement "S_1" does not exist
    File: prepare.c
    Line: 505
    Routine: FetchPreparedStatement

0000   9e 93 c0 0e 3c b6 0a 5e 0e eb df 75 08 00 45 00   ....<..^...u..E.
0010   00 a5 3b 08 40 00 40 06 2e cc 0a f0 00 fe c0 a8   ..;.@.@.........
0020   03 e9 15 38 97 38 37 c3 e7 0a b6 8a 4e 1c 80 18   ...8.87.....N...
0030   00 fc d1 16 00 00 01 01 08 0a 1f 49 97 e4 9a 0c   ...........I....
0040   90 5f 33 00 00 00 04 45 00 00 00 6b 53 45 52 52   ._3....E...kSERR
0050   4f 52 00 56 45 52 52 4f 52 00 43 32 36 30 30 30   OR.VERROR.C26000
0060   00 4d 70 72 65 70 61 72 65 64 20 73 74 61 74 65   .Mprepared state
0070   6d 65 6e 74 20 22 53 5f 31 22 20 64 6f 65 73 20   ment "S_1" does 
0080   6e 6f 74 20 65 78 69 73 74 00 46 70 72 65 70 61   not exist.Fprepa
0090   72 65 2e 63 00 4c 35 30 35 00 52 46 65 74 63 68   re.c.L505.RFetch
00a0   50 72 65 70 61 72 65 64 53 74 61 74 65 6d 65 6e   PreparedStatemen
00b0   74 00 00                                          t..

Close non existed statement

Request

PostgreSQL
    Type: Close
    Length: 16
    Statement: non_exists
PostgreSQL
    Type: Close
    Length: 9
    Statement: S_1
PostgreSQL
    Type: Bind
    Length: 15
    Portal: 
    Statement: S_1
    Parameter formats: 0
    Parameter values: 0
    Result formats: 0
PostgreSQL
    Type: Execute
    Length: 9
    Portal: 
    Returns: all rows
PostgreSQL
    Type: Bind
    Length: 57
    Portal: 
    Statement: S_2
    Parameter formats: 3
    Parameter values: 3
    Result formats: 0
PostgreSQL
    Type: Execute
    Length: 9
    Portal: 
    Returns: 1 rows
PostgreSQL
    Type: Sync
    Length: 4

0000   0a 5e 0e eb df 75 9e 93 c0 0e 3c b6 08 00 45 00   .^...u....<...E.
0010   00 b2 70 48 40 00 40 06 f9 7e c0 a8 03 e9 0a f0   ..pH@.@..~......
0020   00 fe a5 1c 15 38 de 09 0e b4 dc 6e 0b 15 80 18   .....8.....n....
0030   01 00 d1 23 00 00 01 01 08 0a 9a 41 63 d2 1f 7e   ...#.......Ac..~
0040   6b 55 43 00 00 00 10 53 6e 6f 6e 5f 65 78 69 73   kUC....Snon_exis
0050   74 73 00 43 00 00 00 09 53 53 5f 31 00 42 00 00   ts.C....SS_1.B..
0060   00 0f 00 53 5f 31 00 00 00 00 00 00 00 45 00 00   ...S_1.......E..
0070   00 09 00 00 00 00 00 42 00 00 00 39 00 53 5f 32   .......B...9.S_2
0080   00 00 03 00 01 00 01 00 01 00 03 00 00 00 08 7d   ...............}
0090   ec cb 58 6a 47 0e da 00 00 00 08 00 00 00 00 00   ..XjG...........
00a0   00 27 10 00 00 00 08 00 00 00 00 00 00 27 10 00   .'...........'..
00b0   00 45 00 00 00 09 00 00 00 00 01 53 00 00 00 04   .E.........S....

Response

PostgreSQL
    Type: Close completion
    Length: 4
PostgreSQL
    Type: Close completion
    Length: 4
PostgreSQL
    Type: Error
    Length: 107
    Severity: ERROR
    Text: ERROR
    Code: 26000
    Message: prepared statement "S_1" does not exist
    File: prepare.c
    Line: 505
    Routine: FetchPreparedStatement

0000   9e 93 c0 0e 3c b6 0a 5e 0e eb df 75 08 00 45 00   ....<..^...u..E.
0010   00 aa bb 41 40 00 40 06 ae 8d 0a f0 00 fe c0 a8   ...A@.@.........
0020   03 e9 15 38 a5 1c dc 6e 0b 15 de 09 0f 32 80 18   ...8...n.....2..
0030   00 fc d1 1b 00 00 01 01 08 0a 1f 7e 6b 56 9a 41   ...........~kV.A
0040   63 d2 33 00 00 00 04 33 00 00 00 04 45 00 00 00   c.3....3....E...
0050   6b 53 45 52 52 4f 52 00 56 45 52 52 4f 52 00 43   kSERROR.VERROR.C
0060   32 36 30 30 30 00 4d 70 72 65 70 61 72 65 64 20   26000.Mprepared 
0070   73 74 61 74 65 6d 65 6e 74 20 22 53 5f 31 22 20   statement "S_1" 
0080   64 6f 65 73 20 6e 6f 74 20 65 78 69 73 74 00 46   does not exist.F
0090   70 72 65 70 61 72 65 2e 63 00 4c 35 30 35 00 52   prepare.c.L505.R
00a0   46 65 74 63 68 50 72 65 70 61 72 65 64 53 74 61   FetchPreparedSta
00b0   74 65 6d 65 6e 74 00 00                           tement..