Re: HBA files w/include support?

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