Re: [HACKERS] ASYNC Privileges proposal
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 ; -- exception: no channel named NOTIFY , 'hi'; -- exception: no channel named 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 ; -- OK via * CHANNEL LISTEN ; -- OK via * CHANNEL Chris On 18 June 2013 18:31, Josh Berkus j...@agliodbs.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
Re: [HACKERS] ASYNC Privileges proposal
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 ; -- exception: no channel named NOTIFY , 'hi'; -- exception: no channel named 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ASYNC Privileges proposal
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 TO bob; REVOKE ASYNC ON DATABASE 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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 TO bob; REVOKE ASYNC ON DATABASE 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. async_privileges_r0.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ASYNC Privileges proposal
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 TO bob; REVOKE ASYNC ON DATABASE 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. async_privileges_r0.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ASYNC Privileges proposal
Chris Farmiloe chrisfa...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ASYNC Privileges proposal
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 t...@sss.pgh.pa.us wrote: Chris Farmiloe chrisfa...@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
