Inspecting uncommitted PostgreSQL transaction state, externally

Let’s try to inspect uncommitted DB state, “as external as we can”. What do I mean by that? These kind of things, from StackOverflow: How to spy on a transaction that is not yet committed in Postgres for debugging? and Is it possible enter a running transaction using psql (the cli tool)?.

So it comes up. But the answers don’t satisfy me. To have a go at this I am thinking about “just” hijacking the connection (somehow) and using it from another process (say, an interactive SQL shell). Yet before trying that out, I’m curious about what ChatGPT can come up with.

Ask an LLM, after the fashion

Ensues the prompt, not leading it on too much:

On a Linux system, I have an application A that initiates a transaction in a PostgreSQL database. It creates some state inside that transaction. I want to work with that uncommitted state from some other application, B, on the same Linux system as where A is running. I can change the source code of both A and B. Is this possible?

TLDR: Unsatisfactory answers.

• Option 1 is just proxying, letting A execute SQL on B’s behalf. Nota bene, it boldly claims:

  This is the only way for B to directly operate on the same transactional view of the database.

  ❗This in particular, I question.

• Option 2 is near indistinguishable from option 1.

• Option 3 disqualifies by ChatGPT’s own admittance.
• Option 4 is weak, it’s not what I’m asking for (question: «I want to see uncommitted DB transaction data», answer: «Don’t have a database»?)
• The last “Less common possibility” - Interesting, but I want to run arbitrary ad-hoc SQL against the uncommitted state. There may be something there though — a PostgreSQL extension which opens up a manhole? That sounds handy!

So my idea of hijacking the connection… is it a daft idea, if the “PHD-level intelligence”¹ doesn’t come up with it? Let’s find out! How would/could it work?

1. In Postgres, AFAIK pretty much everything is serverside state.
2. Thus so are transactions.
3. And those are per-connection state.²
4. Ergo, if we hijack the connection, we’re “in” the transaction, if any is ongoing, and can thus see uncommitted state.
5. On Unix-like systems, in operations on a connection, the connection is referenced by its file descriptor.
6. Perhaps I can teleport program A’s connection’s file descriptor into process B?
   • On Linux (and other Unix-like) systems, yes, indeed one can Give the Gift of File Descriptors to another process 🫶. That is, we can “send” a file descriptor (of a file, of a Unix socket connection, of a TCP connection, …) over a Unix socket to another process. The kernel, while handling the send/receive system calls of process A and B, knows what’s up since it’s in charge of both processes’ file descriptor tables, and can just make it happen.
7. But then, how can I make a connection using that bare file descriptor? Usually when I call some connect() function of some database driver, I need to supply a socket to connect to, I can’t specify an existing connection.
   • Maybe I can just instantiate a dummy connection, and then alter the processes’ memory to use a different file descriptor number? So flipping things around in userspace?
   • Or instead I might just make the dummy connection’s file descriptor point to the one received over the socket? Flipping things around in kernelspace? Yesss.

All of these pretty basic things are well represented in the training set of an LLM such as ChatGPT, yet it doesn’t seem to be able to connect the dots here. Or… perhaps my hunch is all wrong and it just wouldn’t work? Bam, spoiler: It works. What follows is an annotated proof of concept, using CPython 3.13 on Linux 7.1.5 with the psycopg-3.3.4 database driver, against PostgreSQL 18.4.

The POC

A const.py, to adjust to taste:

from pathlib import Path

# Some existing database, solely to instantiate psycopg with.
# This particular one is for a local database "dummy" connected to over PostgreSQL's Unix socket.
# See https://www.postgresql.org/docs/18/libpq-connect.html#LIBPQ-CONNSTRING-URIS for the URI specification.
DUMMY_DB_URL = 'postgresql:///dummy'

# Process B is going to be listening here. Process A will wait for the socket to appear, then connects to it.
SOCKETPATH = Path('/tmp/funnybusiness.sock')

# The database we'll actually inspect some uncommitted state of.
DB_URL = 'postgresql://myuser:mypassword@127.0.0.1/somedatabase?sslmode=disable'

Process A, the inspectee:

#!/usr/bin/env python3
"""
This is "process A". As in, this is the process that starts a transaction and creates
some uncommitted state, which we want "process B" to work with.
"""
import socket
from time import sleep
from psycopg import connect

import const

dbconn = connect(const.DB_URL)
dbconn.execute("BEGIN TRANSACTION;")
dbconn.execute("CREATE TABLE fun (id int, bla text);")
dbconn.execute("INSERT INTO fun VALUES (1, 'Would you look at all this uncommitted state!');")
print('Uncommitted state:\t', *dbconn.execute('SELECT * FROM fun;'))

client_socket = socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)
print("Attempting to connect to B's server socket...")
while not const.SOCKETPATH.is_socket():
    sleep(0.5)
client_socket.connect(str(const.SOCKETPATH))

print('Sharing my PostgreSQL connection with process B...')
socket.send_fds(client_socket, [b"Here have my DB connection's file descriptor, you're welcome"], [dbconn.connection.fileno()])

print('Waiting for process B to finish...')
client_socket.recv(1) # Block waiting for the inspector side to close the connection, at which point we'll be able to use it again
print('New uncommitted state:\t', *dbconn.execute('SELECT * FROM fun;'))
print('Rolling back...')
dbconn.execute('ROLLBACK;')

And Process B, the inspector:

#!/usr/bin/env python3
"""
This is "process B". This is where we'll receive a file descriptor of process A's PostgreSQL connection,
and then we operate on the uncommitted state of the transaction bound to that connection.
"""
import socket
from os import dup2
from psycopg import connect

import const

server_socket = socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)
server_socket.bind(str(const.SOCKETPATH))
server_socket.listen()
print('Waiting for connections at\t', const.SOCKETPATH)
conn, *_ = server_socket.accept()
print('Client connected.')
const.SOCKETPATH.unlink()  # once client has connected, we may as well clean up the socket

_msg, fds, *_ = socket.recv_fds(conn, 1024, 1)
fd = fds[0]
print('Received a file descriptor:\t', fd)

# can't instantiate a psycoPG Connection with an extant FD, but we can... switch things around!
dbconn = connect(const.DUMMY_DB_URL)
dup2(fd, dbconn.connection.fileno()) # atomically swap the dummy FD out for the received FD

# let's see some uncommitted state created by process A!
print('Uncommitted state from process A:\t', *dbconn.execute('SELECT * FROM fun;'))
dbconn.execute("UPDATE fun SET bla = 'I CAN WATCH YOU TRANSACT' where id = 1;")
print('Updated uncommitted state to:\t', *dbconn.execute('SELECT * FROM fun;'))

# Hack: when we exit, psycopg will terminate the connection. Give it a dummy FD so that process A can
# continue to use it.
f = open('/dev/null', 'wb')
dup2(f.fileno(), dbconn.connection.fileno())

Start A, then start B, and you should see A’s output like this:

Uncommitted state:  (1, 'Would you look at all this uncommitted state!')
Attempting to connect to B's server socket...
Sharing my PostgreSQL connection with process B...
Waiting for process B to finish...
New uncommitted state:  (1, 'I CAN WATCH YOU TRANSACT')
Rolling back...

And B’s output like this:

Waiting for connections at  /tmp/funnybusiness.sock
Client connected.
Received a file descriptor: 5
Uncommitted state from process A:   (1, 'Would you look at all this uncommitted state!')
Updated uncommitted state to:   (1, 'I CAN WATCH YOU TRANSACT')

❗And here we have it. The processes can see and modify eachother’s uncommitted data. And nothing exploded. QED. But, questions!

Should I use this technique ?

With utmost caution. We’re not using things the way they are intended to be used (aka having fun).

Can we use this to have some kind of breakpoint/inspection hatch in SQL?

I wish! Alas, not in pure SQL. But with cooperation/preparation in the inspectee client application, yes. The problem is the connection state. We’d want to have the “inspectee” SQL code execution be paused at the breakpoint (say, we build on some blocking primitive to give ourselves SELECT breakpoint() functionality), yet we’d also want to continue executing commands and retrieving results on that very same connection. That’s not possible.

But if we can break up the flow in the inspectee application code, then we can create a window in which we wait for the debugging to finish (using some form of IPC, or just an inelegant timeout, or a breakpoint to resume from manually) before resuming executing SQL on the connection. That’s basically what’s happening in the POC code above.

Can I keep using this shared connection from more than one thread/process at a time?

Yes, with synchronization, as in the POC. Threads of execution will have to take turns, otherwise reads from and writes to the connection can end up mixed and then what’s eventually serialized on the wire may make no sense to the server or the clients — eg one client reads half a query result frame, the other reads the other half, neither will look like a proper wire format result frame.

Does it work with TCP database connections?

Yes, as long as we’re not using SSL. So why is that?

Well, with files, UNIX domain sockets, and TCP sockets, a bunch of state (for files, a few things such as offset & inode, but for TCP, a lot) is kept kernelside. The handle for that state is the file descriptor (which is a number, local to a process). If we have that handle, we can operate on a TCP connection. For simple operations (read, write, close) we don’t even need to know what we’re operating on, it’ll just work (that’s the “everything is a file [descriptor]” mantra at work) — an thus a simple program (such as our demo) needs no more context to do its reads and writes than the file descriptor number, which it will pass along in every read/write/close/… system call.

Of course the database driver (psycopg in this case) can keep all kinds of other state as well. In the case of this demo, for instance, the receiving side is still under the impression that it’s connected to the database called dummy (check dbconn.connection.info.dbname after the fdup2() switch), whereas it’s now actually connected to database test.

The switcheroo that we’re doing using fdup2() could of course lead to inconsistencies and errors, but for our demonstration of the principle, it doesn’t. Whether we’ll have problems will depends on our DB driver and what we’re doing.

For instance, it’s within one’s powers to write a DB driver that executes SELECT current_database() every now and then, crashing when it doesn’t match the database the connection was instantiated with — but why would one? Anyhow — the question was not whether the switcheroo is guaranteed utterly transparent, the question was whether we could read uncommitted transaction data created by another process, and the answer is yes, even when using the psycoPG DB driver which doesn’t lend itself to cloning extraneous context.

However. With SSL, there will be a bunch of critical clientside state that is particular to that specific SSL connection instantiation. The process that receives the file descriptor will not have that state and can’t use the connection. Well, sure, it can read() from and write() to it, but it’ll be reading encrypted information for which it doesn’t have the correct cipher context to decrypt. Conversely, whatever it writes will not be decryptable by the database server per its understanding of the SSL state of the connection either. That SSL state is also not easy to synchronize between processes, as it changes over the course of connection usage (think about cipher block chaining mode, for instance).

PostgreSQL uses SSL opportunistically (it defaults to sslmode=prefer), so make sure that you disable that if you want to play with this demo using TCP sockets.

What if the application doesn’t cooperate with sending its connection file descriptor over a socket?

We can clone a file descriptor from another process without it having any say in it, using the pidfd_getfd() system call, for which Python support will land in cpython-3.16. That’s nice, it’ll obviate the need for a Unix socket connection between A and B to do the file descriptor gifting with.

Of course we’ll still need to figure out the connection’s file descriptor from process A, and maybe the host language or driver of that process doesn’t expose that easily.

But we can derive it externally.

For TCP, it’s straightforward, for example, for a process with PID 9000 connected to a PostgreSQL server at 127.0.0.1:5432 :

lsof -i 'TCP@127.0.0.1:5432' -a -p 9000

For Unix sockets, I haven’t found a truly great way. The below cross-references the kernel-internal connection inode numbers of unix connections of the process with pid 9000 (using lsof), with the inode numbers associated to connections to the PostgreSQL unix server socket (using ss):

lsof -U -a -p 9000 -E | egrep "INO=($(ss --processes --oneline --extended --no-queues --no-header --unix '( src = unix:/run/postgresql/.s.PGSQL.5432 )' | awk '{printf "%s%s", (NR==1 ? "" : "|"), $4} END {print ""}')) "

In either case the file descriptor numbers will be in the 4th column of the output. But course if the program has multiple connections open, we won’t know which is which. Cross-referencing with PostgreSQL’s pg_stat_activity view could help.

Footnotes