Re: [HACKERS] HBA files w/include support?
On 2/14/14, 8:36 AM, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: In an ideal world we would have a tool where you could plug in a username, database, IP address, and test pg_hba.conf file and it would report what line is matched. That's not a bad idea, but we don't expose the logic that figures that out today.. It would, perhaps, not be horrible to duplicate it, but then we'd need to make sure that we update both places if it ever changes (not that it's changed much in oh-so-many-years). Perhaps another candidate to be a GSoC project. Stupid question... is there a reason we couldn't use the same code for both? BTW, I'm not sure that SQL would be the appropriate API for this testing; but presumably it wouldn't be hard to add functionality to the wire protocol to support the case of hypothetically, if I were to attempt a connection that looks like this, what would happen? -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] HBA files w/include support?
On 2/14/14, 1:06 PM, Jeff Janes wrote: On Fri, Feb 14, 2014 at 6:33 AM, Bruce Momjian br...@momjian.us mailto:br...@momjian.us wrote: On Fri, Feb 14, 2014 at 03:28:23AM -0500, Stephen Frost wrote: Bruce, Having @include and directory.d-style capabilities for pg_hba.conf *and* pg_ident.conf would make managing larger environments much better. There has been some talk about providing those capabilities via tables in the catalog, but I'm not aware of anyone working on it and it'd certainly be quite a bit more work than adding include/dir.d options. Do we want a TODO for this? If we are assembling a wish-list, I've often wanted the opposite of an include. I want the ability to encapsulate the contents of pg_hba.conf directly into postgresql.conf. So instead of giving a filename to hba_file, optionally give a multi-lined string with some kind of here-doc like mechanism, or something like that. When I set up a forked dev environment and then eventually want to compare the diverged dev setup back to production, I often forget to compare the pg_hba.conf file. So is this just to avoid having to diff 2 files instead of one? On it's face, I'm not seeing the value of putting pg_hba.conf inside postgresql.conf... what am I missing? -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] HBA files w/include support?
On 2/14/14, 10:14 AM, Andres Freund wrote: I was asking for use-cases so we could figure out what's the right thing;-) The argument about wanting to assemble a pg_hba file from separately managed configuration pieces seems to have some merit, but the weak spot there is how do you define the search order? Or are you planning to just cross your fingers and hope it doesn't matter too much? The usual solution is to prepend a numeric prefix guaranteeing the search order. 00 is sysadmin stuff, 10 replication, 20 database specific or somesuch. I think most admins using automated tools to manage bigger configuration files by using some .d config directory already know how to deal with that problem. Would the inclusion of the entire directory be done via a single #include (or whatever syntax) directive in pg_hba.conf? I think that's probably OK. But if we're talking about something like hey, if there's a pg_hba.d directory then magically slurp that in, that's far less useful and a much bigger foot-gun. (It also wouldn't provide any value for what Jerry (the op) needs). To summarize, here's what I've seen on this discussion: - People seem to generally be in favor of the idea of includes, though it's not completely clear if people want specific include file X at this point in the ruleset or something more nebulous. - It would be useful to have a mechanism for testing a pg_hba.conf file. - It would also be useful for denied connections to log the actual line/file that denied the connection. - This would be a good GSoC project. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] HBA files w/include support?
* Jim Nasby (j...@nasby.net) wrote: On 2/14/14, 8:36 AM, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: In an ideal world we would have a tool where you could plug in a username, database, IP address, and test pg_hba.conf file and it would report what line is matched. That's not a bad idea, but we don't expose the logic that figures that out today.. It would, perhaps, not be horrible to duplicate it, but then we'd need to make sure that we update both places if it ever changes (not that it's changed much in oh-so-many-years). Perhaps another candidate to be a GSoC project. Stupid question... is there a reason we couldn't use the same code for both? It'd just be a matter of shifting things around to make that work. I'm not against it, but this code is hardly of general or common use. BTW, I'm not sure that SQL would be the appropriate API for this testing; but presumably it wouldn't be hard to add functionality to the wire protocol to support the case of hypothetically, if I were to attempt a connection that looks like this, what would happen? Well, we have that, and it's just do it and you'll see. Making that easier to determine would have to be done post-authentication anyway, lest we make it easier for would-be attackers, and at that point I'm not sure that there's much benefit in having something in the protocol for this rather than just a handy SQL function, which people who care about these things are probably going to be pretty familiar with anyway.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] HBA files w/include support?
* Jim Nasby (j...@nasby.net) wrote: Would the inclusion of the entire directory be done via a single #include (or whatever syntax) directive in pg_hba.conf? Not sure we've even figured that out yet, but probably. I think that's probably OK. But if we're talking about something like hey, if there's a pg_hba.d directory then magically slurp that in, that's far less useful and a much bigger foot-gun. (It also wouldn't provide any value for what Jerry (the op) needs). I agree that it's best to have it be explicit, though the packagers may go ahead and set things up such that a pg_hba.d directory exists by default on their distribution. To summarize, here's what I've seen on this discussion: - People seem to generally be in favor of the idea of includes, though it's not completely clear if people want specific include file X at this point in the ruleset or something more nebulous. My thought would be to support both individual files and directories, where files in a directory are included in C/POSIX lexical order. - It would be useful to have a mechanism for testing a pg_hba.conf file. Agreed. - It would also be useful for denied connections to log the actual line/file that denied the connection. Agreed- in the postmaster log, of course. We would not change the response to the client. - This would be a good GSoC project. That's my 2c on it at least. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] HBA files w/include support?
On Sunday, February 16, 2014, Jim Nasby j...@nasby.net wrote: On 2/14/14, 1:06 PM, Jeff Janes wrote: On Fri, Feb 14, 2014 at 6:33 AM, Bruce Momjian br...@momjian.us mailto: br...@momjian.us wrote: On Fri, Feb 14, 2014 at 03:28:23AM -0500, Stephen Frost wrote: Bruce, Having @include and directory.d-style capabilities for pg_hba.conf *and* pg_ident.conf would make managing larger environments much better. There has been some talk about providing those capabilities via tables in the catalog, but I'm not aware of anyone working on it and it'd certainly be quite a bit more work than adding include/dir.d options. Do we want a TODO for this? If we are assembling a wish-list, I've often wanted the opposite of an include. I want the ability to encapsulate the contents of pg_hba.conf directly into postgresql.conf. So instead of giving a filename to hba_file, optionally give a multi-lined string with some kind of here-doc like mechanism, or something like that. When I set up a forked dev environment and then eventually want to compare the diverged dev setup back to production, I often forget to compare the pg_hba.conf file. So is this just to avoid having to diff 2 files instead of one? It is not so much doing two diffs, as remembering to do two diffs, that I would like to avoid. pg_hba.conf is very easy to forget about. I guess if I spent more time worrying about it, then that is the one I would remember and postgresql.conf is the one I'd forget. Cheers, Jeff
Re: [HACKERS] HBA files w/include support?
On Fri, Feb 14, 2014 at 11:10:48AM -0500, Tom Lane wrote: The argument about wanting to assemble a pg_hba file from separately managed configuration pieces seems to have some merit, but the weak spot there is how do you define the search order? Or are you planning to just cross your fingers and hope it doesn't matter too much? Well, in my case since the only auth method used is md5 the order really doesn't matter. Besides the point that each combination of dbname and username only appears once. But for a general use feature I can imagine it would be a concern. This is not an important feature for me though: the config file is generated by puppet with a bunch of loops and an include directory would not really reduce the amount of work. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] HBA files w/include support?
Bruce, * Bruce Momjian (br...@momjian.us) wrote: On Thu, Feb 13, 2014 at 08:24:27PM -0600, Jerry Sievers wrote: I'm aware of how a pg_hba.conf file can refer to other files for including @lists of users, etc. But there is currently no support for being able to pull in entire file segments as can be done for postgresql.conf via the include directive. In the environment that I'm managing, we are using a makefile to stick together a common header with a custom section for any of several clusters and may extend this further to permit additional includes for hba rules common to groupings of clusters. Anyway, please advise. I don't recall hearing anything like this discussed. Has been proposed, discussed and voted down? Or never mentioned? I have never heard of anyone request this. I've brought it up on various threads before, including both the ALTER SYSTEM thread and the postgresql.conf 'includes' thread, though I don't feel like going back and hunting down the specific emails right now. Having @include and directory.d-style capabilities for pg_hba.conf *and* pg_ident.conf would make managing larger environments much better. There has been some talk about providing those capabilities via tables in the catalog, but I'm not aware of anyone working on it and it'd certainly be quite a bit more work than adding include/dir.d options. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] HBA files w/include support?
Jerry Sievers wrote: The other thing that comes to mind, is that as opposed to postgresql.conf and the include scenario there... one can do show all or query from pg_stat_activity just to see what setting they ended up with. I'm not aware of any way to probe what hba rules are loaded at runtime and thus, debugging hba config changes not really possible. Well, getting includes in postgresql.conf in an acceptable shape required lots more work than just implementing the include directive; one of the very first requirements was that the file path and line number that each setting got its value from was available in pg_settings. I'm pretty sure that a patch that only added includes without equivalent functionality would not get very far. -- Á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] HBA files w/include support?
On Thu, Feb 13, 2014 at 11:28:45PM -0600, Jerry Sievers wrote: One issue with this is that pg_hba.conf is order sensitive, which could become a trap for the unwary if includes are used carelessly. Indeed. The other thing that comes to mind, is that as opposed to postgresql.conf and the include scenario there... one can do show all or query from pg_stat_activity just to see what setting they ended up with. I'm not aware of any way to probe what hba rules are loaded at runtime and thus, debugging hba config changes not really possible. In an ideal world we would have a tool where you could plug in a username, database, IP address, and test pg_hba.conf file and it would report what line is matched. I presume that a simple scenario involving just 1 level of includes not too difficult to grok but nested includes sure might be a foot gun unless there was a way to dump the resulting configs somehow. Thus pasting hba files together externally a more reliable approach. You certainly would not have a visual idea of what line is matched _first_. We have the same problem with postgresql.conf includes, though the last match wins there --- not sure if that makes it any easier. I think one concern is that pg_hba.conf is more security-oriented than postgresql.conf. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] HBA files w/include support?
On Fri, Feb 14, 2014 at 03:28:23AM -0500, Stephen Frost wrote: Bruce, * Bruce Momjian (br...@momjian.us) wrote: On Thu, Feb 13, 2014 at 08:24:27PM -0600, Jerry Sievers wrote: I'm aware of how a pg_hba.conf file can refer to other files for including @lists of users, etc. But there is currently no support for being able to pull in entire file segments as can be done for postgresql.conf via the include directive. In the environment that I'm managing, we are using a makefile to stick together a common header with a custom section for any of several clusters and may extend this further to permit additional includes for hba rules common to groupings of clusters. Anyway, please advise. I don't recall hearing anything like this discussed. Has been proposed, discussed and voted down? Or never mentioned? I have never heard of anyone request this. I've brought it up on various threads before, including both the ALTER SYSTEM thread and the postgresql.conf 'includes' thread, though I don't feel like going back and hunting down the specific emails right now. Having @include and directory.d-style capabilities for pg_hba.conf *and* pg_ident.conf would make managing larger environments much better. There has been some talk about providing those capabilities via tables in the catalog, but I'm not aware of anyone working on it and it'd certainly be quite a bit more work than adding include/dir.d options. Do we want a TODO for this? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] HBA files w/include support?
* Bruce Momjian (br...@momjian.us) wrote: On Fri, Feb 14, 2014 at 03:28:23AM -0500, Stephen Frost wrote: Having @include and directory.d-style capabilities for pg_hba.conf *and* pg_ident.conf would make managing larger environments much better. There has been some talk about providing those capabilities via tables in the catalog, but I'm not aware of anyone working on it and it'd certainly be quite a bit more work than adding include/dir.d options. Do we want a TODO for this? Sure, and it should be a candidate for GSoC, imv... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] HBA files w/include support?
On Fri, Feb 14, 2014 at 3:32 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Feb 13, 2014 at 11:28:45PM -0600, Jerry Sievers wrote: One issue with this is that pg_hba.conf is order sensitive, which could become a trap for the unwary if includes are used carelessly. Indeed. The other thing that comes to mind, is that as opposed to postgresql.conf and the include scenario there... one can do show all or query from pg_stat_activity just to see what setting they ended up with. I'm not aware of any way to probe what hba rules are loaded at runtime and thus, debugging hba config changes not really possible. In an ideal world we would have a tool where you could plug in a username, database, IP address, and test pg_hba.conf file and it would report what line is matched. I almost wrote a function you could call to do that a while back. I never finished it though :) It's not all that hard to do, but requires some minor refactoring of how the HBA code works. What would also be useful would be to be able to use such a function/tool against a different file than the current HBA one, to verify *before* you reload... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [HACKERS] HBA files w/include support?
* Bruce Momjian (br...@momjian.us) wrote: In an ideal world we would have a tool where you could plug in a username, database, IP address, and test pg_hba.conf file and it would report what line is matched. That's not a bad idea, but we don't expose the logic that figures that out today.. It would, perhaps, not be horrible to duplicate it, but then we'd need to make sure that we update both places if it ever changes (not that it's changed much in oh-so-many-years). Perhaps another candidate to be a GSoC project. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] HBA files w/include support?
On Fri, Feb 14, 2014 at 09:34:59AM -0500, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: On Fri, Feb 14, 2014 at 03:28:23AM -0500, Stephen Frost wrote: Having @include and directory.d-style capabilities for pg_hba.conf *and* pg_ident.conf would make managing larger environments much better. There has been some talk about providing those capabilities via tables in the catalog, but I'm not aware of anyone working on it and it'd certainly be quite a bit more work than adding include/dir.d options. Do we want a TODO for this? Sure, and it should be a candidate for GSoC, imv... OK, added. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] HBA files w/include support?
On Fri, Feb 14, 2014 at 12:36 PM, Stephen Frost sfr...@snowman.net wrote: * Bruce Momjian (br...@momjian.us) wrote: In an ideal world we would have a tool where you could plug in a username, database, IP address, and test pg_hba.conf file and it would report what line is matched. That's not a bad idea, but we don't expose the logic that figures that out today.. It would, perhaps, not be horrible to duplicate it, but then we'd need to make sure that we update both places if it ever changes (not that it's changed much in oh-so-many-years). Perhaps another candidate to be a GSoC project. I would like to participate the GSoC this year as a student. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] HBA files w/include support?
Stephen Frost sfr...@snowman.net writes: Having @include and directory.d-style capabilities for pg_hba.conf *and* pg_ident.conf would make managing larger environments much better. I'm a little suspicious of this, mainly because pg_hba searching is necessarily linear (and none too cheap per-entry). I think anyone who tries to use a set of entries large enough to really need multiple files is going to have pain. We already have various methods for making one pg_hba entry do the work of many; for instance, IP-subnet entries, wildcards, and role references. And you can use database CONNECT privilege grants as another substitute for fine-grained pg_hba entries. I'd be interested to see a real use-case where those things aren't an adequate substitute for a pg_hba rule set that's too large to fit conveniently in one file. Maybe we could identify another pg_hba abstraction technique we need to support. In short: I suspect this approach may be fixing the wrong thing. 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] HBA files w/include support?
On 2014-02-14 10:19:30 -0500, Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: Having @include and directory.d-style capabilities for pg_hba.conf *and* pg_ident.conf would make managing larger environments much better. I'm a little suspicious of this, mainly because pg_hba searching is necessarily linear (and none too cheap per-entry). I think anyone who tries to use a set of entries large enough to really need multiple files is going to have pain. I don't think big hba files should be the primary motivator for a feature like this. What I have seen is that people want a couple entries that grant local access to maintenance scripts and root/postgres. Additionally they want to have a *few* for every application that runs on the same cluster and another additional couple of entries allowing access to a limited number of replicas. It's not too hard to script this, simply have a pg_bha.d directory whose contents are concatenated in an alphabetic order resulting in a combined file. But that's clearly inferior to builtin support for that because the error reporting obviously won't be as good and it's not possible to nicely hook this into reloads and such. I wonder if the linear search could be improved a bit by separating database specific entries from database independent ones as the setups with large number of entries mostly had lots of database specific rules. It would probably be nontrivial to guarantee the sequential lookup order, but it should be possible. I'd be interested to see a real use-case where those things aren't an adequate substitute for a pg_hba rule set that's too large to fit conveniently in one file. Maybe we could identify another pg_hba abstraction technique we need to support. That additionally very well might be. The biggest thing I have seen is a) the inability to restrict access based on outside group membership when using ldap, b) the inability to grant membership in groups from outside of pg. Greetings, Andres Freund -- 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] HBA files w/include support?
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: Having @include and directory.d-style capabilities for pg_hba.conf *and* pg_ident.conf would make managing larger environments much better. I'm a little suspicious of this, mainly because pg_hba searching is necessarily linear (and none too cheap per-entry). I think anyone who tries to use a set of entries large enough to really need multiple files is going to have pain. It would depend on the connection rate- some of these environments have low connection rate but quite a few users (eg: corporate database server). As expressed up-thread, and in my own experience, people are already doing this by building up the files themselves, so I don't agree that performance would be so bad that this isn't worth it. Not everyone is using PG to back their website. Improving performance would always be good too, or perhaps providing another mechanism which performs better when there are more entries. We already have various methods for making one pg_hba entry do the work of many; for instance, IP-subnet entries, wildcards, and role references. And you can use database CONNECT privilege grants as another substitute for fine-grained pg_hba entries. Yes, but those entries all have to agree on the exact authentication method, including any mappings or other options which are passed through pg_hba.conf. Also, CONNECT is only true/false, it doesn't have any ability to consider source-IP, SSL, etc. I'd be interested to see a real use-case where those things aren't an adequate substitute for a pg_hba rule set that's too large to fit conveniently in one file. Maybe we could identify another pg_hba abstraction technique we need to support. Being able to 'fit' inside of one file is wholly in the eye of the beholder and isn't terribly relevant either. Larger installations will be using puppet, chef, or one of the other configuration management systems and will want to use even small/independent files rather than having to write code which fools around with the one file approach. The files I have in /etc/cron.d/ aren't particularly large, but it's really nice to be able to independently manage and deploy them without templating them. Also, all of the above ignores the pg_ident side of the house, which is even worse as you need an entry for every user, period, if you're using client-side SSL certificates or Kerberos/GSSAPI-based authentication with full princ names. In short: I suspect this approach may be fixing the wrong thing. I'm curious what you're thinking would be the right thing to fix here? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] HBA files w/include support?
On 2014-02-14 11:03:19 -0500, Stephen Frost wrote: Also, all of the above ignores the pg_ident side of the house, which is even worse as you need an entry for every user, period, if you're using client-side SSL certificates or Kerberos/GSSAPI-based authentication with full princ names. Well, there's regexes for mappings, that can often enough take care of most of that? Greetings, Andres Freund -- Andres Freund 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
Re: [HACKERS] HBA files w/include support?
* Andres Freund (and...@2ndquadrant.com) wrote: On 2014-02-14 11:03:19 -0500, Stephen Frost wrote: Also, all of the above ignores the pg_ident side of the house, which is even worse as you need an entry for every user, period, if you're using client-side SSL certificates or Kerberos/GSSAPI-based authentication with full princ names. Well, there's regexes for mappings, that can often enough take care of most of that? In some cases, yes, but certainly not all. Apologies for over-stating the case, but I came from an environment where the Kerberos princs were 'm##', while the database users were all first-initial || last-name. Also, the CN in an SSL certificate isn't likely to be what you want for a username either, and a regexp isn't likely to help that either. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] HBA files w/include support?
Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: In short: I suspect this approach may be fixing the wrong thing. I'm curious what you're thinking would be the right thing to fix here? I was asking for use-cases so we could figure out what's the right thing ;-) The argument about wanting to assemble a pg_hba file from separately managed configuration pieces seems to have some merit, but the weak spot there is how do you define the search order? Or are you planning to just cross your fingers and hope it doesn't matter too much? 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] HBA files w/include support?
On 2014-02-14 11:10:48 -0500, Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: In short: I suspect this approach may be fixing the wrong thing. I'm curious what you're thinking would be the right thing to fix here? I was asking for use-cases so we could figure out what's the right thing ;-) The argument about wanting to assemble a pg_hba file from separately managed configuration pieces seems to have some merit, but the weak spot there is how do you define the search order? Or are you planning to just cross your fingers and hope it doesn't matter too much? The usual solution is to prepend a numeric prefix guaranteeing the search order. 00 is sysadmin stuff, 10 replication, 20 database specific or somesuch. I think most admins using automated tools to manage bigger configuration files by using some .d config directory already know how to deal with that problem. Greetings, Andres Freund -- Andres Freund 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
Re: [HACKERS] HBA files w/include support?
* Andres Freund (and...@2ndquadrant.com) wrote: On 2014-02-14 11:10:48 -0500, Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: In short: I suspect this approach may be fixing the wrong thing. I'm curious what you're thinking would be the right thing to fix here? I was asking for use-cases so we could figure out what's the right thing ;-) The specific use-cases that I've seen are primairly around managing the sets of users who are allowed to connect from specific subnets. As for how the files might be independently managed, as Andres pointed out, all systems would get a 'default' set which includes access for the local 'postgres' unix user, and then certain sets of systems would get appropriate combinations of: system-specific admin users (from anywhere) normal users for that environment (from the desktop network) application users for app servers (from the app servers) ETL users for batch systems (from the ETL servers) sales users for the *replica* (whose CONNECT privs can't be different..) etc.. Would it be possible to have those all in one pg_hba file? Sure, in most cases, and you'd trust that the accounts simply wouldn't exist or that they wouldn't have CONNECT privs on the database, but this isn't how I run my systems (entries in pg_hba for users that don't exist?) and I doubt I'm alone in that. Being able to enforce based on source-IP is *very* nice and is only available in pg_hba today. Also, as noted above, if you're running a replica that should have different access rights (in particular, more 'open') then you can't depend on CONNECT privs or account authentication (username/password differences) to control access since that's all in the catalog and therefore must be identical to the primary. The argument about wanting to assemble a pg_hba file from separately managed configuration pieces seems to have some merit, but the weak spot there is how do you define the search order? Or are you planning to just cross your fingers and hope it doesn't matter too much? The usual solution is to prepend a numeric prefix guaranteeing the search order. 00 is sysadmin stuff, 10 replication, 20 database specific or somesuch. I think most admins using automated tools to manage bigger configuration files by using some .d config directory already know how to deal with that problem. Indeed, and run-parts codified it as 'lexical sort order (according to the C/POSIX locale)', under at least Debian and Ubuntu systems. I'm pretty confident that most admins are going to be familiar with handling this issue as it's exactly how /etc/cron.daily and anything else called through run-parts works. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] HBA files w/include support?
Tom Lane t...@sss.pgh.pa.us writes: Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: In short: I suspect this approach may be fixing the wrong thing. I'm curious what you're thinking would be the right thing to fix here? I was asking for use-cases so we could figure out what's the right thing ;-) No doubt this environment I took over managing, of 90 clusters ranging from tiny to ~3.5TB, some with hundreds of user accounts... could stand some massive rejiggering in regards to users/rols and such to go towards doing it the right way. That said, trying to roll up the hba files some with over 300 entries and lots of cases of high duplication among clusters belonging to somne subset, would be daunting and perhaps invasive. I realize that gathering up those duplicates into a file common to whatever group and then having that pulled in as an include is going to result in some reordering of the rules since they are not in a totally predictable order presently And my Jr. DBAs are still handling requests daily to add more hba rules quite often to more than a single machine but still along the same groupings mentioned. Even without retrofitting a good cleanup here, being able to include a global section at top of all files and at least one group specific file next then followed by legacy entries and/or items specific to a single cluster, might save a lot of work. Thus my idea of using a make file for this and then inspiring the question about includes. Great just getting the ball rolling and I respect whatever the concensus opinion that emerges. Thx The argument about wanting to assemble a pg_hba file from separately managed configuration pieces seems to have some merit, but the weak spot there is how do you define the search order? Or are you planning to just cross your fingers and hope it doesn't matter too much? regards, tom lane -- Jerry Sievers e: jerry.siev...@comcast.net p: 312.241.7800 -- 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] HBA files w/include support?
On Fri, Feb 14, 2014 at 6:33 AM, Bruce Momjian br...@momjian.us wrote: On Fri, Feb 14, 2014 at 03:28:23AM -0500, Stephen Frost wrote: Bruce, Having @include and directory.d-style capabilities for pg_hba.conf *and* pg_ident.conf would make managing larger environments much better. There has been some talk about providing those capabilities via tables in the catalog, but I'm not aware of anyone working on it and it'd certainly be quite a bit more work than adding include/dir.d options. Do we want a TODO for this? If we are assembling a wish-list, I've often wanted the opposite of an include. I want the ability to encapsulate the contents of pg_hba.conf directly into postgresql.conf. So instead of giving a filename to hba_file, optionally give a multi-lined string with some kind of here-doc like mechanism, or something like that. When I set up a forked dev environment and then eventually want to compare the diverged dev setup back to production, I often forget to compare the pg_hba.conf file. Cheers, Jeff
[HACKERS] HBA files w/include support?
Greetings Hackers. I'm aware of how a pg_hba.conf file can refer to other files for including @lists of users, etc. But there is currently no support for being able to pull in entire file segments as can be done for postgresql.conf via the include directive. In the environment that I'm managing, we are using a makefile to stick together a common header with a custom section for any of several clusters and may extend this further to permit additional includes for hba rules common to groupings of clusters. Anyway, please advise. I don't recall hearing anything like this discussed. Has been proposed, discussed and voted down? Or never mentioned? Thx -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] HBA files w/include support?
On Thu, Feb 13, 2014 at 08:24:27PM -0600, Jerry Sievers wrote: Greetings Hackers. I'm aware of how a pg_hba.conf file can refer to other files for including @lists of users, etc. But there is currently no support for being able to pull in entire file segments as can be done for postgresql.conf via the include directive. In the environment that I'm managing, we are using a makefile to stick together a common header with a custom section for any of several clusters and may extend this further to permit additional includes for hba rules common to groupings of clusters. Anyway, please advise. I don't recall hearing anything like this discussed. Has been proposed, discussed and voted down? Or never mentioned? I have never heard of anyone request this. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] HBA files w/include support?
Bruce Momjian wrote: On Thu, Feb 13, 2014 at 08:24:27PM -0600, Jerry Sievers wrote: Greetings Hackers. I'm aware of how a pg_hba.conf file can refer to other files for including @lists of users, etc. But there is currently no support for being able to pull in entire file segments as can be done for postgresql.conf via the include directive. I have never heard of anyone request this. On the contrary, I have vague memories that this has been discussed and agreed upon; we have just never implemented it. One issue with this is that pg_hba.conf is order sensitive, which could become a trap for the unwary if includes are used carelessly. -- Á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] HBA files w/include support?
Alvaro Herrera alvhe...@2ndquadrant.com writes: Bruce Momjian wrote: On Thu, Feb 13, 2014 at 08:24:27PM -0600, Jerry Sievers wrote: Greetings Hackers. I'm aware of how a pg_hba.conf file can refer to other files for including @lists of users, etc. But there is currently no support for being able to pull in entire file segments as can be done for postgresql.conf via the include directive. I have never heard of anyone request this. On the contrary, I have vague memories that this has been discussed and agreed upon; we have just never implemented it. Interesting and it'd surprise me if no one has ever wanted the feature. One issue with this is that pg_hba.conf is order sensitive, which could become a trap for the unwary if includes are used carelessly. Indeed. The other thing that comes to mind, is that as opposed to postgresql.conf and the include scenario there... one can do show all or query from pg_stat_activity just to see what setting they ended up with. I'm not aware of any way to probe what hba rules are loaded at runtime and thus, debugging hba config changes not really possible. I presume that a simple scenario involving just 1 level of includes not too difficult to grok but nested includes sure might be a foot gun unless there was a way to dump the resulting configs somehow. Thus pasting hba files together externally a more reliable approach. Thanks -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
