Thread: 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.consulting@comcast.net
p: 312.241.7800

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  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

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 Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera <alvherre@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.consulting@comcast.net
p: 312.241.7800

Bruce,

* Bruce Momjian (bruce@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

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 Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On Fri, Feb 14, 2014 at 03:28:23AM -0500, Stephen Frost wrote:
> Bruce,
> 
> * Bruce Momjian (bruce@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  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

* Bruce Momjian (bruce@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

* Bruce Momjian (bruce@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

On Fri, Feb 14, 2014 at 09:34:59AM -0500, Stephen Frost wrote:
> * Bruce Momjian (bruce@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  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

<div dir="ltr"><div class="gmail_extra"><br />On Fri, Feb 14, 2014 at 12:36 PM, Stephen Frost <<a
href="mailto:sfrost@snowman.net">sfrost@snowman.net</a>>wrote:<br />><br />> * Bruce Momjian (<a
href="mailto:bruce@momjian.us">bruce@momjian.us</a>)wrote:<br /> > > In an ideal world we would have a tool where
youcould plug in a<br />> > username, database, IP address, and test pg_hba.conf file and it would<br />> >
reportwhat line is matched.<br />><br />> That's not a bad idea, but we don't expose the logic that figures
that<br/> > out today..  It would, perhaps, not be horrible to duplicate it, but<br />> then we'd need to make
surethat we update both places if it ever<br />> changes (not that it's changed much in oh-so-many-years).
 Perhaps<br/> > another candidate to be a GSoC project.<br />><br /><br /></div><div class="gmail_extra">I would
liketo participate the GSoC this year as a student.<br /><br /></div><div class="gmail_extra">Regards,<br /></div><div
class="gmail_extra"><br/>--<br />Fabrízio de Royes Mello<br />Consultoria/Coaching PostgreSQL<br />>> Timbira: <a
href="http://www.timbira.com.br">http://www.timbira.com.br</a><br/>>> Blog sobre TI: <a
href="http://fabriziomello.blogspot.com">http://fabriziomello.blogspot.com</a><br/> >> Perfil Linkedin: <a
href="http://br.linkedin.com/in/fabriziomello">http://br.linkedin.com/in/fabriziomello</a><br/>>> Twitter: <a
href="http://twitter.com/fabriziomello">http://twitter.com/fabriziomello</a></div></div>

Stephen Frost <sfrost@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

On 2014-02-14 10:19:30 -0500, Tom Lane wrote:
> Stephen Frost <sfrost@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

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@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

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

* Andres Freund (andres@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

Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@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

On 2014-02-14 11:10:48 -0500, Tom Lane wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@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

* Andres Freund (andres@2ndquadrant.com) wrote:
> On 2014-02-14 11:10:48 -0500, Tom Lane wrote:
> > Stephen Frost <sfrost@snowman.net> writes:
> > > * Tom Lane (tgl@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

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Stephen Frost <sfrost@snowman.net> writes:
>
>> * Tom Lane (tgl@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.sievers@comcast.net
p: 312.241.7800

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   <kleptog@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

On 2/14/14, 8:36 AM, Stephen Frost wrote:
> * Bruce Momjian (bruce@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
functionalityto the wire protocol to support the case of "hypothetically, if I were to attempt a connection that looks
likethis, what would happen?"
 
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

On 2/14/14, 1:06 PM, Jeff Janes wrote:
> On Fri, Feb 14, 2014 at 6:33 AM, Bruce Momjian <bruce@momjian.us <mailto:bruce@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
thecontents of pg_hba.conf directly into postgresql.conf.  So instead of giving a filename to hba_file, optionally give
amulti-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,
Ioften 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                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

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
magicallyslurp that in", that's far less useful and a much bigger foot-gun. (It also wouldn't provide any value for
whatJerry (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                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

* Jim Nasby (jim@nasby.net) wrote:
> On 2/14/14, 8:36 AM, Stephen Frost wrote:
> >* Bruce Momjian (bruce@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
functionalityto the wire protocol to support the case of "hypothetically, if I were to attempt a connection that looks
likethis, 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

* Jim Nasby (jim@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
magicallyslurp that in", that's far less useful and a much bigger foot-gun. (It also wouldn't provide any value for
whatJerry (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

On 2/14/14, 1:06 PM, Jeff Janes wrote:

On Fri, Feb 14, 2014 at 6:33 AM, Bruce Momjian <bruce@momjian.us <mailto:bruce@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?