How to match on StartTLS for proxying Postgres?

[coolaj86]

Update

psql will send 00 00 00 08 04 d2 16 2f (SSLRequest) and wait for either

• N (no - plaintext)
• Y (yes - version 2) (deprecated)
• or S (ssl - version 3)

If S is sent, it will immediately begin a standard TLS connection.

After TLS is terminated, of if N (plaintext) is sent, it will send 00 00 xx xx 00 03 00 00 - where xx are Little-Endian length bytes (up to 4k at least) and 03 means "pg version 3", followed by the plaintext db connection information (username, dbname, application name, character encoding).

If sslmode=disable in the client, it will skip the SSLRequest handshake and go straight to plain text, as if N had been sent, or TLS had been terminated.

Original

The first packet from psql is 00 00 00 08 04 d2 16 2f, which I believe is related to StartTLS.

• openssl s_client -starttls postgres
• https://www.postgresql.org/docs/current/protocol-message-formats.html
• https://www.tzeejay.com/blog/2022/06/golang-postgresql-check-certificates/

Either way, it doesn't send any SNI or ALPN information until later on in the handshake.

Is there a way that I could route based on the first 8 bytes?

I almost got around this by using sclient for TLS and piping the psql connection through with sslmode=disable, but I think my HTTP matcher is causing it to hang waiting for a \r\n that never comes.

[mohammed90]

Matching of PostgreSQL connections was recently merged in #186. Anything beyond matching and simple proxying/teeing will require custom handlers or matchers to be developed. My previous research indicates each database has its own packet structure, so each requires its own matching logic:

https://caddy.community/t/need-some-help-with-enabling-lets-encrypt-certificate-on-layer-4-module/18628/11

I can't come up with a UX-friendly way to match arbitrary bytes

[coolaj86]

@mohammed90 #186 is not actually detecting Postgres. It's detecting the StartTLS byte (SMTP, SHTTP) it is detecting SSLRequest

I think the "Version 3" byte refers to SSLv3, not pgsql v3. Nope, it's SSLRequest pg v3.

The 2f byte refers to SSL/TLS whereas a 30 in that position would refer to GSSAPI (competing standard at the time which postgres also adopted). I'm not sure with all the specifics yet, but it may be a bitmask that signals either protocol.

See: traefik/traefik#9929

I can't come up with a UX-friendly way to match arbitrary bytes

How about:

{
  "match": [
    {
      "bytes": {
        "values": [
          {
            "offset": 0,
            "value": "0x0000000804d2162f"
          },
          {
            "offset": 0,
            "value": "0x0000000804d21630"
          }
        ]
      }
    }
  ]
}

The way that HAProxy does it is similar to that.

[coolaj86]

I believe what's supposed to happen is that Caddy should send a "hello" / "accepted" style packet back, then the client will send SNI + ALPN like a normal TLS connection.

Then caddy would reconstruct the StartTLS byte to the correct server, eat the response, and forward the true TLS connection on from that point.

I'm digging in a little bit to try to figure out more...

[coolaj86]

Looks like I was wrong. The packet is a postgres-specific SSLRequest: https://svn.nmap.org/nmap/nselib/sslcert.lua

  postgres_prepare_tls_without_reconnect = function(host, port)
    -- http://www.postgresql.org/docs/devel/static/protocol-message-formats.html
    -- 80877103 is "SSLRequest" in v2 and v3 of Postgres protocol
    local s, resp = comm.opencon(host, port, string.pack(">I4I4", 8, 80877103))
    if not s then
      return false, ("Failed to connect to Postgres server: %s"):format(resp)
    end
    -- v2 has "Y", v3 has "S"
    if string.match(resp, "^[SY]") then
      starttls_supported(host, port, true)
      return true, s
    elseif string.match(resp, "^N") then
      starttls_supported(host, port, false)
      return false, "Postgres server does not support SSL"
    end
    return false, "Unknown response from Postgres server"
  end,

So Caddy would have to respond with the byte S, after which the SNI information should come.

Is there currently a way to modify that matcher to send the S back so that it can get the rest of the information to match on?

[coolaj86]

Verified:

Terminal 1

printf 'S' | nc -l localhost 54321 | hexyl

Terminal 2

psql 'postgres://postgres:postgres@localhost:54321/postgres?sslmode=require'

Result (Terminal 1)

┌────────┬─────────────────────────┬─────────────────────────┬────────┬────────┐
│00000000│ 00 00 00 08 04 d2 16 2f ┊ 16 03 01 01 38 01 00 01 │⋄⋄⋄••×•/┊••••8•⋄•│
│00000010│ 34 03 03 91 93 fa f9 ce ┊ 9d 1c 06 d6 03 cf 6d be │4••×××××┊ו•×•×m×│
│00000020│ 4e 72 e3 ea 30 d4 14 3d ┊ 6c 7b 3e 65 2a ea 63 cf │Nr××0ו=┊l{>e*×c×│
│00000030│ cc 71 bd 20 2e c2 b3 00 ┊ 6f 19 cc b5 ee 59 5a 1f │×q× .××⋄┊o•×××YZ•│
│00000040│ e3 81 33 66 0a e4 f8 cd ┊ 80 b0 b5 8e 7e 77 d2 3c │××3f_×××┊××××~w×<│
│00000050│ 48 48 2c 9e 00 3e 13 02 ┊ 13 03 13 01 c0 2c c0 30 │HH,×⋄>••┊••••×,×0│
│00000060│ 00 9f cc a9 cc a8 cc aa ┊ c0 2b c0 2f 00 9e c0 24 │⋄×××××××┊×+×/⋄××$│
│00000070│ c0 28 00 6b c0 23 c0 27 ┊ 00 67 c0 0a c0 14 00 39 │×(⋄k×#×'┊⋄g×_ו⋄9│
│00000080│ c0 09 c0 13 00 33 00 9d ┊ 00 9c 00 3d 00 3c 00 35 │×_ו⋄3⋄×┊⋄×⋄=⋄<⋄5│
│00000090│ 00 2f 00 ff 01 00 00 ad ┊ 00 00 00 0e 00 0c 00 00 │⋄/⋄ו⋄⋄×┊⋄⋄⋄•⋄_⋄⋄│
│000000a0│ 09 6c 6f 63 61 6c 68 6f ┊ 73 74 00 0b 00 04 03 00 │_localho┊st⋄•⋄••⋄│
│000000b0│ 01 02 00 0a 00 16 00 14 ┊ 00 1d 00 17 00 1e 00 19 │••⋄_⋄•⋄•┊⋄•⋄•⋄•⋄•│
│000000c0│ 00 18 01 00 01 01 01 02 ┊ 01 03 01 04 00 23 00 00 │⋄••⋄••••┊••••⋄#⋄⋄│
│000000d0│ 00 16 00 00 00 17 00 00 ┊ 00 0d 00 30 00 2e 04 03 │⋄•⋄⋄⋄•⋄⋄┊⋄_⋄0⋄.••│
│000000e0│ 05 03 06 03 08 07 08 08 ┊ 08 1a 08 1b 08 1c 08 09 │••••••••┊•••••••_│
│000000f0│ 08 0a 08 0b 08 04 08 05 ┊ 08 06 04 01 05 01 06 01 │•_••••••┊••••••••│
│00000100│ 03 03 03 01 03 02 04 02 ┊ 05 02 06 02 00 2b 00 05 │••••••••┊••••⋄+⋄•│
│00000110│ 04 03 04 03 03 00 2d 00 ┊ 02 01 01 00 33 00 26 00 │•••••⋄-⋄┊•••⋄3⋄&⋄│
│00000120│ 24 00 1d 00 20 92 9c 91 ┊ b3 48 c7 a8 fa 1e 1e d9 │$⋄•⋄ ×××┊×H××ו•×│
│00000130│ 8e ff 94 51 5f 2b 93 5c ┊ 2b 0d 9c 7d 46 e2 08 3b │×××Q_+×\┊+_×}Fו;│
│00000140│ ba ba 78 d9 73          ┊                         │××x×s   ┊        │
└────────┴─────────────────────────┴─────────────────────────┴────────┴────────┘

Comparing that to the output of curl https://localhost:54321/hello:

┌────────┬─────────────────────────┬─────────────────────────┬────────┬────────┐
│00000000│ 16 03 01 01 3a 01 00 01 ┊ 36 03 03 6f c4 fa 4d 5c │••••:•⋄•┊6••o××M\│
│00000010│ 1c 91 f0 f1 85 98 b2 26 ┊ bc 27 95 01 7b 8d ef 0e │•××××××&┊×'ו{×ו│
│00000020│ 8b 33 92 1e 89 98 55 4c ┊ 4b 2e e0 20 db b7 1a 80 │×3ו××UL┊K.× ×ו×│
│00000030│ 0b 73 ea 87 38 17 f5 78 ┊ 01 9f 80 41 e9 1b 15 b1 │•s××8•×x┊•××Aו•×│
│00000040│ e0 a4 c5 a6 81 7e 4f 24 ┊ 97 9d 2e 78 00 62 13 03 │×××××~O$┊××.x⋄b••│
│00000050│ 13 02 13 01 cc a9 cc a8 ┊ cc aa c0 30 c0 2c c0 28 │••••××××┊×××0×,×(│
│00000060│ c0 24 c0 14 c0 0a 00 9f ┊ 00 6b 00 39 ff 85 00 c4 │×$ו×_⋄×┊⋄k⋄9××⋄×│
│00000070│ 00 88 00 81 00 9d 00 3d ┊ 00 35 00 c0 00 84 c0 2f │⋄×⋄×⋄×⋄=┊⋄5⋄×⋄××/│
│00000080│ c0 2b c0 27 c0 23 c0 13 ┊ c0 09 00 9e 00 67 00 33 │×+×'×#ו┊×_⋄×⋄g⋄3│
│00000090│ 00 be 00 45 00 9c 00 3c ┊ 00 2f 00 ba 00 41 c0 11 │⋄×⋄E⋄×⋄<┊⋄/⋄×⋄Aו│
│000000a0│ c0 07 00 05 00 04 c0 12 ┊ c0 08 00 16 00 0a 00 ff │ו⋄•⋄•×•┊ו⋄•⋄_⋄×│
│000000b0│ 01 00 00 8b 00 2b 00 09 ┊ 08 03 04 03 03 03 02 03 │•⋄⋄×⋄+⋄_┊••••••••│
│000000c0│ 01 00 33 00 26 00 24 00 ┊ 1d 00 20 3e ad 97 0e a7 │•⋄3⋄&⋄$⋄┊•⋄ >×ו×│
│000000d0│ a5 8a 6f b6 99 66 5c 1f ┊ b2 d7 64 42 25 1e b1 ee │××o××f\•┊××dB%•××│
│000000e0│ 65 2b 46 ba c3 47 1c e1 ┊ 65 fc 4b 00 00 00 0e 00 │e+F××G•×┊e×K⋄⋄⋄•⋄│
│000000f0│ 0c 00 00 09 6c 6f 63 61 ┊ 6c 68 6f 73 74 00 0b 00 │_⋄⋄_loca┊lhost⋄•⋄│
│00000100│ 02 01 00 00 0a 00 0a 00 ┊ 08 00 1d 00 17 00 18 00 │••⋄⋄_⋄_⋄┊•⋄•⋄•⋄•⋄│
│00000110│ 19 00 0d 00 18 00 16 08 ┊ 06 06 01 06 03 08 05 05 │•⋄_⋄•⋄••┊••••••••│
│00000120│ 01 05 03 08 04 04 01 04 ┊ 03 02 01 02 03 00 10 00 │••••••••┊•••••⋄•⋄│
│00000130│ 0e 00 0c 02 68 32 08 68 ┊ 74 74 70 2f 31 2e 31    │•⋄_•h2•h┊ttp/1.1 │
└────────┴─────────────────────────┴─────────────────────────┴────────┴────────┘

That very much appears that as soon as the character S is sent, the standard TLS connection resumes. The next 16 bytes are almost identical in both cases, aside from perhaps some size or version bytes being slightly different, and you can see the SNI localhost in both messages.

[metafeather]

Thanks for digging into the SSL side of Postgres - it's certainly more complex and so I started by addressing the non-SSL side of things, for which I have #188 in progess, however it looks like there may be some hope from your comments.

What is currently unclear to me is how Caddy L4 can match across multiple requests, I see some use of cx.Context and caddy.Context which is probably the way to go.

For other details on the Postgres protocol I have found a great resource in https://github.com/rueian/pgbroker

[mholt]

@metafeather Awesome, thanks. We'll probably be making some improvements to the matching algorithm here soon: #192 -- just FYI. I don't know, maybe it won't affect your plugin (much).

[coolaj86]

FYI: I've submitted a request to Postgres to add --tls and --alpn options of some sort to the client - in which case the plaintext matching that caddy is already equipped to handle would be sufficient:

• https://www.postgresql.org/message-id/ECNyobMWPeoCd4yj_5J0RsDL1yKC9MbbBwOGCYHgcts7v0BW_-znGIoxcvfzUsf3yKvUB6Lef22OBMZnJyZ-0T2U1qaVflQqEGO0RFHp1PE%3D%40proton.me
• https://www.postgresql.org/message-id/y3hCpl3ALJQPlIn8aKG19aiYbNM_HbchVTOqlwm2Y9OE-sWmtre-Cljlt9Jd_yYsv5S3mDNG-T5OXXfU8GgDrdu2MjTBEcWl23_NUesj8i8%3D%40proton.me

I probably didn't submit it to the right place, but perhaps it will get the conversation started.

[coolaj86]

Speak of the Zeitgeist!

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d39a49c1e459804831302807c724fa6512e90cf0

This discussed a little over a year ago, and the commits are beginning to land.

[coolaj86]

Heyo! Postgres 17 has landed with standard TLS!!
(i.e. upgrade just psql and you shouldn't have to do any StartTLS shenanigans anymore)

Now, I know many were looking forward for waiting the next 6 years for Debian and Ubuntu to include it but, for shame, I put together a script for so that anyone can compile it with fairly relocatable options (i.e. should work on Ubuntu 22 and 24, Debian Buster and Bookworm, etc, Alpine 3.18, 3.19, maybe even 3.20):

See pinned issues at: https://github.com/bnnanet/postgresql-releases/issues

And I made the binaries I've built with that process available on the same repo: https://github.com/bnnanet/postgresql-releases/releases/tag/REL_17_0

Due to a wontfix with the postgres make system I had to set a few manual linker settings so, if there's any issue, just open an issue in that repo with OS version and such and I'll look into it. gnu => Ubuntu / Debian, musl => Alpine / Docker

[mholt]

Now, I know many were looking forward for waiting the next 6 years for Debian and Ubuntu to include it but, for shame, I put together a script for so that anyone can compile it with fairly relocatable options

I died. 😆 Thanks AJ!