I have been looking into the implementation details of PostgreSQL client to database communication recently, and was amazed at how easy it is to look at the PostgreSQL traffic using wireshark. When I put my amazement on Twitter, I got asked to provide an example of how to do this.
How to install wireshark on RHEL/Centos/Alma/etc.
Installing wireshark is done in the following way:
sudo yum install wireshark-cli
This installs the CLI version of wireshark, for which the name of the executable is tshark (not wireshark-cli).
How to use wireshark for sniffing PostgreSQL traffic
The next obvious question is: okay, but how do I use it?
This too is actually very easy.
If you want to see all the network traffic, and a complete description of the PostgreSQL related data in the traffic, use:
sudo tshark -i any -f 'tcp port 5432' -d tcp.port==5432,pgsql -O pgsql
There is one important caveat here: the wireshark executable must be executed on either the client machine or the server machine. Otherwise it cannot capture the network traffic.
How does that look like?
This is how it looks like on my server when I start wireshark:
[vagrant@yb-1 ~]$ sudo tshark -i any -f 'tcp port 5433' -d tcp.port==5433,pgsql -O pgsql
Running as user "root" and group "root". This could be dangerous.
Capturing on 'any'
Please mind I capture the traffic on port 5433, which is the port that YugabyteDB uses, change for 5432 for PostgreSQL.
If I connect to the database with psql, the following is seen:
Frame 1: 80 bytes on wire (640 bits), 80 bytes captured (640 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.1, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 52441, Dst Port: 5433, Seq: 0, Len: 0
Frame 2: 76 bytes on wire (608 bits), 76 bytes captured (608 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.1
Transmission Control Protocol, Src Port: 5433, Dst Port: 52441, Seq: 0, Ack: 1, Len: 0
Frame 3: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.1, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 52441, Dst Port: 5433, Seq: 1, Ack: 1, Len: 0
Frame 4: 76 bytes on wire (608 bits), 76 bytes captured (608 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.1, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 52441, Dst Port: 5433, Seq: 1, Ack: 1, Len: 8
PostgreSQL
Type: SSL request
Length: 8
Frame 5: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.1
Transmission Control Protocol, Src Port: 5433, Dst Port: 52441, Seq: 1, Ack: 9, Len: 0
Frame 6: 69 bytes on wire (552 bits), 69 bytes captured (552 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.1
Transmission Control Protocol, Src Port: 5433, Dst Port: 52441, Seq: 1, Ack: 9, Len: 1
Frame 7: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.1, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 52441, Dst Port: 5433, Seq: 9, Ack: 2, Len: 0
Frame 8: 152 bytes on wire (1216 bits), 152 bytes captured (1216 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.1, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 52441, Dst Port: 5433, Seq: 9, Ack: 2, Len: 84
PostgreSQL
Type: Startup message
Length: 84
Parameter name: user
Parameter value: yugabyte
Parameter name: database
Parameter value: yugabyte
Parameter name: application_name
Parameter value: psql
Parameter name: client_encoding
Parameter value: UTF8
Frame 9: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.1
Transmission Control Protocol, Src Port: 5433, Dst Port: 52441, Seq: 2, Ack: 93, Len: 0
Frame 10: 404 bytes on wire (3232 bits), 404 bytes captured (3232 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.1
Transmission Control Protocol, Src Port: 5433, Dst Port: 52441, Seq: 2, Ack: 93, Len: 336
PostgreSQL
Type: Authentication request
Length: 8
Authentication type: Success (0)
PostgreSQL
Type: Parameter status
Length: 26
Parameter name: application_name
Parameter value: psql
PostgreSQL
Type: Parameter status
Length: 25
Parameter name: client_encoding
Parameter value: UTF8
PostgreSQL
Type: Parameter status
Length: 23
Parameter name: DateStyle
Parameter value: ISO, MDY
PostgreSQL
Type: Parameter status
Length: 25
Parameter name: integer_datetimes
Parameter value: on
PostgreSQL
Type: Parameter status
Length: 27
Parameter name: IntervalStyle
Parameter value: postgres
PostgreSQL
Type: Parameter status
Length: 20
Parameter name: is_superuser
Parameter value: on
PostgreSQL
Type: Parameter status
Length: 25
Parameter name: server_encoding
Parameter value: UTF8
PostgreSQL
Type: Parameter status
Length: 39
Parameter name: server_version
Parameter value: 11.2-YB-2.17.2.0-b0
PostgreSQL
Type: Parameter status
Length: 35
Parameter name: session_authorization
Parameter value: yugabyte
PostgreSQL
Type: Parameter status
Length: 35
Parameter name: standard_conforming_strings
Parameter value: on
PostgreSQL
Type: Parameter status
Length: 17
Parameter name: TimeZone
Parameter value: UTC
PostgreSQL
Type: Backend key data
Length: 12
PID: 5689
Key: 4094446470
PostgreSQL
Type: Ready for query
Length: 5
Status: Idle (73)
Frame 11: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.1, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 52441, Dst Port: 5433, Seq: 93, Ack: 338, Len: 0
Yes, that is a lot of information.
But the reason there is this much information is because these are all network packets that are transmitted as part of setting up the database connection.
This includes the first 3 packets performing the classic TCP 3-way handshake (frames 1-3).
The frames 4-7 are a negotiation for SSL; frame 4 shows the client asking for the PostgreSQL traffic to be SSL or not.
The frames 8-11 are dealing with the startup message from the database, which sets parameters and values. Frame 10 sends back a lot of individual messages in a single frame back as a response to the client startup message, such as:
- The authentication request response
- Various parameter status messages
- The ready for query message, indicating the server can receive another request.
Now for the reason most people will use it, to see the database requests: here there are multiple options, but let me show two common dialogues:
Simple query protocol
Frame 12: 87 bytes on wire (696 bits), 87 bytes captured (696 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.1, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 53026, Dst Port: 5433, Seq: 93, Ack: 338, Len: 19
PostgreSQL
Type: Simple query
Length: 18
Query: select now();
Frame 13: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.1
Transmission Control Protocol, Src Port: 5433, Dst Port: 53026, Seq: 338, Ack: 112, Len: 0
Frame 14: 157 bytes on wire (1256 bits), 157 bytes captured (1256 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.1
Transmission Control Protocol, Src Port: 5433, Dst Port: 53026, Seq: 338, Ack: 112, Len: 89
PostgreSQL
Type: Row description
Length: 28
Field count: 1
Column name: now
Table OID: 0
Column index: 0
Type OID: 1184
Column length: 8
Type modifier: -1
Format: Text (0)
PostgreSQL
Type: Data row
Length: 39
Field count: 1
Column length: 29
Data: 323032332d30342d31312030393a33333a30392e36363032...
PostgreSQL
Type: Command completion
Length: 13
Tag: SELECT 1
PostgreSQL
Type: Ready for query
Length: 5
Status: Idle (73)
Frame 15: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.1, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 53026, Dst Port: 5433, Seq: 112, Ack: 427, Len: 0
This is a simple query protocol dialogue. Contrary to popular believe, the simple query protocol is not a wire protocol. The simple query protocol is a request type for a PostgreSQL database.
Frame 12 shows the query being sent to the database backend. This is a single request, which lets the PostgreSQL database backend deal with the 4 steps of PostgreSQL execution.
Frame 14 is the database response. The database response consists of a number of messages inside the frame:
- A description of the row it is going to send.
- The data of the row.
- The indication the command has been executed.
- The indication the backend is ready for another request.
Extended query
Frame 15: 124 bytes on wire (992 bits), 124 bytes captured (992 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 50034, Dst Port: 5433, Seq: 258, Ack: 428, Len: 56
PostgreSQL
Type: Parse
Length: 20
Statement:
Query: SELECT now()
Parameters: 0
PostgreSQL
Type: Bind
Length: 12
Portal:
Statement:
Parameter formats: 0
Parameter values: 0
Result formats: 0
PostgreSQL
Type: Describe
Length: 6
Portal:
PostgreSQL
Type: Execute
Length: 9
Portal:
Returns: all rows
PostgreSQL
Type: Sync
Length: 4
Frame 16: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 5433, Dst Port: 50034, Seq: 428, Ack: 314, Len: 0
Frame 17: 167 bytes on wire (1336 bits), 167 bytes captured (1336 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 5433, Dst Port: 50034, Seq: 428, Ack: 314, Len: 99
PostgreSQL
Type: Parse completion
Length: 4
PostgreSQL
Type: Bind completion
Length: 4
PostgreSQL
Type: Row description
Length: 28
Field count: 1
Column name: now
Table OID: 0
Column index: 0
Type OID: 1184
Column length: 8
Type modifier: -1
Format: Text (0)
PostgreSQL
Type: Data row
Length: 39
Field count: 1
Column length: 29
Data: 323032332d30342d31312030393a34323a35372e37303331...
PostgreSQL
Type: Command completion
Length: 13
Tag: SELECT 1
PostgreSQL
Type: Ready for query
Length: 5
Status: Idle (73)
Frame 18: 68 bytes on wire (544 bits), 68 bytes captured (544 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 50034, Dst Port: 5433, Seq: 314, Ack: 527, Len: 0
Here we see the same query (select now()) being executed, but now using the extended query protocol.
Frame 15 is the client request. Because this is sending a request using the extended query protocol, it doesn't send a single query, but it sends a request for the different steps that query execution must make:
- Parse. The client parse step performs the server parse and rewrite steps, and introduces the SQL to the session, and performs the parsing.
- Bind. The client bind step performs the server plan step. If there are bind variables, the variables are first bound before the plan step, to allow the planner to use the variables.
- Execute. The client and server execute are the same thing. The important thing to notice here is that Parse, Bind and Execute are all within the same frame.
Frame 17 contains the backend response. As you can see, it responded to each request: it accepted the parse, and the parsing completed, it then accepted the bind request and completed that, and then it responded with the execution output, which is identical to the simple query protocol response: a row description, the data row, command completion and ready for query.
In case you wonder what happens if an invalid statement is sent: the server will simply respond with an error:
Frame 15: 170 bytes on wire (1360 bits), 170 bytes captured (1360 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 5433, Dst Port: 48402, Seq: 428, Ack: 311, Len: 102
PostgreSQL
Type: Error
Length: 101
Severity: ERROR
Text: ERROR
Code: 42703
Message: column "__" does not exist
Position: 8
File: parse_relation.c
Line: 3301
Routine: errorMissingColumn
(I changed the query from select now() to select __)
And after the error it will respond that it is ready again:
Frame 17: 74 bytes on wire (592 bits), 74 bytes captured (592 bits) on interface 0
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.66.80, Dst: 192.168.66.80
Transmission Control Protocol, Src Port: 5433, Dst Port: 48402, Seq: 530, Ack: 311, Len: 6
PostgreSQL
Type: Ready for query
Length: 5
Status: Idle (73)
Top comments (0)