Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

neon.com

Post reply on HN
Print view View original

31–40 of 50 posts

Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

#31

Post by asveikau »

mike_hearn wrote: post #10
jtwaleson wrote: post #2

From the title I was hoping for this being hacky on the server application side, like how it aborts and clears the memory for a running query. Still an interesting read. Just wondering, why can't the TCP connection of the query not be used to send a cancellation request? Why does it have the be out of band?

Because Postgres is a very old codebase and was written in a style that assumes there are no threads, and thus there's nothing to listen for a cancellation packet whilst work is getting done. A lot of UNIXes had very poor support for threads for a long time and so this kind of multi-process architecture is common in old codebases. The TCP URG bit came out of this kind of problem. It triggers a SIGURG signal on UNIX w…

> These days you'd implement cancellation by having one thread handle inbound messages and another thread do the actual work with shared memory to implement a cooperative cancellation mechanic.

Doesn't necessarily need a thread per connection. Could be on an epoll/kqueue/io-uring.

The query would need to periodically re-check a cancellation flag, which has costs and would come with a delay if it's particularly busy.

Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

#32

Post by somat »

dmurray wrote: post #18
i18nagentai wrote: post #17

[flagged]

Doesn't it also make sense in the context of modern networking assumptions? I've never had to connect to PostGres in an adversarial environment. I've been at work or at home and I connected to PostGres instances owned by me or my employer. If I tried to connect to my work instance from a coffee shop, the first thing I'd do would be to log in to a VPN. That's your multiplexed protocol layer right there: the security h…

It makes me think of ipsec, ipsec was originally intended to be used sort of the the same as we use tls today, but application independent. when making a connection to a random remote machine the os would see if it could spool up a ipsec sa. No changes to a user program would be needed. But while they were faffing about trying to overcomplicate ipsec ssl came along and stole it's lunch money.

This application of ipsec was never used and barely implemented. Today getting it to make ad-hoc connections is a tricky untested edge case and ipsec was regulated to dedicated tunnels. Where everyone hates it because it is too tricky to get the parameters aligned.

There is definitely a case to be made that it is right and proper that secure connections are handled in the application(tls), But sometimes I like to think of how it could have been. where all applications get a secure connection whether they want one or not.

As a useless dangling side thought, an additional piece would be needed for ad-hoc ipsec that as far as I know was never implemented, a way to notify the OS that this connection must be encrypted(a socket option? SO_ENC?). This is most of the case for encrypted connections being the duty of the application.

Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

#33

Post by eqvinox »

gpderetta wrote: post #20

TLS is not async signal safe. But having a dedicated thread whose responsibility is to only send cancel tokens via a TLS connection and is woken up by a posix semaphore seems a small, self contained change that doesn't require any major refactoring.

This doesn't really have anything to do with async signal safety. You could perfectly fine capture the Ctrl+C in psql, stick a notifier byte in a pipe (or use signalfd to begin with) and handle it as a synchronous event in a main loop. You'd still need to establish a new connection purely to bypass buffered data. (or use TCP URG, but that seems generally a poor idea.)

Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

#34

Post by toast0 »

marcosdumay wrote: post #30
mike_hearn wrote: post #10

Earlier quoted context omitted.

Because Postgres is a very old codebase and was written in a style that assumes there are no threads, and thus there's nothing to listen for a cancellation packet whilst work is getting done. A lot of UNIXes had very poor support for threads for a long time and so this kind of multi-process architecture is common in old codebases. The TCP URG bit came out of this kind of problem. It triggers a SIGURG signal on UNIX w…

> how many web servers abort request processing if the connection drops? I don't think I have ever seen a published web service which error log wasn't full of broken pipe messages. So, AFAIK, all.

You only get a broken pipe when you write, which is often after you've already done most of the work.

Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

#35

Post by pas »

shilgapira wrote: post #23
tensegrist wrote: post #21

Earlier quoted context omitted.

is sed s/—/--/ the new meta

offtopic, but it's interesting how large of a discrepancy there is between the length of your comment and how much time i'd have to spend explaining background info to a non-programmer to get them to understand why this is funny

why is it funny? it seems like a sincere question

Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

#36

Post by megous »

i18nagentai wrote: post #17

[flagged]

cancel key is arbitrary sized

https://www.postgresql.org/docs/current/protocol-message-for... / BackendKeyData

I'm fairly certain that this cancellation approach has nothing to do with UNIX networking assumptions, and everything to do with the connection/process model of PostgreSQL.

Creating a connection => starting a process and passing the accepted socket to it (so in-band cancel would have to go directy to the backend executing the query) + single-threaded backend process not reading from socket when executing a query, so it would get the cancellation request only after the query finishes (or even after all pipelined queries before it finish, which is even worse).

Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

#37

Post by pas »

eqvinox wrote: post #33
gpderetta wrote: post #20

TLS is not async signal safe. But having a dedicated thread whose responsibility is to only send cancel tokens via a TLS connection and is woken up by a posix semaphore seems a small, self contained change that doesn't require any major refactoring.

This doesn't really have anything to do with async signal safety. You could perfectly fine capture the Ctrl+C in psql, stick a notifier byte in a pipe (or use signalfd to begin with) and handle it as a synchronous event in a main loop. You'd still need to establish a new connection purely to bypass buffered data. (or use TCP URG, but that seems generally a poor idea.)

I believe the suggestion is to have a TLS endpoint in the server, which demultiplexes the incoming CancelRequest and signals to the corresponding worker process via shared memory

Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

#38

Post by gpderetta »

eqvinox wrote: post #33
gpderetta wrote: post #20

TLS is not async signal safe. But having a dedicated thread whose responsibility is to only send cancel tokens via a TLS connection and is woken up by a posix semaphore seems a small, self contained change that doesn't require any major refactoring.

This doesn't really have anything to do with async signal safety. You could perfectly fine capture the Ctrl+C in psql, stick a notifier byte in a pipe (or use signalfd to begin with) and handle it as a synchronous event in a main loop. You'd still need to establish a new connection purely to bypass buffered data. (or use TCP URG, but that seems generally a poor idea.)

> This doesn't really have anything to do with async signal safety.

TLS not being async signal safe is explicitly called out on the article as the reason the token is sent in clear text.

> Handle it as a synchronous event in a main loop

Of course of you rearchitect the client there are better solutions. But again, the article mentions that's not planned for now.

By comparison, delegating cancellation to a background background thread can be done non-intrusively. In principe no code outside the cancel path need changing.

Edit: the article mentions that there is a refactor in the works to implement cancel over tls [1]. Turns out that they decided to use a thread (with a pipe for signaling).

[1] https://www.postgresql.org/message-id/flat/DEY0N7FS8NCU.1F7Q...

Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

#39

Post by kelnos »

> There are architectural reasons why psql doesn’t yet use libpq’s encrypted cancellation functions (it “would need a much larger refactor to be able to call them due to the new functions not being signal-safe”)

This surprised me. I was like, "surely socket()/connect()/send()/recv() aren't async signal safe!" But after a quick trip to `man signal-safety`, it turns out they are, which surprised me. I guess it shouldn't, perhaps: likely all of those functions are little more than wrappers around the corresponding syscalls, so there isn't any libc state to possibly corrupt or deadlock you if you use them in a signal handler. And I assume the kernel needs to keep itself in a consistent, non-deadlockable state before it calls a signal handler anyway.

(And I'm not at all surprised that whatever TLS library they're using calls things or is itself not async signal safe.)

Either way, wow! In 2026 it feels absolutely bonkers that a software dev team would continue to put out something like this. Honestly, once psql got TLS support, when you make a TLS connection it should have put up a big warning and ask you, "This program cannot cancel queries over a secure channel; do you still want to enable query cancellation?" Or hell, just disable query cancellation in those cases and not even give an option.

I guess this is "just" a DoS, though, and only in cases where someone authorized is poking around using psql while connected to a server exposed to the public internet. Hopefully that situation isn't common. And even if it is, there's no opportunity for data exfiltration or RCE, so... the author's "heebie-jeebies level 6" feels appropriate.

(And there's an easy mitigation if you know the issue: once you cancel a query with ctrl+c, quit the psql session and start a new one. That will give you the process a new "cancellation key", and the old one from the old process won't work for an attacker anymore.)

Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

#40

Post by kelnos »

tensegrist wrote: post #21
i18nagentai wrote: post #17

[flagged]

is sed s/—/--/ the new meta

I have used "--" as a lazy-man's emdash for decades at this point. Once I heard that people started assuming text that uses emdashes was written by an LLM I got worried that people were going to think that I'm an LLM, but then I realized the LLMs use the real unicode emdash character, while I just use two regular ASCII-zone hyphens. Whew.

(Also I just learned that ASCII 0x2d/unicode U+002D is more properly called a "hyphen" [well, "HYPHEN-MINUS"], not a "dash".)

Post reply on HN