postgresPostgreSQL Frontend/Backend Wire Protocol v3.0Port 5432 StartupMessage -> Query -> ReadyForQuery (app <-> pg postgres)
What It Does on the Wire
Speak fluent PostgreSQL v3.0 wire protocol ('Q', 'R', 'C', 'Z', 'E' tagged messages with big-endian length prefixes) over stateful TCP 5432, working identically over IPv4 or IPv6 flow blocks.
PostgreSQL (postgres) communicates over TCP port 5432 using the Frontend/Backend v3.0 wire protocol (0x00030000). Unlike MySQL (where the server speaks first), PostgreSQL is client-speaks-first: the client sends a length-prefixed StartupMessage (startup) containing user and database (db) NUL-separated key/value pairs, followed by 1-byte tagged messages ('R' authok, 'Q' query, 'C' commandcomplete, 'Z' readyforquery, and 'E' errorresponse).
FrameAssembly calculates every 32-bit big-endian PostgreSQL length field automatically and keeps TCP sequence numbers synchronized across the full session.
Database Activity Monitoring (DAM) & COPY TO PROGRAM IDS Rules
Test Suricata, Zeek, and DAM policies against postgres query packets carrying SELECT, ALTER ROLE, or COPY ... TO PROGRAM statements.
PgBouncer, RDS Proxy & VPC Firewall Rule Verification
Verify TCP 5432 connectivity from Kubernetes worker nodes to managed PostgreSQL subnets.
SQLSTATE Authentication & Permission Error Alerting
Emit errorresponse frames following startup or query to verify SIEM failed-login and unauthorized-table alerts.
Minimal Quickstart (postgres_quickstart.frasm)
Hit Preview Packets to compile in-browser and eyeball timestamps + wire byte lengths, or ship it straight to the Playground.
host app_pod { ip 10.50.10.12 }host pg_db { ip 10.50.20.54 }compile { app_pod -> pg_db tcp syn srcport 55432 dstport 5432 wait 1ms app_pod <- pg_db tcp syn ack wait 1ms app_pod -> pg_db tcp ack wait 1ms app_pod -> pg_db postgres startup user "app_ro" db "inventory" wait 2ms app_pod <- pg_db postgres authok wait 1ms app_pod <- pg_db postgres readyforquery wait 2ms app_pod -> pg_db postgres query sql "SELECT sku, qty FROM stock WHERE qty < 10;"}What the Compiler Calculates So You Don't Have To
- Defaults to TCP destination port
5432(psh ack) and writes Protocol Version3.0(0x00030000) insidestartupframes. - Computes the 32-bit big-endian self-inclusive length (
4 + body_bytes) on every'R','Q','C','Z', and'E'message. - Terminates
sqlstrings with a NUL byte (0x00) inside Simple Query ('Q') frames so Wireshark’spgsqldissector parses the SQL statement cleanly.
Full PostgreSQL v3.0 Startup, SQL Query Lifecycle & ErrorResponse
Completes a TCP 5432 handshake, authenticates via startup -> authok -> readyforquery, runs an audit query (commandcomplete + readyforquery), and triggers an errorresponse on a restricted table.
host api_svc { ip 172.16.54.15 }host pg_pri { ip 172.16.54.100 }compile { api_svc -> pg_pri tcp syn srcport 51432 dstport 5432 win 65535 mss 1460 wait 1ms api_svc <- pg_pri tcp syn ack win 65535 mss 1460 wait 1ms api_svc -> pg_pri tcp ack wait 1ms // 1. Client-first StartupMessage -> AuthenticationOk + ReadyForQuery ('I') api_svc -> pg_pri postgres startup user "reporting_ro" db "analytics" wait 2ms api_svc <- pg_pri postgres authok wait 1ms api_svc <- pg_pri postgres readyforquery wait 2ms // 2. Authorized SELECT query -> CommandComplete + ReadyForQuery api_svc -> pg_pri postgres query sql "SELECT count(*) FROM daily_events;" wait 3ms api_svc <- pg_pri postgres commandcomplete wait 1ms api_svc <- pg_pri postgres readyforquery wait 4ms // 3. Unauthorized write attempt -> ErrorResponse ('E') api_svc -> pg_pri postgres query sql "DROP TABLE audit_trail;" wait 2ms api_svc <- pg_pri postgres errorresponse}# 1. Eyeball relative timestamps, ports & wire lengths in your terminal
frameassembly show postgres_session_audit.frasm
# 2. Compile .frasm -> classic .pcap (tcpdump/tcpreplay/Suricata) or nanosecond .pcapng
frameassembly postgres_session_audit.frasm postgres_session_audit.pcap
frameassembly postgres_session_audit.frasm postgres_session_audit.pcapng
# 3. Round-trip .pcap <-> .frasm whenever you want to scrub or tweak an existing capture
frameassembly reverse postgres_session_audit.pcap postgres_session_audit_edited.frasmAll postgres Keywords & Header Options
Every sub-keyword and header option is lowercase [a-z0-9]+ with zero underscores — mix and match them in any order after postgres:
src_host -> dst_host postgres [startup | authok | query | commandcomplete | readyforquery | errorresponse] [user "<text>"] [db "<text>"] [sql "<text>"]| Keyword / Option | Accepted Value | Default | Effect on Packet |
|---|---|---|---|
| startup | authok | query | commandcomplete | readyforquery | errorresponse | PostgreSQL v3.0 message type | query ('Q') | Selects untagged StartupMessage (startup), 'R' AuthOK (authok), 'Q' Simple Query (query), 'C' CommandComplete, 'Z' ReadyForQuery, or 'E' ErrorResponse. |
| user "<text>" | db "<text>" | Quoted database role and database name on startup | user "postgres", db "postgres" | Writes NUL-delimited user and database parameters in startup packets. |
| sql "<text>" | Quoted SQL statement on query | "SELECT 1;" | Sets the NUL-terminated SQL query string inside 'Q' packets. |
Cross-Layer Wrappers Valid with postgres
802.1Q VLAN / QinQ / MPLS tags, IPv4/IPv6 QoS & TTL fields, wait gaps, and transport overrides allowed on this protocol
11 options
Cross-Layer Wrappers Valid with postgres
802.1Q VLAN / QinQ / MPLS tags, IPv4/IPv6 QoS & TTL fields, wait gaps, and transport overrides allowed on this protocol
| Option | Range / Format | Default | Effect on Packet |
|---|---|---|---|
| wait <number><ns|us|ms|s|m> | Time gap (ns, us, ms, s, m) | 10ms | Advances the packet timestamp clock after sending this packet and before the next packet (wait 0ns sends back-to-back). |
| payload "<text>" | hexpayload "<hex>" | Quoted text or hex bytes (choose one) | Omitted | Appends or overrides raw payload bytes ("deadbeef" or "de:ad:be:ef") and updates packet lengths and checksums. |
| srcmac <mac> | dstmac <mac> | Colon, hyphen, or Cisco dot MAC | Host MAC / RFC multicast MAC | Overrides the 14-byte Ethernet II source or destination MAC address for this single packet. |
| vlan <0..4095> [pcp <0..7>] [dei <0|1>] | VLAN ID 0..4095, PCP 0..7, DEI 0..1 | Untagged | Wraps the packet in an IEEE 802.1Q VLAN tag (0x8100) with optional 802.1p QoS priority. |
| qinq <0..4095> [innervlan <0..4095>] | Outer S-VLAN 0..4095, Inner C-VLAN 0..4095 | Untagged (innervlan 1 when qinq is set) | Double-tags the packet with outer IEEE 802.1ad S-Tag (0x88a8) plus inner IEEE 802.1Q C-Tag (0x8100). |
| mpls <0..1048575> [exp|tc <0..7>] [bos <0|1>] [mplsttl <0..255>] | Label 0..1048575, exp/tc 0..7, bos 0..1, ttl 0..255 | Unlabeled (at most 1 mpls tag per packet) | Inserts a 32-bit MPLS label header (0x8847) ahead of the Layer 3 header. |
| ttl <0..255> [dscp <0..63>] [ecn <0..3>] | TTL/Hop Limit 0..255, DSCP 0..63, ECN 0..3 | ttl 64, dscp 0, ecn 0 | Sets IPv4 TTL or IPv6 Hop Limit plus DiffServ (DSCP) and ECN QoS markings. |
| ipid <0..65535> [df] [mf] [fragoffset <0..8191>] | IPv4 ID, DF/MF flags, fragment offset 0..8191 | ipid 1, flags 0, fragoffset 0 | Sets IPv4 Identification, Do-Not-Fragment (df), More-Fragments (mf), and fragment offset (ignored over IPv6). |
| flowlabel <0..1048575> | 20-bit IPv6 Flow Label (0..1048575) | 0 | Sets the 20-bit IPv6 Flow Label for ECMP routing tests (ignored when running over IPv4 hosts). |
| srcport <0..65535> | dstport <0..65535> | Port number 0..65535 | srcport 49152 (remembered), TCP 5432 | Overrides source or destination port and remembers the port pair automatically on <- replies. |
| seq <number> | acknum <number> | win <0..65535> | syn | ack | psh | fin | rst | TCP sequence/ack numbers, window size, flags & RFC 7323 options | psh ack, win 65535, automatic TCP seq/acknum | Overrides automatic TCP sequence tracking, flags, or RFC 7323 options (mss, wscale, sackperm, tsval, tsecr). |
Gotchas & Quick Fixes
PostgreSQL Wireshark Display Filter Is pgsql (Not postgres)
Wireshark display filter: pgsqlWhile the FrameAssembly keyword is postgres, Wireshark names its dissector pgsql (pgsql.query, pgsql.user).