Re: [HACKERS] SET variable - Permission issues
Kevin Grittner wrote: Joe Conway m...@joeconway.com wrote: On 10/10/2011 01:52 PM, Gurjeet Singh wrote: ALTER USER novice SET MIN_VAL OF statement_timeout TO '1'; -- So that the user cannot turn off the timeout ALTER DATABASE super_reliable SET ENUM_VALS OF synchronous_commit TO 'on'; -- So that the user cannot change the synchronicity of transactions against this database. I like this better than GRANT/REVOKE on SET. +1 I would really like a way to prevent normal users from switching from the default transaction isolation level I set. This seems like a good way to do that. Putting sane bounds on some other settings, more to protect against the accidental bad settings than malicious mischief, would be a good thing, too. Is this a TODO? We might not want to make work_mem SUSET, but it would allow administrators to control this. -- 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
Re: [HACKERS] SET variable - Permission issues
Bruce Momjian br...@momjian.us wrote: Kevin Grittner wrote: Joe Conway m...@joeconway.com wrote: On 10/10/2011 01:52 PM, Gurjeet Singh wrote: ALTER USER novice SET MIN_VAL OF statement_timeout TO '1'; -- So that the user cannot turn off the timeout ALTER DATABASE super_reliable SET ENUM_VALS OF synchronous_commit TO 'on'; -- So that the user cannot change the synchronicity of transactions against this database. I like this better than GRANT/REVOKE on SET. +1 I would really like a way to prevent normal users from switching from the default transaction isolation level I set. This seems like a good way to do that. Putting sane bounds on some other settings, more to protect against the accidental bad settings than malicious mischief, would be a good thing, too. Is this a TODO? We might not want to make work_mem SUSET, but it would allow administrators to control this. Well, we've identified a few people who like the idea, but I'm not sure we have the degree of consensus we normally look for before putting something on the TODO list. After the discussion on this thread, are there still any *objections* to allowing bounds or subsets to be SUSET to limit GUC values more strictly than the limits hard-coded in C? -Kevin -- 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] SET variable - Permission issues
On 10/11/2011 11:53 AM, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: Is this a TODO? We might not want to make work_mem SUSET, but it would allow administrators to control this. Well, we've identified a few people who like the idea, but I'm not sure we have the degree of consensus we normally look for before putting something on the TODO list. That's pretty much what I was thinking. After the discussion on this thread, are there still any *objections* to allowing bounds or subsets to be SUSET to limit GUC values more strictly than the limits hard-coded in C? No objections here. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -- 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] SET variable - Permission issues
Kevin Grittner kevin.gritt...@wicourts.gov writes: Well, we've identified a few people who like the idea, but I'm not sure we have the degree of consensus we normally look for before putting something on the TODO list. After the discussion on this thread, are there still any *objections* to allowing bounds or subsets to be SUSET to limit GUC values more strictly than the limits hard-coded in C? No objection here, I like this whole idea. Adding the information visible at the right places is a fun project in itself, too :) Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] SET variable - Permission issues
Kevin Grittner kevin.gritt...@wicourts.gov writes: Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Adding the information visible at the right places is a fun project in itself, too :) I was thinking a couple new columns in pg_settings (and what backs it) would be the main thing, but I haven't searched the source code yet. Does something else leap to mind for you? This isn't exactly a trivial matter. What happens for instance if you try to change the limit, and there are already active values outside the limit in some processes? 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] SET variable - Permission issues
Tom Lane t...@sss.pgh.pa.us wrote: This isn't exactly a trivial matter. What happens for instance if you try to change the limit, and there are already active values outside the limit in some processes? I would certainly vote for enforcing on the SET and not causing an error on the attempt to change the limit. (Maybe a notice?) At the time they set the GUC, they were allowed to do so. It's a bit like revoking a user's right to create a table in a schema -- what if they've already done so? You leave the table and you don't let them create another. What problems do you see with that? -Kevin -- 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] SET variable - Permission issues
On 10/11/2011 02:07 PM, Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: This isn't exactly a trivial matter. What happens for instance if you try to change the limit, and there are already active values outside the limit in some processes? I would certainly vote for enforcing on the SET and not causing an error on the attempt to change the limit. (Maybe a notice?) At the time they set the GUC, they were allowed to do so. It's a bit like revoking a user's right to create a table in a schema -- what if they've already done so? You leave the table and you don't let them create another. What problems do you see with that? Yeah, I don't know why it need be handled any different than say ALTER DATABASE foo SET config_param TO value or ALTER ROLE foo SET config_param TO value These cases do not effect already existing processes either. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -- 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] SET variable - Permission issues
Joe Conway m...@joeconway.com writes: On 10/11/2011 02:07 PM, Kevin Grittner wrote: I would certainly vote for enforcing on the SET and not causing an error on the attempt to change the limit. ... What problems do you see with that? Yeah, I don't know why it need be handled any different than say ALTER DATABASE foo SET config_param TO value or ALTER ROLE foo SET config_param TO value These cases do not effect already existing processes either. It's not the same thing. Those operations are documented as providing the initial default value for subsequently-started sessions. The proposed change in limit values is different because the GUC range limits have always before been immutable and continuously enforced for the life of a database instance. It may be that Kevin's proposal is adequate. But I find that far from obvious. The trend of everything we've done with GUC for the last ten years is to cause settings changes to apply immediately on-demand and without oh, but that's obvious if you know the implementation special cases. I'm not real sure why this should get a free exemption from that expectation ... or to put it more plainly, I *am* sure that we'll be expected to fix it later, just like we had to fix the behavior around removal of postgresql.conf entries, and some other things that people didn't find as obvious as all that. 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] SET variable - Permission issues
On 10/09/2011 09:09 PM, Robert Haas wrote: Having said that, I do think it might be useful to have ways of controlling the values that users can set for GUC values, not so much as a guard against an all-out assault (which is probably futile) but as a way for DBAs to enforce system policy. But even that seems like a lot of work for a fairly marginal benefit I think the issues Josh raised are valid concerns for a number of use cases. Even if you don't want to allow anyone on the Internet into your database (as Josh does, since his application is a game and his attempt is to set policies and privileges such that it is actually safe), there are plenty of companies needing to run Postgres in a multi-tenant environment. Currently customer A can set work_mem = some very large number; and set statement_timeout = 0; and run a big query effectively DOS'ing customers B, C, and D. If these two settings could be restricted by the DBA, there would be a much lower chance of this happening. There are undoubtedly other holes to fill, but it seems like a worthy cause. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -- 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] SET variable - Permission issues
On Mon, Oct 10, 2011 at 1:06 PM, Joe Conway m...@joeconway.com wrote: On 10/09/2011 09:09 PM, Robert Haas wrote: Having said that, I do think it might be useful to have ways of controlling the values that users can set for GUC values, not so much as a guard against an all-out assault (which is probably futile) but as a way for DBAs to enforce system policy. But even that seems like a lot of work for a fairly marginal benefit I think the issues Josh raised are valid concerns for a number of use cases. Even if you don't want to allow anyone on the Internet into your database (as Josh does, since his application is a game and his attempt is to set policies and privileges such that it is actually safe), there are plenty of companies needing to run Postgres in a multi-tenant environment. Currently customer A can set work_mem = some very large number; and set statement_timeout = 0; and run a big query effectively DOS'ing customers B, C, and D. If these two settings could be restricted by the DBA, there would be a much lower chance of this happening. There are undoubtedly other holes to fill, but it seems like a worthy cause. Even in a controlled environment, say in a company where only legit apps developed in-house are run on the DB, a DBA would want peace of mind that the developers are not setting these GUCs at runtime (which is often even recommended in case of work_mem) to bypass a policy set by the DBA and are capable of bringing the DB down to its knees. Regards, -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] SET variable - Permission issues
Gurjeet Singh singh.gurj...@gmail.com writes: On Mon, Oct 10, 2011 at 1:06 PM, Joe Conway m...@joeconway.com wrote: Currently customer A can set work_mem = some very large number; and set statement_timeout = 0; and run a big query effectively DOS'ing customers B, C, and D. If these two settings could be restricted by the DBA, there would be a much lower chance of this happening. There are undoubtedly other holes to fill, but it seems like a worthy cause. Even in a controlled environment, say in a company where only legit apps developed in-house are run on the DB, a DBA would want peace of mind that the developers are not setting these GUCs at runtime (which is often even recommended in case of work_mem) to bypass a policy set by the DBA and are capable of bringing the DB down to its knees. Any developer who can't think of six ways to DOS the server without changing those settings should be fired on the spot for incompetence. I could get interested in this if it weren't that the problem is so open-ended as to be basically insoluble. The only solution to the OP's problem that's not got more holes than a wheel of Swiss cheese is to not let untrustworthy people have direct SQL access to the server. It *does not improve security* to close a couple of obvious holes and leave a bunch of other avenues to the same end open. All it does is give you a false sense of security. 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] SET variable - Permission issues
On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Any developer who can't think of six ways to DOS the server without changing those settings should be fired on the spot for incompetence. No kidding. But the point is that if the developer down the hall maliciously destroys your database server, you can go through channels and get him reprimanded or fired. But if the developer down the hall is a new hire who doesn't know beans about PostgreSQL and tries setting work_mem to 10GB, well, oops, it was an accident. And then another developer gets hired three weeks later and does the same thing. And then three months later somebody does it again. After a while people no longer remember that in each case it was a developer to blame. What they remember is that the DBA let the server go down three times. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] SET variable - Permission issues
On Mon, Oct 10, 2011 at 2:55 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Any developer who can't think of six ways to DOS the server without changing those settings should be fired on the spot for incompetence. No kidding. But the point is that if the developer down the hall maliciously destroys your database server, you can go through channels and get him reprimanded or fired. But if the developer down the hall is a new hire who doesn't know beans about PostgreSQL and tries setting work_mem to 10GB, well, oops, it was an accident. And then another developer gets hired three weeks later and does the same thing. And then three months later somebody does it again. After a while people no longer remember that in each case it was a developer to blame. What they remember is that the DBA let the server go down three times. IOW, honest mistakes happen. Would it be possible to make the SUSET/USERSET property of a GUC modifiable? So a DBA can do ALTER USER novice SET CONTEXT OF work_mem TO 'superuser'; Or, maybe ALTER USER novice SET MAX_VAL OF work_mem TO '1 MB'; and extend it to say ALTER USER novice SET MIN_VAL OF statement_timeout TO '1'; -- So that the user cannot turn off the timeout ALTER DATABASE super_reliable SET ENUM_VALS OF synchronous_commit TO 'on'; -- So that the user cannot change the synchronicity of transactions against this database. Regards, -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] SET variable - Permission issues
On 10/10/2011 01:52 PM, Gurjeet Singh wrote: On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane wrote: Any developer who can't think of six ways to DOS the server without changing those settings should be fired on the spot for incompetence. Perhaps, but I think our long term goal at least should be to make it possible to prevent that -- not necessarily the default configuration, but it should be at least *possible* for a sufficiently careful DBA to harden their postgres instance. I have multiple clients that either do run or would like to run postgres multi-tenant, and at the moment that is somewhere between risky and unacceptable. Would it be possible to make the SUSET/USERSET property of a GUC modifiable? So a DBA can do ALTER USER novice SET CONTEXT OF work_mem TO 'superuser'; Or, maybe ALTER USER novice SET MAX_VAL OF work_mem TO '1 MB'; and extend it to say ALTER USER novice SET MIN_VAL OF statement_timeout TO '1'; -- So that the user cannot turn off the timeout ALTER DATABASE super_reliable SET ENUM_VALS OF synchronous_commit TO 'on'; -- So that the user cannot change the synchronicity of transactions against this database. I like this better than GRANT/REVOKE on SET. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -- 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] SET variable - Permission issues
Joe Conway m...@joeconway.com wrote: On 10/10/2011 01:52 PM, Gurjeet Singh wrote: ALTER USER novice SET MIN_VAL OF statement_timeout TO '1'; -- So that the user cannot turn off the timeout ALTER DATABASE super_reliable SET ENUM_VALS OF synchronous_commit TO 'on'; -- So that the user cannot change the synchronicity of transactions against this database. I like this better than GRANT/REVOKE on SET. +1 I would really like a way to prevent normal users from switching from the default transaction isolation level I set. This seems like a good way to do that. Putting sane bounds on some other settings, more to protect against the accidental bad settings than malicious mischief, would be a good thing, too. -Kevin -- 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] SET variable - Permission issues
On Sat, Oct 8, 2011 at 12:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh j...@schemaverse.com writes: [ unhappy about users being able to freely adjust work_mem etc ] Really, if you're letting users issue arbitrary SQL queries, there simply isn't any way to prevent them from beating your server into the ground. I don't think that inserting a hack to prevent specific configuration variables from being adjusted is going to help you against an uncooperative user. You'd be better off to rethink the let them issue SQL queries directly part of your design. The reason that the specific variables you mention (as well as some others that bear on such things) are USERSET and not SUSET is precisely that we are not trying to constrain the amount of resources an uncooperative user can consume. If we did try to do that, quite a lot of design decisions would have to be revisited, and there would be a number of unpleasant tradeoffs to be made. GUC privilege levels are just the tip of the iceberg. Yeah. For example, if somebody writes a complicated query against a gigantic table that runs for a long time, is that because they're trying to DOS the server, or because they have a legitimate need for the results of that query, and it just so happens to be an expensive query? That's not really a question a computer can answer, and if you restrict users to running only queries that are so short and simple that they can't be used to DOS the box, you'll likely also be locking out a significant percentage of legitimate user needs. Having said that, I do think it might be useful to have ways of controlling the values that users can set for GUC values, not so much as a guard against an all-out assault (which is probably futile) but as a way for DBAs to enforce system policy. But even that seems like a lot of work for a fairly marginal benefit -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] SET variable - Permission issues
Robert Haas robertmh...@gmail.com writes: On Sat, Oct 8, 2011 at 12:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: The reason that the specific variables you mention (as well as some others that bear on such things) are USERSET and not SUSET is precisely that we are not trying to constrain the amount of resources an uncooperative user can consume. If we did try to do that, quite a lot of design decisions would have to be revisited, and there would be a number of unpleasant tradeoffs to be made. GUC privilege levels are just the tip of the iceberg. Yeah. For example, if somebody writes a complicated query against a gigantic table that runs for a long time, is that because they're trying to DOS the server, or because they have a legitimate need for the results of that query, and it just so happens to be an expensive query? That's not really a question a computer can answer, and if you restrict users to running only queries that are so short and simple that they can't be used to DOS the box, you'll likely also be locking out a significant percentage of legitimate user needs. Rereading the original message, I see that Josh raised an independent point which I failed to see was independent. Namely, that if one has a SECURITY DEFINER function that relies on random() producing unpredictable values, then an unprivileged user might be able to compromise the behavior of the function by issuing SET SEED just before calling the function. That does seem like a potential security issue --- not a very big one, but still undesirable. And unlike other GUCs that might affect query behavior, a SECURITY DEFINER function can't protect itself against such a thing by adding SET clauses, because setting the seed to a known value is exactly what it doesn't want. I still don't think that a specialized GUC assignment privilege is worth the trouble, but I could see an argument for just changing the seed GUC from USERSET to SUSET. 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] SET variable - Permission issues
Josh j...@schemaverse.com writes: [ unhappy about users being able to freely adjust work_mem etc ] Really, if you're letting users issue arbitrary SQL queries, there simply isn't any way to prevent them from beating your server into the ground. I don't think that inserting a hack to prevent specific configuration variables from being adjusted is going to help you against an uncooperative user. You'd be better off to rethink the let them issue SQL queries directly part of your design. The reason that the specific variables you mention (as well as some others that bear on such things) are USERSET and not SUSET is precisely that we are not trying to constrain the amount of resources an uncooperative user can consume. If we did try to do that, quite a lot of design decisions would have to be revisited, and there would be a number of unpleasant tradeoffs to be made. GUC privilege levels are just the tip of the iceberg. 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
