Toolssupabasepostgres

The Supabase pooler keeps your session settings, for someone else

Christian Drexler

I wanted to look at a few rows in a production database, and I wanted to be careful about it. So before the first query I made my session read-only:

set session characteristics as transaction read only;

It is a sensible habit on a direct connection. Whatever I type afterwards, the database will refuse to change anything. I ran my selects, got my answer and disconnected.

A few minutes later a sign-up on the application failed with a server error. The log had the reason:

ERROR: cannot execute INSERT in a read-only transaction (SQLSTATE 25006)

The application had not been deployed or changed in any way. It was trying to create a profile row, exactly as it had a thousand times before, and the database was telling it that it was not allowed to write.

What I had done

I had not connected to the database directly. I had used the connection string on port 6543, which is Supabase's pooler in transaction mode.

A transaction pooler does not give you a connection of your own. It keeps a small number of real database connections open and lends one to each client for the length of one transaction. When your transaction ends, the same real connection goes to whoever asks next. That is how a few dozen database connections can serve thousands of short-lived clients, such as serverless functions.

The catch is in the word session. A SET at session level changes a property of the real connection, and it does not belong to me as a client. In transaction mode there is no such thing as my session. There is the pooled connection I happened to be holding, and my setting stayed on it when I let go.

So one of the pooled connections was now read-only for everyone. Every request that happened to be handed that connection could read and could not write. Requests that got any of the others worked. From the application's side that looks like an insert that fails sometimes, for no reason, with nothing in common between the failures.

The safe way to be careful

The intention was right. It needs the form that ends with the transaction.

begin transaction read only;

select id, email, created_at from profiles order by created_at desc limit 20;
-- more selects

rollback;

begin transaction read only applies to this one transaction. The rollback at the end closes it, and the connection goes back to the pool in exactly the state it was in. Nothing I do inside can change data, and nothing I set can outlive me.

The rule I took from it: through a pooler, anything that should be temporary has to live inside a transaction. That covers set local in place of set, begin ... read only in place of session characteristics, and no session-level statement at all on port 6543. If I really need session behaviour, for example a long analysis with temporary tables, I use the direct connection on port 5432, where the session is actually mine.

Finding the poisoned connection

The setting does not go away by itself. It stays until that real connection is closed, which can be a long time on a busy pool. So I had to find it.

Each query through the pooler lands on some backend. Asking many times in parallel hits most of them:

for i in $(seq 1 30); do
  psql "$POOLER_URL" -Atc \
    "select pg_backend_pid(), current_setting('default_transaction_read_only')" &
done | sort -u
wait

The output is a short list of process IDs, each with off, and in my case one with on. That one was the connection I had left behind.

Closing it is one statement, with the process ID from the list:

select pg_terminate_backend(1234567);

The pooler notices the connection is gone and opens a fresh one, with default settings. I ran the loop again, every line said off, and the sign-up worked on the next try.

How much damage

In my case, one failed sign-up, and it was my own test account. The time between my SET and the fix was short, and traffic was low. I was lucky that I tested the sign-up right after, for an unrelated reason.

The uncomfortable part is how this would have looked without that luck. A fraction of write requests failing, the fraction depending on pool size. No deployment to blame, and no error in the application code. It would have lasted until the pooler happened to recycle that connection, and then healed itself with no explanation. That is the kind of incident that ends with "could not reproduce".

The short version

  • Port 6543 is a shared pool. Session-level SET statements there change a connection other clients will use.
  • For careful reads on production: begin transaction read only; ... rollback;.
  • For temporary settings: set local, inside a transaction.
  • If writes fail at random with SQLSTATE 25006, look for a backend where default_transaction_read_only is on, and terminate it.

I had tried to make my session safer. The pooler does not have sessions, so I had made one shared connection read-only for every client that got it after me.

supabasepostgrespgbouncergotcha