Thread: ASYNC Privileges proposal

On Mon, May 20, 2013 at 02:44:58AM +0100, Chris Farmiloe wrote:
> 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  

I question the usefulness of allowing listen/notify to be restricted to
an entire class of users.  The granularity of this seems too broad,
though I am not sure if allowing message to be sent to a specific user
is easily achievable.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +

>> 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  
> 
> I question the usefulness of allowing listen/notify to be restricted to
> an entire class of users.  The granularity of this seems too broad,
> though I am not sure if allowing message to be sent to a specific user
> is easily achievable.

Well, if we're going to have privs on LISTEN/NOTIFY at all, they should
be on specific message bands, i.e.:

REVOKE LISTEN ON 'cacheupdates' FROM PUBLIC;
GRANT LISTEN ON 'cacheupdates' TO webuser;
GRANT LISTEN ON ALL TO admin;

I can certainly see wanting this, but it'd be a great deal more
sophisticated than what Chris has proposed.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 06/27/2013 02:49 AM, Chris Farmiloe wrote:
> So I would think that if this was to go further then "channels" would need
> to be more of a first class citizen and created explicitly, with CREATE
> CHANNEL, DROP CHANNEL etc:
> 
>     CREATE CHANNEL channame;
>     GRANT LISTEN ON CHANNEL channame TO rolename;
>     GRANT NOTIFY ON CHANNEL channame TO rolename;
>     LISTEN channame; -- OK
>     NOTIFY channame, 'hi'; -- OK
>     LISTEN xxxx; -- exception: no channel named "xxxx"
>     NOTIFY xxxx, 'hi'; -- exception: no channel named "xxxx"
> 
> Personally I think explicitly creating channels would be beneficial; I have
> hit issues where an typo in a channel name has caused a bug to go unnoticed
> for a while.
> ....But of course this would break backwards compatibility with the current
> model (with implicit channel names) so unless we wanted to force everyone
> to add "CREATE CHANNEL" statements during their upgrade then, maybe there
> would need to be some kind of system to workaround this....

I agree, but that would make this a MUCH bigger patch than it is now.
So the question is whether you're willing to go there.

If nobody wants to work on that, I don't find it unreasonable to have
some permissions on LISTEN/NOTIFY period.  However, I'd like to see
separate permissions on LISTEN and NOTIFY; I can easily imagine wanting
to grant a user one without the other.

Also, someone would need to do performance tests to see how much the
permissions check affects performance.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com