Why pg_hba not in table?

Jason C. Leach wrote:

hi,

Why not put pg_hba.conf in a pg table? Seems like it would be much
easier to work with. After all, if we can keep users in the db
tables, why not this?

Go for it! It is actually on the TODO.

Joshua D. Drake

jason.leach@gmail.com ("Jason C. Leach") writes:

Why not put pg_hba.conf in a pg table? Seems like it would be much
easier to work with. After all, if we can keep users in the db
tables, why not this?

... Because it represents information that needs to be accessed
*before* a connection to the database is established.

This is the configuration that determines whether or not a DB
connection is permitted. If we store the information in a table, then
the connection has to be accepted in order to determine if the
connection should be accepted.

As things stand, pg_hba.conf will reject many of the cases without
needing to burden the database engine with another connection.

If connections are required, then:

a) There are presumably some new race conditions for vulnerabilities
that come available;

b) A new DOS attack is introduced.
--
select 'cbbrowne' || '@' || 'acm.org';
http://cbbrowne.com/info/unix.html
:FATAL ERROR -- ILLEGAL ERROR

Chris Browne <cbbrowne@acm.org> writes:

jason.leach@gmail.com ("Jason C. Leach") writes:

Why not put pg_hba.conf in a pg table? Seems like it would be much
easier to work with. After all, if we can keep users in the db
tables, why not this?

... Because it represents information that needs to be accessed
*before* a connection to the database is established.

Right; however, we've already solved the problem of maintaining a
flat-file representation of a cluster-wide table: both pg_database and
pg_shadow/pg_authid are handled that way. I see no reason in principle
why pg_hba.conf couldn't be replaced by a flat-file dump of a database
table.

The interesting part of the problem is designing a tabular
representation that works well and doesn't give up any necessary
features. The existing definition of pg_hba.conf is pretty
non-orthogonal --- in particular its dependence on entry ordering is
nasty if that's to be translated into a table. It'd be better to come
up with a model that doesn't depend on entry order, if we can do that
without sacrificing too much flexibility. And there's too much
variability in what the column entries are --- the notion of tossing a
list or a file inclusion reference into a column may work OK as text,
but it would suck to do that in a table. (But file insertion could
possibly be dispensed with, on the grounds that you could do the
equivalent with pure SQL manipulations.) We've also speculated about
adding a CONNECT privilege to databases, which could cover some of the
ground now occupied by pg_hba.conf.

If we go this route, I'd like to see pg_ident.conf also replaced by
a table, or maybe folded into the same table.

One other small point is the bootstrapping problem: if you can't get
into the database to modify the config table, you've got trouble.

Bottom line is that we need a well-thought-out design proposal first.
The coding would probably be pretty straightforward once we had a
design that would work.

regards, tom lane

On Tue, Feb 07, 2006 at 03:24:01PM -0500, Tom Lane wrote:

One other small point is the bootstrapping problem: if you can't get
into the database to modify the config table, you've got trouble.

Hence MySQL's --skip-grant-tables option; if you've locked yourself
out then you have to disable security entirely to get back in and
fix the problem. With a configuration that you can edit from outside
the database, you can usually get back in without having to punch
as big a hole.

--
Michael Fuhr

On Tue, 2006-02-07 at 15:37, Michael Fuhr wrote:

On Tue, Feb 07, 2006 at 03:24:01PM -0500, Tom Lane wrote:

One other small point is the bootstrapping problem: if you can't get
into the database to modify the config table, you've got trouble.

Hence MySQL's --skip-grant-tables option; if you've locked yourself
out then you have to disable security entirely to get back in and
fix the problem. With a configuration that you can edit from outside
the database, you can usually get back in without having to punch
as big a hole.

And you can change pg_hba.conf on the fly, so you don't have to restart
a 24/7 database because you locked the superuser out.

Scott Marlowe <smarlowe@g2switchworks.com> writes:

On Tue, 2006-02-07 at 15:37, Michael Fuhr wrote:

On Tue, Feb 07, 2006 at 03:24:01PM -0500, Tom Lane wrote:

One other small point is the bootstrapping problem: if you can't get
into the database to modify the config table, you've got trouble.

Hence MySQL's --skip-grant-tables option; if you've locked yourself
out then you have to disable security entirely to get back in and
fix the problem. With a configuration that you can edit from outside
the database, you can usually get back in without having to punch
as big a hole.

And you can change pg_hba.conf on the fly, so you don't have to restart
a 24/7 database because you locked the superuser out.

If your back were against the wall, you could probably hand-edit the
flat-file version of the permission file enough to let yourself in
without shutting down the postmaster. It might not be as user-friendly
to edit as the current pg_hba.conf, but it'd still be flat ASCII I expect.

Also, we already have various scenarios in which dropping down to a
standalone backend is the only recovery path --- deleting the last
superuser role is a good example. So I'm not sure we should insist
that the connection permission file/table has to be any more robust
against superuser stupidity.

The case that I am most worried about is the new-installation scenario:
what will the startup default be, and how hard will be it be to fix it
if you don't like it? This is a big problem for first-timers already,
and we mustn't make it worse. But perhaps there's an opportunity here
to make it better.

regards, tom lane

And you can change pg_hba.conf on the fly, so you don't have to restart
a 24/7 database because you locked the superuser out.

If your back were against the wall, you could probably hand-edit the
flat-file version of the permission file enough to let yourself in
without shutting down the postmaster. It might not be as user-friendly

to edit as the current pg_hba.conf, but it'd still be flat ASCII I expect.

Hi,

AFAIC, I've written scripts that alter the file "pg_hba.conf" on the fly,
while running PostgreSQL, and also *before* starting PostgreSQL !

The goal was to create a "restricted" mode, called via :
service postgresql start-restricted

For example, if the database server is off, and maintenance is needed
*before* any normal (non-superuser) connections, we can start the server
directly, with perfect security settings...

So, the actual pg_hba.conf file is ideal !
If we would have to start the database in order to reconfigure it to
prevent normal connections, a normal user could take advantage of this
to connect during this process !!

Don't loose flexibility and security for some "elegant" evolutions !

Best Regards,
Philippe Ferreira.