Protocol 72 of 81Layer 7FrameAssembly v3.1.2
llms-full.txt
postgresPostgreSQL Frontend/Backend Wire Protocol v3.0

Port 5432 StartupMessage -> Query -> ReadyForQuery (app <-> pg postgres)

Default Port / Protocol
TCP 5432
Host Endpoint Rule
Dual-Stack IPv4/IPv6
Wireshark Display Filter
pgsql
Standards & Specs
PostgreSQL Protocol v3.0
Wire Stack:Ethernet II[802.1Q / QinQ / MPLS]IPv4 / IPv6TCP 5432PostgreSQL

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.

postgres_quickstart.frasm12 lines
Open in 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 Version 3.0 (0x00030000) inside startup frames.
  • Computes the 32-bit big-endian self-inclusive length (4 + body_bytes) on every 'R', 'Q', 'C', 'Z', and 'E' message.
  • Terminates sql strings with a NUL byte (0x00) inside Simple Query ('Q') frames so Wireshark’s pgsql dissector parses the SQL statement cleanly.
Multi-Frame Lab · Ready to Compile

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.

postgres_session_audit.frasm19 lines
Open in Playground
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
}
Terminal Commands (FrameAssembly CLI v3.1.2)
# 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.frasm

All 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 / OptionAccepted ValueDefaultEffect on Packet
startup | authok | query | commandcomplete | readyforquery | errorresponsePostgreSQL v3.0 message typequery ('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 startupuser "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
OptionRange / FormatDefaultEffect on Packet
wait <number><ns|us|ms|s|m>Time gap (ns, us, ms, s, m)10msAdvances 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)OmittedAppends 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 MACHost MAC / RFC multicast MACOverrides 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..1UntaggedWraps 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..4095Untagged (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..255Unlabeled (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..3ttl 64, dscp 0, ecn 0Sets 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..8191ipid 1, flags 0, fragoffset 0Sets 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)0Sets 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..65535srcport 49152 (remembered), TCP 5432Overrides source or destination port and remembers the port pair automatically on <- replies.
seq <number> | acknum <number> | win <0..65535> | syn | ack | psh | fin | rstTCP sequence/ack numbers, window size, flags & RFC 7323 optionspsh ack, win 65535, automatic TCP seq/acknumOverrides 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)

Valid v3.1.2 Syntax
Wireshark display filter: pgsql

While the FrameAssembly keyword is postgres, Wireshark names its dissector pgsql (pgsql.query, pgsql.user).

Field Guide 03
Poke Stateful Firewalls, Suricata, Zeek & OT Conduits