Add support for listening to database notifications

[carragom]

Problem

I would like for PostgREST to provide a way for a client to listen for database events like those created with NOTIFY channel [ , payload ]. I found #1388 but I feel that got side tracked so I would like to propose a more focused and hopefully minimal feature request here.

Solution

I know very little about the internals of PostgREST so forgive me if this section makes no sense in the context of this project.

As a general approach I think server-sent events would be the logical choice for this. So maybe have an endpoint at /rpc/listen?channels=chat that will register the client and a list of channels it wants to listen on. Have a configuration option db-channels or similar which limits the channels exposed by the /rpc/listen endpoint. This option would be empty by default which means all requests to /rpc/listen would return a client error. In this example there would be db-channels=chat to expose the chat channel.

PostgREST would be in charge of the following:

1. On startup create a listener for all channels in db-channels
2. Add the client and it's channels to a client list when requested via /rpc/listen
3. Remove the client from the list on disconnect/timeout
4. Route database notifications to clients on the list interested on the specific channel
5. Make sure only authenticated users can call /rpc/listen
6. Make sure a user can only subscribe to channels allowed in db-channels

Use case

Here I would like to explain a possible use case for this feature but keep in mind this is all user code and not related to PostgREST itself.

Lets assume I have a table called data and as a client I would like to be notified when the data table is changed in any way. I could create a trigger like this:

create function notify_change()
	returns trigger
	language plpgsql
as $$
declare
	rec record;
	payload jsonb;
begin
	case tg_op
		when 'insert', 'update' then
			rec := new;
		when 'delete' then
			rec := old;
		else
			-- could happen in the future ?
			raise exception 'unknown tg_op: "%".', tg_op;
	end case;

	payload := json_build_object(
		'timestamp', current_timestamp,
		'operation', lower(tg_op),
		'schema', tg_table_schema,
		'table', tg_table_name,
		'row', row_to_json(rec)
	);

	-- notify the table general channel, useful to listen for all operations on all tables
	perform pg_notify('table', payload::text);
	-- notify the table-<tg_op> dynamic channel, useful to listen for specific operations on all tables, e.g. table-update or table-insert
	execute format('select pg_notify(%l, %l)', format('table-%s', lower(tg_op)), payload::text);
	-- notify the table-<tg_table_name> dynamic channel, useful to listen for all operations on a specific table, e.g. table-data
	execute format('select pg_notify(%l, %l)', format('table-%s', tg_table_name), payload::text);
	return rec;
end
$$;

This trigger could be attached to the data table and any other table to generate notifications when those tables change. This trigger would notify on three different channels:

• table: Notifies of all operations on any table
• table-<table_name>: Notifies of any operation a specific table
• table-<insert,update,delete,truncate>: Notifies of specific operation on any table

Then as a client I could register to /rpc/listen?channel=table-data and be notified of any change on the data table and be provided with all the information on the event itself including the changed row.

The above example is simple and needs careful consideration specially on the security side of things but this would not be a PostgREST responsibility just like PostgreSQL itself is not responsible for the security of the data transmitted using NOTIFY channel [ , payload ].

Thanks a lot for the great project.