Thread: [HACKERS] ALTER SYSTEM for pg_hba.conf
My next thought is ALTER SYSTEM support for pg_hba.conf, especially
since that would make it easier to do a formal test of Haribabu's
pg_hba view patch by adding each of the options one by one and then
juggling them.
ALTER SYSTEM
ENABLE [LOCAL | REMOTE] ACCESS
FOR
[DATABASE <name> | REPLICATION ]
[USER <name> ]
[NETWORK {ADDRESS [NETMASK] | hostname]
[options]
USING <authentication-method>
[WITH [[NO] ENCRYPTION] [PRIORITY n]]
....and we can then have a nice simple
ALTER SYSTEM ENABLE REMOTE ACCESS FOR REPLICATION USING md5;
into which we can tie any required parameters (i.e. one command to
enable replication)
Note that the keyword HOST would be replaced by REMOTE and SSL by
ENCRYPTION to make it clearer.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Simon Riggs <simon@2ndquadrant.com> writes:
> My next thought is ALTER SYSTEM support for pg_hba.conf, especially
> since that would make it easier to do a formal test of Haribabu's
> pg_hba view patch by adding each of the options one by one and then
> juggling them.
It's quite unclear from this spec what you have in mind to control the
entry order. Also, I'd personally be -1 on inventing a pile of new SQL
keywords for this. Why not do it with a function, instead? Or for extra
credit, finish the pg_hba view work first and then make it an updatable
view.
> ....and we can then have a nice simple
> ALTER SYSTEM ENABLE REMOTE ACCESS FOR REPLICATION USING md5;
I am minus a lot more than 1 on inventing a new SQL statement every time
somebody thinks of a new way in which they'd like to frob pg_hba.conf.
regards, tom lane
On 4 January 2017 at 20:30, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Simon Riggs <simon@2ndquadrant.com> writes: >> My next thought is ALTER SYSTEM support for pg_hba.conf, especially >> since that would make it easier to do a formal test of Haribabu's >> pg_hba view patch by adding each of the options one by one and then >> juggling them. > > It's quite unclear from this spec what you have in mind to control the > entry order. Also, I'd personally be -1 on inventing a pile of new SQL > keywords for this. Why not do it with a function, instead? Or for extra > credit, finish the pg_hba view work first and then make it an updatable > view. > >> ....and we can then have a nice simple >> ALTER SYSTEM ENABLE REMOTE ACCESS FOR REPLICATION USING md5; > > I am minus a lot more than 1 on inventing a new SQL statement every time > somebody thinks of a new way in which they'd like to frob pg_hba.conf. Oh believe me, I'm with you on that thought! ...but I had thought the ALTER SYSTEM discussion was over now and SQL was the agreed way, now. I was chasing the "lets make replication simple as possible" direction but this really doesn't add much apart from syntax. Definitely have better things to do though, so happy to leave it there. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Wed, Jan 4, 2017 at 3:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Simon Riggs <simon@2ndquadrant.com> writes: >> My next thought is ALTER SYSTEM support for pg_hba.conf, especially >> since that would make it easier to do a formal test of Haribabu's >> pg_hba view patch by adding each of the options one by one and then >> juggling them. > > It's quite unclear from this spec what you have in mind to control the > entry order. Also, I'd personally be -1 on inventing a pile of new SQL > keywords for this. Why not do it with a function, instead? Or for extra > credit, finish the pg_hba view work first and then make it an updatable > view. > >> ....and we can then have a nice simple >> ALTER SYSTEM ENABLE REMOTE ACCESS FOR REPLICATION USING md5; > > I am minus a lot more than 1 on inventing a new SQL statement every time > somebody thinks of a new way in which they'd like to frob pg_hba.conf. Yeah. I don't think that the idea of having SQL syntax to manipulate pg_hba.conf is a terrible one, but it'd probably require some thought to figure out exactly how to do it nicely - i.e. easy-to-use and not too many new keywords. There's also the question of whether opening up the ability to do this sort of thing from the SQL level is a security hazard, but we've already gone fairly far down the path of assuming that there's not a tremendous amount of privilege separation between the operating system user account and the database superuser, so maybe the answer is that as things stand it's not expanding the vulnerability surface very much. One thing I'm kind of happy about is that, as far as I can see, there hasn't been much backlash against the existing ALTER SYSTEM, either from a security point of view or a user-confusion point of view. We (collectively) spent a lot of time worrying about that, and AFAICS it hasn't really been the case. Now, I am not sure how many people are using it vs. other methods of setting cluster-wide configuration parameters, and there have been a handful of bug reports, but basically nobody's come back and said that they had a terrible, horrible, no-good, very-bad day as a result of it, which was a concern at the time. So maybe the experience with a new variant would be similarly good. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Greetings, * Robert Haas (robertmhaas@gmail.com) wrote: > On Wed, Jan 4, 2017 at 3:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Simon Riggs <simon@2ndquadrant.com> writes: > >> My next thought is ALTER SYSTEM support for pg_hba.conf, especially > >> since that would make it easier to do a formal test of Haribabu's > >> pg_hba view patch by adding each of the options one by one and then > >> juggling them. > > > > It's quite unclear from this spec what you have in mind to control the > > entry order. Also, I'd personally be -1 on inventing a pile of new SQL > > keywords for this. Why not do it with a function, instead? Or for extra > > credit, finish the pg_hba view work first and then make it an updatable > > view. > > > >> ....and we can then have a nice simple > >> ALTER SYSTEM ENABLE REMOTE ACCESS FOR REPLICATION USING md5; > > > > I am minus a lot more than 1 on inventing a new SQL statement every time > > somebody thinks of a new way in which they'd like to frob pg_hba.conf. > > Yeah. I don't think that the idea of having SQL syntax to manipulate > pg_hba.conf is a terrible one, but it'd probably require some thought > to figure out exactly how to do it nicely - i.e. easy-to-use and not > too many new keywords. Agreed. > There's also the question of whether opening > up the ability to do this sort of thing from the SQL level is a > security hazard, but we've already gone fairly far down the path of > assuming that there's not a tremendous amount of privilege separation > between the operating system user account and the database superuser, > so maybe the answer is that as things stand it's not expanding the > vulnerability surface very much. If we keep it to superusers then we aren't changing anything, from my point of view at least. That does bring up the question of if it'd be useful for a non-superuser to be able to control. I'm on the fence about that at the moment. Generally speaking, it's useful for non-superusers to be able to control access, but pg_hba is a bit special as it also controls the auth method and I'm not sure that is really something it makes sense for a non-superuser to hack around. However, the other bits that pg_hba allows (controlling access based on if it's an SSL connection, or based on the source IP) would be nice to provide alongside the 'CONNECT' GRANT privilege instead of only being able to do in pg_hba. In short, I'd rather we look at ways to minimize the need for users to interact with pg_hba.conf than make it easier to do. > One thing I'm kind of happy about is that, as far as I can see, there > hasn't been much backlash against the existing ALTER SYSTEM, either > from a security point of view or a user-confusion point of view. I've seen complaints about it and have seen people changing the permissions to be root/root on the .auto.conf file to disallow 'regular' superusers from doing ALTER SYSTEM. It's not exactly elegant but it's a way to avoid the risk of someone messing with the system config without going through the CM system. > We > (collectively) spent a lot of time worrying about that, and AFAICS it > hasn't really been the case. Now, I am not sure how many people are > using it vs. other methods of setting cluster-wide configuration > parameters, and there have been a handful of bug reports, but > basically nobody's come back and said that they had a terrible, > horrible, no-good, very-bad day as a result of it, which was a concern > at the time. So maybe the experience with a new variant would be > similarly good. I've not seen any reports of someone having a terrible day because of it, yet. Thanks! Stephen
On Thu, Jan 5, 2017 at 11:56 AM, Stephen Frost <sfrost@snowman.net> wrote: > Greetings, > > If we keep it to superusers then we aren't changing anything, from my > point of view at least. That does bring up the question of if it'd be > useful for a non-superuser to be able to control. I'm on the fence > about that at the moment. Generally speaking, it's useful for > non-superusers to be able to control access, but pg_hba is a bit special > as it also controls the auth method and I'm not sure that is really > something it makes sense for a non-superuser to hack around. > > However, the other bits that pg_hba allows (controlling access based on > if it's an SSL connection, or based on the source IP) would be nice to > provide alongside the 'CONNECT' GRANT privilege instead of only being > able to do in pg_hba. > > In short, I'd rather we look at ways to minimize the need for users to > interact with pg_hba.conf than make it easier to do. That's an interesting point. >> One thing I'm kind of happy about is that, as far as I can see, there >> hasn't been much backlash against the existing ALTER SYSTEM, either >> from a security point of view or a user-confusion point of view. > > I've seen complaints about it and have seen people changing the > permissions to be root/root on the .auto.conf file to disallow 'regular' > superusers from doing ALTER SYSTEM. It's not exactly elegant but it's a > way to avoid the risk of someone messing with the system config without > going through the CM system. Hmm, OK. They're not bothered by ALTER DATABASE the_one_everybody_uses? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
* Robert Haas (robertmhaas@gmail.com) wrote: > On Thu, Jan 5, 2017 at 11:56 AM, Stephen Frost <sfrost@snowman.net> wrote: > >> One thing I'm kind of happy about is that, as far as I can see, there > >> hasn't been much backlash against the existing ALTER SYSTEM, either > >> from a security point of view or a user-confusion point of view. > > > > I've seen complaints about it and have seen people changing the > > permissions to be root/root on the .auto.conf file to disallow 'regular' > > superusers from doing ALTER SYSTEM. It's not exactly elegant but it's a > > way to avoid the risk of someone messing with the system config without > > going through the CM system. > > Hmm, OK. They're not bothered by ALTER DATABASE the_one_everybody_uses? Generally speaking, an ALTER DATABASE is unlikely to make the cluster fail to start. To be clear, I've only seen 1 or 2 cases and I'm not sure if, in those cases, they even fully understood how much can be changed through ALTER DATABASE or ALTER ROLE. My goal in those cases (and others where I come across installations with a lot of superusers) is typically to try and educate them as to just how close a superuser is to the unix user and recommend that they reconsider how they handle access privileges in the system (in particular, to try and get them to not have so many superusers and instead use other ways to give people access to what they need). Of course, that tends to lead into things like "well, how do I make sure that user X has read rights on every table, always" or "how do I give someone the ability to terminate runaway queries that another user started." We've made progress there, but there's more to do still. Thanks! Stephen
On Thu, Jan 5, 2017 at 12:28 PM, Stephen Frost <sfrost@snowman.net> wrote: > Generally speaking, an ALTER DATABASE is unlikely to make the cluster > fail to start. To be clear, I've only seen 1 or 2 cases and I'm not > sure if, in those cases, they even fully understood how much can be > changed through ALTER DATABASE or ALTER ROLE. OK. > My goal in those cases (and others where I come across installations > with a lot of superusers) is typically to try and educate them as to > just how close a superuser is to the unix user and recommend that they > reconsider how they handle access privileges in the system (in > particular, to try and get them to not have so many superusers and > instead use other ways to give people access to what they need). Makes sense. > Of course, that tends to lead into things like "well, how do I make sure > that user X has read rights on every table, always" or "how do I give > someone the ability to terminate runaway queries that another user > started." We've made progress there, but there's more to do still. I agree! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 01/05/2017 08:27 AM, Robert Haas wrote: > There's also the question of whether opening up the ability to do > this sort of thing from the SQL level is a security hazard, It unquestionably is. > but we've already gone fairly far down the path of assuming that > there's not a tremendous amount of privilege separation between the > operating system user account and the database superuser, I think this is a very bad assumption. > so maybe the answer is that as things stand it's not expanding the > vulnerability surface very much. Perhaps as things currently stand this is true. > One thing I'm kind of happy about is that, as far as I can see, there > hasn't been much backlash against the existing ALTER SYSTEM, either > from a security point of view or a user-confusion point of view. Possibly only because there are workarounds possible using hooks and extension code. Personally I think we should have an official way to disable ALTER SYSTEM and I would like the same for pg_hba.conf related functionality. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
On 04-01-2017 17:30, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: >> My next thought is ALTER SYSTEM support for pg_hba.conf, especially >> since that would make it easier to do a formal test of Haribabu's >> pg_hba view patch by adding each of the options one by one and then >> juggling them. > > It's quite unclear from this spec what you have in mind to control the > entry order. Also, I'd personally be -1 on inventing a pile of new SQL > keywords for this. Why not do it with a function, instead? Or for extra > credit, finish the pg_hba view work first and then make it an updatable > view. > Even if you made the view updatable, you need a field to control the order. It has the line_number but an specific field would be desirable (someone could add a blank or comment line between querying the view and typing the update command). Also, in-place update a .conf file was something vetoed in the ALTER SYSTEM design and I think it was a clever idea. If we decided to mix automated and hand editing, a rewrite on every change is an easier path. Unlike ALTER SYSTEM, I'm afraid we can't invent a pg_hba.auto.conf because (i) order matters and (ii) it stops processing when a rule matches. In this case, we'll limit the feature usefulness. If we don't invent new fields in pg_hba.conf, a function could be a solution instead of a SQL syntax. However, a new field could break compatibility (unless we stick with a default value that could not be a good idea in the security pov). -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte24x7 e Treinamento
On 1/5/17 11:56 AM, Stephen Frost wrote: > I've seen complaints about it and have seen people changing the > permissions to be root/root on the .auto.conf file to disallow 'regular' > superusers from doing ALTER SYSTEM. It's not exactly elegant but it's a > way to avoid the risk of someone messing with the system config without > going through the CM system. Good idea! I have also had that issue. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services