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 <bruce@momjian.us> http://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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* 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

On Fri, Feb 14, 2014 at 3:32 PM, Bruce Momjian <bruce@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/

* 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. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Feb 14, 2014 at 12:36 PM, Stephen Frost <sfrost@snowman.net> 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.

I would like to participate the GSoC this year as a student.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers