The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
2 days ago
- #Database
- #Postgres
- #Security
- Postgres queries can be cancelled for various reasons, such as long execution time, missing indexes, or SQL mistakes.
- Cancelling a query in psql sends a CancelRequest over a new connection, identified by a process ID and a secret key.
- CancelRequests are sent unencrypted by default, posing a security risk, including potential Denial of Service attacks.
- Postgres 17 introduced support for encrypted CancelRequests, but psql still uses unencrypted messages due to architectural constraints.
- Protocol v3.2 allows for longer secret keys to prevent brute-force attacks, but requires explicit configuration.
- Unencrypted CancelRequests can be replayed by attackers to cancel future queries on the same connection.
- Elephantshark, a Postgres traffic monitor, initially struggled with concurrent connections and unencrypted CancelRequests.
- The solution involves mapping process IDs and secret keys to destination hostnames to handle CancelRequests correctly.
- Recommendations include using Postgres 18 with min_protocol_version=3.2, VPNs, and avoiding Ctrl-C in psql for security.
- Future updates may bring encrypted CancelRequests to psql, improving security.