ASYNC Privileges proposal

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.

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(at)momjian(dot)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

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....

Possibly some kind of "catch-all" channel, that enables implicit channel
names?

GRANT LISTEN, NOTIFY ON CHANNEL * TO PUBLIC; -- enabled by default for
backwards compat
NOTIFY xxxx; -- OK via * CHANNEL
LISTEN xxxx; -- OK via * CHANNEL

Chris

On 18 June 2013 18:31, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

>
> >> 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