Re: ALTER SYSTEM for pg_hba.conf

Greetings,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Jan 4, 2017 at 3:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> >> My next thought is ALTER SYSTEM support for pg_hba.conf, especially
> >> since that would make it easier to do a formal test of Haribabu's
> >> pg_hba view patch by adding each of the options one by one and then
> >> juggling them.
> >
> > It's quite unclear from this spec what you have in mind to control the
> > entry order. Also, I'd personally be -1 on inventing a pile of new SQL
> > keywords for this. Why not do it with a function, instead? Or for extra
> > credit, finish the pg_hba view work first and then make it an updatable
> > view.
> >
> >> ....and we can then have a nice simple
> >> ALTER SYSTEM ENABLE REMOTE ACCESS FOR REPLICATION USING md5;
> >
> > I am minus a lot more than 1 on inventing a new SQL statement every time
> > somebody thinks of a new way in which they'd like to frob pg_hba.conf.
>
> Yeah. I don't think that the idea of having SQL syntax to manipulate
> pg_hba.conf is a terrible one, but it'd probably require some thought
> to figure out exactly how to do it nicely - i.e. easy-to-use and not
> too many new keywords.

Agreed.

> There's also the question of whether opening
> up the ability to do this sort of thing from the SQL level is a
> security hazard, but we've already gone fairly far down the path of
> assuming that there's not a tremendous amount of privilege separation
> between the operating system user account and the database superuser,
> so maybe the answer is that as things stand it's not expanding the
> vulnerability surface very much.

If we keep it to superusers then we aren't changing anything, from my
point of view at least. That does bring up the question of if it'd be
useful for a non-superuser to be able to control. I'm on the fence
about that at the moment. Generally speaking, it's useful for
non-superusers to be able to control access, but pg_hba is a bit special
as it also controls the auth method and I'm not sure that is really
something it makes sense for a non-superuser to hack around.

However, the other bits that pg_hba allows (controlling access based on
if it's an SSL connection, or based on the source IP) would be nice to
provide alongside the 'CONNECT' GRANT privilege instead of only being
able to do in pg_hba.

In short, I'd rather we look at ways to minimize the need for users to
interact with pg_hba.conf than make it easier to do.

> One thing I'm kind of happy about is that, as far as I can see, there
> hasn't been much backlash against the existing ALTER SYSTEM, either
> from a security point of view or a user-confusion point of view.

I've seen complaints about it and have seen people changing the
permissions to be root/root on the .auto.conf file to disallow 'regular'
superusers from doing ALTER SYSTEM. It's not exactly elegant but it's a
way to avoid the risk of someone messing with the system config without
going through the CM system.

> We
> (collectively) spent a lot of time worrying about that, and AFAICS it
> hasn't really been the case. Now, I am not sure how many people are
> using it vs. other methods of setting cluster-wide configuration
> parameters, and there have been a handful of bug reports, but
> basically nobody's come back and said that they had a terrible,
> horrible, no-good, very-bad day as a result of it, which was a concern
> at the time. So maybe the experience with a new variant would be
> similarly good.

I've not seen any reports of someone having a terrible day because of
it, yet.

Thanks!

Stephen