Re: ALTER SYSTEM vs symlink

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> Well, mumble --- the subtext I thought I was hearing from Stephen was
> >> that he'd not give his DBAs write access on postgresql.conf either.
> >> But yes, pushing people away from ALTER SYSTEM and towards manual editing
> >> of postgresql.conf would be a foolish way of "improving safety".
>
> > This is all very environment specific.  Changes to postgresql.conf, in
> > many environments, go through a serious of tests before being deployed
> > by a CM system.  How do we accomplish the same kind of tests before
> > deploying a change with ALTER SYSTEM?  We provide no mechanism to do
> > that today.
>
> Sure, so if you have such a process, you tell your DBAs not to use ALTER
> SYSTEM.  End of problem --- or if it isn't end of problem, you have HR
> issues that the database cannot fix for you.

It's not that simple though, as you point out- ALTER SYSTEM for *some*
operations is just fine, but it isn't for other changes.  That's one of
the difficulties with it.

Further, there's a level of cooperation required between the
postgresql.conf and the system, at a level which, in my mind anyway,
falls under the purview of the system adminsitration team instead of the
DBA team.  In larger organizations, those can be quite distinct sets.
If the DBA team is changing values via ALTER SYSTEM then, again, you can
run into problems when the sysadmin team or the CM system makes a
change.

Consider cases such as listen_addresses or, more generally, startup-time
options.  I brought all of this up on that thread and pointed out that
there are a bunch of postgresql.conf configuration options where system
level changes have to be made at the same time, which is why they make
sense to be put under a CM system which controls both the
postgresql.conf and the system configuration (what the IP address of the
system is, for example).

> The core point here is that if you're handing people superuser and
> expecting that they can't possibly circumvent any training-wheel-type
> restrictions you then put on that, you're wrong.  In the end you'd
> better trust that your DBAs know the process they're supposed to follow
> and follow it.

I don't view this as being about circumvention or training-wheel-type
restrictions.

> It may be that, at some point in the future, we'll have this sliced and
> diced fine enough that it's safe to allow some part of ALTER SYSTEM
> functionality to be accessible to people you don't want to give full
> superuser to.  But there's no such thing as "partial superuser", and
> personally I believe that it would be a tremendous waste of time to
> try to build such a feature.

I certainly look forward to having more fine grained control, to the
point where I'd like to be able to run a system reasonably without an
active superuser login.  Having superusers logging into production
running databases is extremely dangerous.  What I have seen happening,
in multiple organizations, is a move to proxy everything going to the
database through some other system which attempts to vet and verify that
the action is acceptable (this also happens to offer up much better
auditing than what we have today).  I feel confident that we can provide
a better solution than those proxy-based approaches.

Thanks!

Stephen