Обсуждение: ASYNC Privileges proposal

Поиск
Список
Период
Сортировка

ASYNC Privileges proposal

От
Chris Farmiloe
Дата:
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.

Вложения

Re: ASYNC Privileges proposal

От
Tom Lane
Дата:
Chris Farmiloe <chrisfarms@gmail.com> writes:
> 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.

TBH, nobody has complained about this in the fifteen-plus years that
LISTEN has been around.  I'm dubious about adding privilege-checking
overhead for everybody to satisfy a complaint from one person.

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

... and if I did think that there were an issue here, I doubt I'd think
that a privilege as coarse-grained as that would fix it.  Surely you'd
want per-channel privileges if you were feeling paranoid about this,
not to mention separate read and write privileges.  But the demand for
that just isn't out there.
        regards, tom lane



Re: ASYNC Privileges proposal

От
Chris Farmiloe
Дата:
In fairness NOTIFY has only had a payload since v9 (maybe 8.4?), and the issue of trust is mainly tied to data leaking from the payload, so I suspect I won't be last person to request this as people re-visit NOTIFY :)
...but I totally get your point of course.

My first thought was also that having control at the channel-level would be ideal, but that would be a huge implementation change by the looks of things, would certainly affect performance a great deal more and would not really give much more benefit that could be attained with database-level control + a "SECURITY DEFINER" function.


Chris


On 20 May 2013 03:23, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Chris Farmiloe <chrisfarms@gmail.com> writes:
> 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.

TBH, nobody has complained about this in the fifteen-plus years that
LISTEN has been around.  I'm dubious about adding privilege-checking
overhead for everybody to satisfy a complaint from one person.

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

... and if I did think that there were an issue here, I doubt I'd think
that a privilege as coarse-grained as that would fix it.  Surely you'd
want per-channel privileges if you were feeling paranoid about this,
not to mention separate read and write privileges.  But the demand for
that just isn't out there.

                        regards, tom lane

Re: ASYNC Privileges proposal

От
Craig Ringer
Дата:
On 05/20/2013 09:54 AM, 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.
Just for context, here's the dba.stackexchange.com question where this
was previously raised:

http://dba.stackexchange.com/q/42496/7788

I agree that this only became relevant since NOTIFY payload support was
added, so we can't really just say "nobody's wanted this in 15 years". A
couple of years, maybe, but given the lagging client driver support for
NOTIFY payloads, maybe not even that.

That said, I personally don't see this as a significant problem since
the approach that worked prior to the introduction of notify payloads -
just using a staging table - continues to work just fine. I  agree with
Tom that much finer grained control would be required if adding
privileges at all were to be particularly useful; such a coarse right as
Chris proposes seems unlikely to cover many use cases. We'd likely
control over who can NOTIFY and who can LISTEN to a given channel name.

I'm just not convinced it's worth the complexity it'll introduce or any
performance consequences. If it could be done by adding a few hooks that
a C extension could use, that might be worthwhile though.

The same argument might be applied to advisory locking, after all. I
can't really see adding fine-grained rights to _everything_, but hooks
might not be harmful.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services