Thread: API for Managing pg_hba and postgresql.conf

Looking at the list history, I see this has been discussed in the  
past, but it has been long enough that perhaps it is time to revisit it.

It would appear from my own support queues, that one of the most  
prevalent issues with PostgreSQL installations is not a functional  
one, but an administrative one. For obvious reasons, the secure by  
default installation is the correct choice, but it presents a  
problem.  With the proliferation of PostgreSQL onto platforms where  
security isn't natural, there are hurdles that have to be dealt with.

What I'm seeing is a default installation protects the Data directory  
properly, but in so doing means that altering the configuration files,  
pg_hba.conf and postgresql.conf require database administrators, who  
should not necessarily have a level of rights to become superuser at  
the file system level to alter the mentioned files.

Rather than change the fundamental file layout or location, I would  
propose that we expose an API or Schema in the database to better  
allow manipulation of these configuration structure from within  
PostgreSQL.  This would allow a DBA to make changes to the  
configuration without the need to be a machine administrator, or even  
to run with escalated privilege at the OS level.

My concern over tackling this is that of security.

What would be the appropriate way to protect this API.

Should it be a collection of functions or s a schema?

Should it be part of the INFORMATION_SCHEMA?

Should it be an entirely different schema, say CONFIGURATION_SCHEMA?

Should the Schema or functions be restricted to a specific database  
(say postgres) rather than part of every database?

Since most changes would require a SIGHUP, should the server process  
itself be alter to allow for a dynamic restart from within the  
environment?

While I have opinions and have tinkered with the idea a bit, I'll be  
the first to admit that this is functionality that needs to be  
discussed and structure in a generally supportable way rather than a  
platform specific hack, so I'm looking for thoughts and opinions of an  
educated variety.

A huge portion of the motivation here is to allow for easy to  
graphical administration interfaces, making the system more  
approachable, and to make remote administration of these files less  
cumbersome.

On Thu, 14 Aug 2008, Andrew Satori wrote:

> What I'm seeing is a default installation protects the Data directory 
> properly, but in so doing means that altering the configuration files, 
> pg_hba.conf and postgresql.conf require database administrators, who should 
> not necessarily have a level of rights to become superuser at the file system 
> level to alter the mentioned files.

This suggests you're doing something wrong, and it's no wonder you think 
this is a serious management problem (I consider it a minor one).


In a typical installation, the data directory that contains the database 
and configuring files will be owned by a database user, typically 
"postgres".  DBAs should be able to get to all of those by switching to 
that user, without need any true superuser rights.  If that isn't the case 
in your environment, I'm curious how you ended up there, and it's no 
wonder that's a serious management problem for you.  I consider this area 
only a minor annoyance.  Normally the only thing I see that you can't do 
as the postgres user is directly execute the database start/stop scripts 
that root runs ("service postgresql start" on RedHat for example), but 
since all that can be done via pg_ctl instead this is an easily scriptable 
issue to work around.

> A huge portion of the motivation here is to allow for easy to graphical 
> administration interfaces, making the system more approachable, and to make 
> remote administration of these files less cumbersome.

There's already a bunch of work in that area going on already.  Check the 
archives for the "GUCS Overhaul" and "Parsing of pg_hba.conf and 
authentication inconsistencies" threads for two long-running discussions 
of the fundamental server-side issues needed to pull that off.  You 
touched on the main one here:

> Since most changes would require a SIGHUP, should the server process 
> itself be alter to allow for a dynamic restart from within the 
> environment?

Many of the problems with what you're thinking of revolve around the 
unfortunate answers that pop up when you ask "what if I try and put bad 
information in postgresql.conf/pg_hba.conf?"; see those threads I 
mentioned for some context on that.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD