What the article doesn’t mention is that the pg_notify() function accepts standard strings for the channel name and payload, and you can parameterize them as $1, $2. Whereas with the NOTIFY command, neither the channel name nor the payload string are parameterizable (even the payload has to be a string literal). This is important for security, when you want either to be dynamic - you would have to splice them into the query string.
With the command, of course you can still escape the payload using regular string escapes, and the channel is an identifier, so you can use identifier escape syntax for “special” names. But it’s still needlessly restrictive/dangerous.
At my work our backend uses LISTEN/NOTIFY to listen to database changes and inform the UI over the websocket connection if the user’s (who is using the browser) view needs to be refreshed.
I think the idea is good but our implementation is not good. Would love to see better working examples of something like this.
I’m doing the same in a Phoenix app. I start a GenServer as part of the app that handles the listens and sends out an Endpoint.broadcast/3 when a relevant one comes in (on the busy one that broadcast includes the query results the clients crave). The LiveView instances clients are on subscribe to the endpoint channel when they start up, so the updated query results generate new HTML and bang it out to the browsers.
I dunno about your implementation, but like you said the idea sounds fine. We did something similar at a previous job. We had a 3rd party integration which made changes to certain models in the background, which would then trigger LISTEN/NOTIFY to tell other parts of the software to restart a computation.
In my current job, we also have a “main” server which runs and sends updates to clients, and when a command line task or cron job makes some changes to the db, it informs the main server about the changes so that it can send updates to the clients.
cool, but it’s not durable (a disconnected listener wouldn’t see notifications during the time it’s not listening), which might be ok for some use cases. Also the payload size is limited, so don’t try to put too big payloads in there, it wasn’t meant for that.
In the use cases where I had to do something like that, the listener would either on startup perform a fresh query to see what’s new (or just what’s there), or didn’t have to do anything for $reasons. The payload should typically just be a primary key value and the entity name, so that the listener itself can fetch the thing that changed.
val = time.time()
cursor.execute(f"NOTIFY match_updates, '{val}';")
not a great way to escape your inputs. Wouldn’t python’s open classes allow me to monkey patch time.time() to return '; DROP DATABASE little_bobby_tables; -- ?
While I agree that you should use parameters for every dynamic value I think this case can be considered safe. If someone can monkey patch time.time you are already screwed, they could just run the drop command directly.
I think Postgres’s LISTEN / NOTIFY is fantastic, and not only just because it’s a notification mechanism conveniently built into the database that you’re already spending an effort to keep running and managing its credentials etc, but also because it interacts nicely with Postgres’s transactions.
That said, I think the notification payload comes with big gotchas as it is implemented right now!
First of all, you should always make sure that your use of LISTEN / NOTIFY is disconnect-resistant. I.e, you can’t just rely solely on the information contained in the payload, there must be some sort of snapshot table that you read right after LISTENing whenever you reconnect.
The second and bigger problem is, Postgres doesn’t have any user privileges that manage access to channels neither while LISTENing nor NOTIFYing. That means, if you’re relying on multiple roles and Postgres’s access policies as an additional layer of security, you can’t trust the notification payload while LISTENing, and you can’t be sure who’s LISTENing when you NOTIFY with a payload.
My take away from these observations is that the only correct way to use LISTEN / NOTIFY is as an optimization mechanism that signals the need to go read the latest information on a regular table. I.e the reconnect logic is simply equivalent to receiving a no-payload notification and the lack of LISTEN / NOTIFY access policies doesn’t matter, because the payload is only meaningful to users that have access to the “ground truth” table(s) and it can never mislead a LISTENer because it just tells you to go look at something.
I think this perspective is also in line with LISTEN / NOTIFY’s semantics where Postgres is allowed to smash multiple notifications with the same payload into a single notification. So, for example it’s wrong to count the number of notifications one receives. The only correct way to react to a notification is to go read the latest state.
I’ve used this a few times and my biggest roadblock is always the intermediate tools. psycopg2 doesn’t deal with it well, pgbouncer seems to get confused, sqlalchemy doesn’t know how to use it right with a connection pool, etc
Then perhaps you should consider using better tools…
Having said that, AFAIK psycopg2 can handle it just fine, it’s just not super well-documented. There’s another comment here that explains how to do it. Essentially, you take the connection and call poll() on it and when that (or a query result) returns, you can iterate through conn.notifies.
What the article doesn’t mention is that the
pg_notify()function accepts standard strings for the channel name and payload, and you can parameterize them as$1,$2. Whereas with theNOTIFYcommand, neither the channel name nor the payload string are parameterizable (even the payload has to be a string literal). This is important for security, when you want either to be dynamic - you would have to splice them into the query string.With the command, of course you can still escape the payload using regular string escapes, and the channel is an identifier, so you can use identifier escape syntax for “special” names. But it’s still needlessly restrictive/dangerous.
At my work our backend uses LISTEN/NOTIFY to listen to database changes and inform the UI over the websocket connection if the user’s (who is using the browser) view needs to be refreshed.
I think the idea is good but our implementation is not good. Would love to see better working examples of something like this.
I’m doing the same in a Phoenix app. I start a
GenServeras part of the app that handles the listens and sends out anEndpoint.broadcast/3when a relevant one comes in (on the busy one that broadcast includes the query results the clients crave). TheLiveViewinstances clients are on subscribe to the endpoint channel when they start up, so the updated query results generate new HTML and bang it out to the browsers.I dunno about your implementation, but like you said the idea sounds fine. We did something similar at a previous job. We had a 3rd party integration which made changes to certain models in the background, which would then trigger
LISTEN/NOTIFYto tell other parts of the software to restart a computation.In my current job, we also have a “main” server which runs and sends updates to clients, and when a command line task or cron job makes some changes to the db, it informs the main server about the changes so that it can send updates to the clients.
Here is how you can use it as a simple message queue in Python: https://gist.github.com/kissgyorgy/beccba1291de962702ea9c237a900c79
cool, but it’s not durable (a disconnected listener wouldn’t see notifications during the time it’s not listening), which might be ok for some use cases. Also the payload size is limited, so don’t try to put too big payloads in there, it wasn’t meant for that.
In the use cases where I had to do something like that, the listener would either on startup perform a fresh query to see what’s new (or just what’s there), or didn’t have to do anything for
$reasons. The payload should typically just be a primary key value and the entity name, so that the listener itself can fetch the thing that changed.Hey I did exactly the same on a little idea I had :) https://github.com/docteurklein/pg-sse/blob/master/src/main.rs#L70-L90
not a great way to escape your inputs. Wouldn’t python’s open classes allow me to monkey patch
time.time()to return'; DROP DATABASE little_bobby_tables; --?While I agree that you should use parameters for every dynamic value I think this case can be considered safe. If someone can monkey patch time.time you are already screwed, they could just run the drop command directly.
I think Postgres’s LISTEN / NOTIFY is fantastic, and not only just because it’s a notification mechanism conveniently built into the database that you’re already spending an effort to keep running and managing its credentials etc, but also because it interacts nicely with Postgres’s transactions.
That said, I think the notification payload comes with big gotchas as it is implemented right now!
First of all, you should always make sure that your use of LISTEN / NOTIFY is disconnect-resistant. I.e, you can’t just rely solely on the information contained in the payload, there must be some sort of snapshot table that you read right after LISTENing whenever you reconnect.
The second and bigger problem is, Postgres doesn’t have any user privileges that manage access to channels neither while LISTENing nor NOTIFYing. That means, if you’re relying on multiple roles and Postgres’s access policies as an additional layer of security, you can’t trust the notification payload while LISTENing, and you can’t be sure who’s LISTENing when you NOTIFY with a payload.
My take away from these observations is that the only correct way to use LISTEN / NOTIFY is as an optimization mechanism that signals the need to go read the latest information on a regular table. I.e the reconnect logic is simply equivalent to receiving a no-payload notification and the lack of LISTEN / NOTIFY access policies doesn’t matter, because the payload is only meaningful to users that have access to the “ground truth” table(s) and it can never mislead a LISTENer because it just tells you to go look at something.
I think this perspective is also in line with LISTEN / NOTIFY’s semantics where Postgres is allowed to smash multiple notifications with the same payload into a single notification. So, for example it’s wrong to count the number of notifications one receives. The only correct way to react to a notification is to go read the latest state.
I’ve used this a few times and my biggest roadblock is always the intermediate tools. psycopg2 doesn’t deal with it well, pgbouncer seems to get confused, sqlalchemy doesn’t know how to use it right with a connection pool, etc
Then perhaps you should consider using better tools…
Having said that, AFAIK psycopg2 can handle it just fine, it’s just not super well-documented. There’s another comment here that explains how to do it. Essentially, you take the connection and call
poll()on it and when that (or a query result) returns, you can iterate throughconn.notifies.I’ve never had issues with psycopg2 and PG’s listen/notify. I can’t comment on the other tools.