Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Again, what are we trying to achieve?! no idea - wondering about that myself... It seems we are trying to add grammar for modifying postgresql.conf. Something we can already do easily in a standard extension, but without grammar changes. Maybe better to provide a contrib/ to modify config, then design what we can achieve more with an ALTER SYSTEM command. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
=?iso-8859-1?q?C=E9dric_Villemain?= ced...@2ndquadrant.com writes: Again, what are we trying to achieve?! no idea - wondering about that myself... It seems we are trying to add grammar for modifying postgresql.conf. Something we can already do easily in a standard extension, but without grammar changes. Maybe better to provide a contrib/ to modify config, then design what we can achieve more with an ALTER SYSTEM command. Hmm ... putting the UI into a contrib module would neatly solve the problem for people who don't want the functionality. They just don't install the module. Another thought here is that it seems like we've been discussing multiple scenarios that don't necessarily all have the same best solution. Maybe we should think in terms of multiple contrib modules. 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On Tue, Aug 6, 2013 at 12:40:12PM +0200, Cédric Villemain wrote: Again, what are we trying to achieve?! no idea - wondering about that myself... It seems we are trying to add grammar for modifying postgresql.conf. Something we can already do easily in a standard extension, but without grammar changes. Maybe better to provide a contrib/ to modify config, then design what we can achieve more with an ALTER SYSTEM command. Let's look at the problems: * remote users can lock themselves out of the server * interconnected GUC variables are complex to change * need a way to disable this feature Given the above, I am not sure I see a way forward for ALTER SYSTEM SET. One compromise that avoids the problems above would be to have a limited feature called ALTER LOG SET that only controls logging parameters, but I am not sure there is enough use-case there. This is not a zero-cost feature as we would be giving people _two_ ways to configure Postgres, and two ways to find a fix if it is broken, so we have to have a clear win to implement this. Also, if you have broken the system via ALTER SYSTEM SET, you might need to edit flat files to fix it, adding further complexity and limitations if someone only knows the SQL method of configuration. Given that, and the problems above, I reluctantly just don't see how this features moves us forward. There seemed to be agreement on having a config.d, though. -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
There seemed to be agreement on having a config.d, though. Yes. Also, the validate_conf_parameter() (or some similar name) Amit added in his patch sounds useful if an extension can use it (to check a GUC someone want to change, to check a configuration file, ...) -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
* Tom Lane (t...@sss.pgh.pa.us) wrote: =?iso-8859-1?q?C=E9dric_Villemain?= ced...@2ndquadrant.com writes: Maybe better to provide a contrib/ to modify config, then design what we can achieve more with an ALTER SYSTEM command. Hmm ... putting the UI into a contrib module would neatly solve the problem for people who don't want the functionality. They just don't install the module. Another thought here is that it seems like we've been discussing multiple scenarios that don't necessarily all have the same best solution. Maybe we should think in terms of multiple contrib modules. I'd certainly be happier with that- but it already exists, to some extent, in the form of the pgAdmin adminpack contrib module. Perhaps we should be working on improving what we've already got there rather than adding something completely new.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On Tue, Aug 6, 2013 at 10:58:52AM -0400, Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: =?iso-8859-1?q?C=E9dric_Villemain?= ced...@2ndquadrant.com writes: Maybe better to provide a contrib/ to modify config, then design what we can achieve more with an ALTER SYSTEM command. Hmm ... putting the UI into a contrib module would neatly solve the problem for people who don't want the functionality. They just don't install the module. Another thought here is that it seems like we've been discussing multiple scenarios that don't necessarily all have the same best solution. Maybe we should think in terms of multiple contrib modules. I'd certainly be happier with that- but it already exists, to some extent, in the form of the pgAdmin adminpack contrib module. Perhaps we should be working on improving what we've already got there rather than adding something completely new.. Yes, adminpack has mostly read/write file I/O operations --- it would need a higher-level API. -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On 08/06/2013 05:29 AM, Bruce Momjian wrote: Let's look at the problems: * remote users can lock themselves out of the server * interconnected GUC variables are complex to change * need a way to disable this feature Given the above, I am not sure I see a way forward for ALTER SYSTEM SET. One compromise that avoids the problems above would be to have a limited feature called ALTER LOG SET that only controls logging parameters, but I am not sure there is enough use-case there. This is not a zero-cost feature as we would be giving people _two_ ways to configure Postgres, and two ways to find a fix if it is broken, so we have to have a clear win to implement this. Also, if you have broken the system via ALTER SYSTEM SET, you might need to edit flat files to fix it, adding further complexity and limitations if someone only knows the SQL method of configuration. Given that, and the problems above, I reluctantly just don't see how this features moves us forward. Well said. I'd like to look at use cases, and let's see how ALTER SYSTEM SET addresses or doesn't address these use cases. I'd really like it if some other folks also posted use cases they know of. (1) Making is easier for GUIs to manage PostgreSQL settings. (2) Enabling DBAAS services to give users limited control over settings. (3) Making the initial developer experience better by not requiring hand-editing of configuration files to play with settings. (4) Making it easier to write automated testing scripts which test changes to settings. (5) Enabling new ways of writing Puppet/Chef/etc. scripts, which can check a setting before changing it. For the current patch, it fullfills these use cases as follows: (1) works, but is ALSO one of the critical problems with the feature. (2) does not work for this purpose. (3) works for this purpose. (4) works for this purpose. (5) works for this purpose, although it's unclear if it's actually wanted. Others? -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On Tue, Aug 6, 2013 at 10:33:20AM -0700, Josh Berkus wrote: On 08/06/2013 05:29 AM, Bruce Momjian wrote: Let's look at the problems: * remote users can lock themselves out of the server * interconnected GUC variables are complex to change * need a way to disable this feature Given the above, I am not sure I see a way forward for ALTER SYSTEM SET. One compromise that avoids the problems above would be to have a limited feature called ALTER LOG SET that only controls logging parameters, but I am not sure there is enough use-case there. This is not a zero-cost feature as we would be giving people _two_ ways to configure Postgres, and two ways to find a fix if it is broken, so we have to have a clear win to implement this. Also, if you have broken the system via ALTER SYSTEM SET, you might need to edit flat files to fix it, adding further complexity and limitations if someone only knows the SQL method of configuration. Given that, and the problems above, I reluctantly just don't see how this features moves us forward. Well said. I wish I had good news to say well. ;-) I'd like to look at use cases, and let's see how ALTER SYSTEM SET addresses or doesn't address these use cases. I'd really like it if some other folks also posted use cases they know of. (1) Making is easier for GUIs to manage PostgreSQL settings. One of the traps here is that while it makes it easier, it also could trap the user if they don't have the knowledge to fix a problem because would need to acquire the knowledge while they are trying to fix the problem, rather then while they are making the initial change. (2) Enabling DBAAS services to give users limited control over settings. Right. This could be accomplished by giving users a function API for certain features, and then marking the function as SECURITY DEFINER. However, I am unclear how to do this in a generic way. Once neat idea would be to have a lookup table define which setting the administrator wishes to allow for non-superusers. If adminpack has an API to change postgresql.conf, it would be easy to create a function with SECURITY DEFINER permissions that SET lookup-allowed values in postgresql.conf. -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Josh Berkus escribió: (2) Enabling DBAAS services to give users limited control over settings. (5) Enabling new ways of writing Puppet/Chef/etc. scripts, which can check a setting before changing it. Surely these two cases are better covered by conf.d. For (2), DBaaS providers could offer pre-cooked snippets that are dropped in conf.d at the click of a button. For (5) it should be obvious that conf.d is better than ALTER SYSTEM. Not clear on (4); I think conf.d would also solve that one. -- Álvaro Herrerahttp://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
Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On Tue, Aug 6, 2013 at 3:31 PM, Bruce Momjian br...@momjian.us wrote: I'd like to look at use cases, and let's see how ALTER SYSTEM SET addresses or doesn't address these use cases. I'd really like it if some other folks also posted use cases they know of. (1) Making is easier for GUIs to manage PostgreSQL settings. One of the traps here is that while it makes it easier, it also could trap the user if they don't have the knowledge to fix a problem because would need to acquire the knowledge while they are trying to fix the problem, rather then while they are making the initial change. I think the more serious problem here is not about knowledge acquisition, but access to problem-fixing means. As was said some posts ago, if a DBA has access to a superuser account but not to server configuration files, he can lock himself out for good. No amount of knowledge will avail him/her. That's bad. -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
All: (this thread concerns argument (F) whether or not admins need a way to disable ALTER SYSTEM SET) On 08/01/2013 04:03 PM, Dimitri Fontaine wrote: If only we could trigger some actions when a command is about to be executed, in a way that it's easy for the user to implement whatever policy he fancies… Oh, maybe I should finish preparing those patches for Event Triggers to be fully usable in 9.4 then ;) I don't see this as a solution at all. Mr. Sysadmin, we've given the DBAs a new tool which allows them to override your version-controlled database parameter settings. You can turn it off, though, by using this incredibly complicated, brand-new Event Trigger tool which requires writing lots of SQL code to make work. Per Stephen Frost's arguments, some system owners are going to be opposed to allowing ALTER SYSTEM SET at all because it can mess systems up and cause downtime. Yes, that's already true of ALTER ROLE and ALTER DATABASE, but ALTER SYSTEM SET expands this the ability of the DBA to change setting substantially. That's obviously its benefit, but it's also clearly a misfeature for some system owners. Also, to be blunt, most DBAs/DEVs don't *know* about ALTER ROLE/DATABASE. I don't think this is a small thing. I really think we'll get a LOT of blowback from sysadmins -- and maybe even refusals to upgrade -- if we add ALTER SYSTEM SET in 9.4.0 with no easy way to disable it. Having an easy way to disable ALTER SYSTEM SET is *also* a good way to get out of the whole situation of I set shared_buffers to 100GB using ALTER SYSTEM SET and now PostgreSQL won't start problem. As I've said before, I think the disable switch can be a follow-up patch to the main ALTER SYSTEM SET patch. -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On Mon, Aug 5, 2013 at 11:16:24AM -0700, Josh Berkus wrote: I don't see this as a solution at all. Mr. Sysadmin, we've given the DBAs a new tool which allows them to override your version-controlled database parameter settings. You can turn it off, though, by using this incredibly complicated, brand-new Event Trigger tool which requires writing lots of SQL code to make work. Per Stephen Frost's arguments, some system owners are going to be opposed to allowing ALTER SYSTEM SET at all because it can mess systems up and cause downtime. Yes, that's already true of ALTER ROLE and ALTER DATABASE, but ALTER SYSTEM SET expands this the ability of the DBA to change setting substantially. That's obviously its benefit, but it's also clearly a misfeature for some system owners. Also, to be blunt, most DBAs/DEVs don't *know* about ALTER ROLE/DATABASE. I don't think this is a small thing. I really think we'll get a LOT of blowback from sysadmins -- and maybe even refusals to upgrade -- if we -- Really? Is that a reasonable statement? add ALTER SYSTEM SET in 9.4.0 with no easy way to disable it. Having an easy way to disable ALTER SYSTEM SET is *also* a good way to get out of the whole situation of I set shared_buffers to 100GB using ALTER SYSTEM SET and now PostgreSQL won't start problem. As I've said before, I think the disable switch can be a follow-up patch to the main ALTER SYSTEM SET patch. You are mixing the behaviors of disabling the ALTER SYSTEM SET command with recognizing ALTER SYSTEM SET settings already made --- the later would be to fix a problem with the server not starting. However, frankly, these are flat files, so I don't see a problem with having the administrator modify the flat file. Would disabling the ALTER SYSTEM SET command also disable recognizing any ALTER SYSTEM SET commands already performed? Maybe that was already understood, but I missed that point. -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
* Bruce Momjian (br...@momjian.us) wrote: On Mon, Aug 5, 2013 at 11:16:24AM -0700, Josh Berkus wrote: I don't think this is a small thing. I really think we'll get a LOT of blowback from sysadmins -- and maybe even refusals to upgrade -- if we -- Really? Is that a reasonable statement? I don't believe Josh was saying that in jest.. I certainly believe that it could happen. As I've said before, I think the disable switch can be a follow-up patch to the main ALTER SYSTEM SET patch. Even if it's a different patch, it should go in at the same time, imv.. You are mixing the behaviors of disabling the ALTER SYSTEM SET command with recognizing ALTER SYSTEM SET settings already made --- the later would be to fix a problem with the server not starting. That's a good point. However, frankly, these are flat files, so I don't see a problem with having the administrator modify the flat file. Admins on Ubuntu or Debian or a host of their derivatives aren't going to be looking in $PGDATA for config files that they have to hand-modify to fix something the DBA did. When they eventually figure it out, they're going to be *very* unhappy. Would disabling the ALTER SYSTEM SET command also disable recognizing any ALTER SYSTEM SET commands already performed? Maybe that was already understood, but I missed that point. I don't have an answer to that one, though I do like the idea of a switch that says only read the settings from my postgresql.conf file. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On Mon, Aug 5, 2013 at 02:52:40PM -0400, Stephen Frost wrote: However, frankly, these are flat files, so I don't see a problem with having the administrator modify the flat file. Admins on Ubuntu or Debian or a host of their derivatives aren't going to be looking in $PGDATA for config files that they have to hand-modify to fix something the DBA did. When they eventually figure it out, they're going to be *very* unhappy. Well, can you assume that if you have a problem with one of your ALTER SYSTEM SET commands, that disabling _all_ of them is going to get you a running system? I question that, e.g. port. With postgresql.conf, you can modify the bad entry, but how would that happen with ALTER SYSTEM SET? -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Bruce Momjian br...@momjian.us writes: Well, can you assume that if you have a problem with one of your ALTER SYSTEM SET commands, that disabling _all_ of them is going to get you a running system? I question that, e.g. port. With postgresql.conf, you can modify the bad entry, but how would that happen with ALTER SYSTEM SET? I think we already have consensus that the settings will be in text files, so that system breakage can be handled by editing the files. What Josh seems to be concerned with in this thread is the question of whether we should support an installation *policy decision* not to allow ALTER SYSTEM SET. Not because a particular set of parameters is broken, but just because somebody is afraid the DBA might break things. TBH I'm not sure I buy that, at least not as long as ALTER SYSTEM is a superuser feature. There is nothing in Postgres that denies permissions to superusers, and this doesn't seem like a very good place to start. 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Tom Lane escribió: What Josh seems to be concerned with in this thread is the question of whether we should support an installation *policy decision* not to allow ALTER SYSTEM SET. Not because a particular set of parameters is broken, but just because somebody is afraid the DBA might break things. TBH I'm not sure I buy that, at least not as long as ALTER SYSTEM is a superuser feature. There is nothing in Postgres that denies permissions to superusers, and this doesn't seem like a very good place to start. Someone made an argument about this on IRC: GUI tool users are going to want to use ALTER SYSTEM through point-and-click, and if all we offer is superuser-level access to the feature, we're going to end up with a lot of people running with superuser privileges just so that they are able to tweak inconsequential settings. This seems dangerous. The other issue is that currently you can only edit a server's config if you are logged in to it. If we permit SQL-level access to that, and somebody who doesn't have access to edit the files blocks themselves out, there is no way for them to get a working system *at all*. -- Álvaro Herrerahttp://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
Re: [HACKERS] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Hi, I'm now completely lost in the current threads. Is there a single valid use case for the feature we're trying to design? Who is the target audience of this patch? Josh Berkus j...@agliodbs.com writes: I don't see this as a solution at all. Mr. Sysadmin, we've given the DBAs a new tool which allows them to override your version-controlled database parameter settings. You can turn it off, though, by using this incredibly complicated, brand-new Event Trigger tool which requires writing lots of SQL code to make work. Well, given what has been said already and very recently again by Tom, it's superuser only and installing a precedent wherein superuser is not allowed to use a feature looks like a dead-end. You would have to make a case that it's comparable to allow_system_table_mods. If you believe that revoking ALTER SYSTEM SET privileges to superusers isn't going to be accepted, I know of only two other paths to allow you to implementing your own policy, including per-GUC policy and non-superuser granting of ALTER SYSTEM SET in a controled fashion: - add per GUC GRANT/REVOKE capabilities to SETTINGs, - implement the same thing within an Event Trigger. The former has been talked about lots of time already in the past and I'm yet to see any kind of progress made about it despite plenty of user support for the feature, the latter requires a shared catalog for global object Event Triggers and maybe a flexible Extension that you can manage by just dropping a configuration file into the PostgreSQL conf.d. So when trying to be realistic the answer is incredibly complicated because it involves a stored procedure to implement the local policy and a command to enable the policy, really, I wonder who you're addressing there. Certainly not DBA, so that must be sysadmins, who would be better off without the feature in the first place if I'm understanding you. Again, what are we trying to achieve?! 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane escribió: What Josh seems to be concerned with in this thread is the question of whether we should support an installation *policy decision* not to allow ALTER SYSTEM SET. Not because a particular set of parameters is broken, but just because somebody is afraid the DBA might break things. TBH I'm not sure I buy that, at least not as long as ALTER SYSTEM is a superuser feature. There is nothing in Postgres that denies permissions to superusers, and this doesn't seem like a very good place to start. Someone made an argument about this on IRC: GUI tool users are going to want to use ALTER SYSTEM through point-and-click, and if all we offer is superuser-level access to the feature, we're going to end up with a lot of people running with superuser privileges just so that they are able to tweak inconsequential settings. This seems dangerous. Agreed, but what else are you going to do? You can't have random unprivileged users changing settings that affect other users, even if those settings are somehow safe. And what is more to the point in this thread, having an additional shutoff that prevents even superusers from doing it doesn't reduce the temptation for everyone to make themselves superuser all the time. The other issue is that currently you can only edit a server's config if you are logged in to it. If we permit SQL-level access to that, and somebody who doesn't have access to edit the files blocks themselves out, there is no way for them to get a working system *at all*. True. So, if they were smart enough to prevent themselves from changing any settings remotely, they have no feature. And if they weren't, having such a blocking capability didn't really help them either. These are both valid worries, but what design is going to make them better? Other than forgetting about ALTER SYSTEM entirely? 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On Mon, 5 Aug 2013 15:53:01 -0400 Alvaro Herrera alvhe...@2ndquadrant.com wrote: The other issue is that currently you can only edit a server's config if you are logged in to it. If we permit SQL-level access to that, and somebody who doesn't have access to edit the files blocks themselves out, there is no way for them to get a working system *at all*. This is not a valid point, at least for mebasically all OSs should disable any change to firewall if you are connected remotely cause you can block yourselfgiving power to users does not mean being their babysitterwe should smart enough to use the power we receive...if we are not smart...we should start thinking about other profession And excuse my English, I hope the point is clear... Best regards Rodrigo Gonzalez -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On 08/05/2013 10:18 PM, Dimitri Fontaine wrote: Hi, I'm now completely lost in the current threads. Is there a single valid use case for the feature we're trying to design? Who is the target audience of this patch? wonder about that myself... Josh Berkus j...@agliodbs.com writes: I don't see this as a solution at all. Mr. Sysadmin, we've given the DBAs a new tool which allows them to override your version-controlled database parameter settings. You can turn it off, though, by using this incredibly complicated, brand-new Event Trigger tool which requires writing lots of SQL code to make work. Well, given what has been said already and very recently again by Tom, it's superuser only and installing a precedent wherein superuser is not allowed to use a feature looks like a dead-end. You would have to make a case that it's comparable to allow_system_table_mods. If you believe that revoking ALTER SYSTEM SET privileges to superusers isn't going to be accepted, I know of only two other paths to allow you to implementing your own policy, including per-GUC policy and non-superuser granting of ALTER SYSTEM SET in a controled fashion: - add per GUC GRANT/REVOKE capabilities to SETTINGs, realistically I think this is what we want(fsvo) for this feature as a prerequisite, however that also will make it fairly complex to use for both humans and tools so not sure we would really gain anything... - implement the same thing within an Event Trigger. The former has been talked about lots of time already in the past and I'm yet to see any kind of progress made about it despite plenty of user support for the feature, the latter requires a shared catalog for global object Event Triggers and maybe a flexible Extension that you can manage by just dropping a configuration file into the PostgreSQL conf.d. So when trying to be realistic the answer is incredibly complicated because it involves a stored procedure to implement the local policy and a command to enable the policy, really, I wonder who you're addressing there. Certainly not DBA, so that must be sysadmins, who would be better off without the feature in the first place if I'm understanding you. Again, what are we trying to achieve?! no idea - wondering about that myself... Stefan -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On 08/05/2013 09:53 PM, Alvaro Herrera wrote: Tom Lane escribió: What Josh seems to be concerned with in this thread is the question of whether we should support an installation *policy decision* not to allow ALTER SYSTEM SET. Not because a particular set of parameters is broken, but just because somebody is afraid the DBA might break things. TBH I'm not sure I buy that, at least not as long as ALTER SYSTEM is a superuser feature. There is nothing in Postgres that denies permissions to superusers, and this doesn't seem like a very good place to start. Someone made an argument about this on IRC: GUI tool users are going to want to use ALTER SYSTEM through point-and-click, and if all we offer is superuser-level access to the feature, we're going to end up with a lot of people running with superuser privileges just so that they are able to tweak inconsequential settings. This seems dangerous. indeed it is The other issue is that currently you can only edit a server's config if you are logged in to it. If we permit SQL-level access to that, and somebody who doesn't have access to edit the files blocks themselves out, there is no way for them to get a working system *at all*. thinking more about that - is there _ANY_ prerequisite of an application that can be completely reconfigured over a remote access protocol and solved the reliability and security challenges of that to a reasonable degree? Stefan -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
Stefan, thinking more about that - is there _ANY_ prerequisite of an application that can be completely reconfigured over a remote access protocol and solved the reliability and security challenges of that to a reasonable degree? Good question! I also think that, given the issues raised in discussion of ALTER SYSTEM SET, we should poll a bit of the wider community. I'll put something up on my blog, unless someone can think of a better way. -- 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] Disabling ALTER SYSTEM SET WAS: Re: ALTER SYSTEM SET command to change postgresql.conf parameters
On Mon, Aug 5, 2013 at 03:53:01PM -0400, Alvaro Herrera wrote: The other issue is that currently you can only edit a server's config if you are logged in to it. If we permit SQL-level access to that, and somebody who doesn't have access to edit the files blocks themselves out, there is no way for them to get a working system *at all*. Well, if we want to give the administrator a way to disable honoring any previously-defined ALTER SYSTEM SET commands, how would they do that without OS access? By definition, they can't connect via SQL, so what would the API be? Also, even if they could do it remotely, if they previously set listen_addresses via ALTER SYSTEM SET, and we then disable all ALTER SYSTEM SET settings, they still can't access the system because by default Postgres will only listen on local sockets. In summary, the SQL interface to configuration parameters is a convenience, but I don't think it is ever going to be something that can replace full file system access --- that is not a limitation of the implemention of ALTER SYSTEM SET, but just something that is impossible. -- 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
