Listen for postgres events using owner connection url

[jhg03a]

Feature description

I'd like the pg client that's handling the notification subscription to use the owner connection string instead of the client connection string.

Motivating example

In my environment I use pgbouncer in transaction pool mode by default. This unfortunately silently eats all notifications coming from postgres as there's not a session to send them back to. I do separate my postgres client connection string from the owner connection string to limit superuser exposure. This enables me to still keep the more optimal transaction pooling of pgbouncer for routine client queries/mutations. The present workaround is to either use session pooling in pgbouncer or drop it entirely unfortunately.

Breaking changes

For users who don't separate the owner connection string, there's no difference. For users that do, it might change behavioral expectations on that owner client connection to be short lived.

Supporting development

I [tick all that apply]:

• am interested in building this feature myself
• am interested in collaborating on building this feature
• am willing to help testing this feature before it's released
• am willing to write a test-driven test suite for this feature (before it exists)
• am a Graphile sponsor ❤️
• have an active support or consultancy contract with Graphile

[benjie]

I think having the option to do this kind of thing via a dedicated client makes sense, but it's not clear to me that ownerConnectionString is the right client for this. This might also overlap with requirements for subscriptions on replicas via readOnlyConnectionString. More thought required 🤔

[benjie]

I think to do this right what we actually want is a "listenClient" that gets passed all the way through PostGraphile - this way the pg-pubsub listeners can use it as well as the introspection plugin. The issue is if the client disconnects. We don't want to pass a "listenPool" because then we'd have to get two separate connections from it (one for introspection, one for pg-pubsub)... So really we want a managed listener that manages its own client but is shared by the whole stack. That's a bit of an ask for v4...

In the mean time, maybe you can set up an external listener for these events and then patch those events back into the relevant pgClient using pgClient.emit(...)?