* Robert Haas (robertmh...@gmail.com) wrote: > On Thu, Aug 29, 2013 at 8:07 PM, Stephen Frost <sfr...@snowman.net> wrote: > > I'm not talking about malicious DBAs but rather a generally > > knowledgable DBA who changed shared_buffers up too high and then leaves on > > vacation, while the OPs guys need to do a database restart for whatever > > reason and then discover it doesn't start. > > /me looks at Stephen incredulously. > > In the first place, modifying postgresql.conf and not immediately > restarting the server to test your changes is probably the single most > basic DBA error imaginable.
You're not modifying postgresql.conf with ALTER SYSTEM, now are you?
Admins are going to realize the need to restart or at least reload the
service after updating such, but a DBA who has focused mainly on
normalization, query optimization, etc, isn't going to have the same
experience that a sysadmin has.
A DBA updating a GUC, something they are likely to do frequently
day-in and day-out, isn't necessairly going to consider that it's a
reboot-needing change. Indeed, it's not unreasonable to consider that
we may, some day, actually be able to change or increase
shared_buffers on the fly (or error in the trying); I'd think your
work with the dynamic backends would actually make that likely to
happen sooner rather than later.
> I have a hard time viewing such a person
> as generally knowledgeable. I hope the DBA in question doesn't have
> access to the switches, because he's probably the sort of guy who
> reassigns switch ports and doesn't type "wr m" afterwards.
I'd consider the DBAs who I work with as generally knowledgable, and
thankfully also cautious enough to talk to me before making changes, but
they have superuser access (they need to be able to do database
'releases' to our production environments and those scripts need to
change table ownership and do other things which can be annoying to
maintain the permissions for) and certainly understand changing GUCs
(mostly things like work_mem).
> In the second place, the same guy can do the same thing today. He
> just has to use "vi". In fact, I think this is a pretty common
> failure mode in poorly-controlled environments where too many people
> have access to the configuration files. Now maybe you're going to
> tell me that the ops guys can't modify the configuration file because
> they only have SQL-level access, but then how are they managing to
> restart the database? They need to be able to run pg_ctl *as the
> postgres user* to do that, and if they have shell access to that
> account, all bets are off.
You seem to be confused here between the DBA/data team and the OPs or
Infrastructure team. I'm making a clear distinction between them and
feel quite comfortable with it because it's the environment that I live
in today. My job, in fact, is exactly to straddle that line and work
with both.
In the above example, the DBA hasn't got root on the box and can't
simply go change or modify postgresql.conf, not even with "vi", and is
true for most of the DBAs on my team, even though they have superuser
access in PG.
> Sure, you can construct a scenario where this matters. The ops guys
> have "sudo postgres pg_ctl" access but adminpack isn't installed and
> they have no other way to modify the configuration file. But that's
> just bizarre. And if that's really the environment you have, then you
> can install a loadable module that grabs ProcessUtility_hook and uses
> it to forbid ALTER SYSTEM on that machine. Hell, we can ship such a
> thing in contrib. Problem solved. But it's surely too obscure a
> combination of circumstances to justify disabling this by default.
It's not the OPs guy that I'm worried about using ALTER SYSTEM- I don't
expect them to have any clue about it or care about it, except where it
can be used to modify things under /etc which they, rightfully, consider
their domain.
Thanks,
Stephen
signature.asc
Description: Digital signature
