How to sniff PostgreSQL traffic

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)