Hey all,
I find the current LISTEN / NOTIFY rather limited in the context of databases with multiple roles. As it stands it is not possible to restrict the use of LISTEN or NOTIFY to specific roles, and therefore notifications (and their payloads) cannot really be trusted as coming from any particular source.
If the payloads of notifications could be trusted, then applications could make better use of them, without fear of leaking any sensitive information to anyone who shouldn't be able to see it.
I'd like to propose a new ASYNC database privilege that would control whether a role can use LISTEN, NOTIFY and UNLISTEN statements and the associated pg_notify function.
ie:
GRANT ASYNC ON DATABASE xxxx TO bob;
REVOKE ASYNC ON DATABASE xxxx FROM bob;
SECURITY DEFINER functions could then be used anywhere that a finer grained access control was required.
I had a quick play to see what might be involved [attached], and would like to hear people thoughts; good idea, bad idea, not like that! etc
Chris.