Re: ALTER SYSTEM for pg_hba.conf

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

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

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company