Re: control pg_hba.conf via SQL

[Chris Browne <cbbrowne(at)acm(dot)org>]

andrew(at)dunslane(dot)net (Andrew Dunstan) writes:
> We don't have the luxury of being able just to throw out old stuff
> because we think it might be neater to do it another way.  The current
> rules for HBA are order dependent. The issue raised as I understood it
> was not to invent a new scheme but to be able to manage it from inside
> a postgres session.

If the need to support "legacy usage" mandates something like Svenne
Krap's suggestion of a control flag inside pg_hba.conf, or something
otherwise akin to Robert Treat's suggestions, then I think this *is*
designing something new/neater.

I think it would take a fair bit of work (and kludging of design) to
build something to slavishly emulate pg_hba.conf; it seems to me that
it is a much better thing to have an inside-the-database HBA scheme be
based on what is a good design inside-the-database.

> Of course, if we go for a new scheme that is not order dependent,
> then inventing a reasonable SQL syntax to support it becomes a heck
> of a lot easier. Something along the lines of GRANT/REVOKE CONNECT
> ... should do the trick.

Sure.  This would come as something of a 2 level attack on the problem:

 1.  Find a decent representation for the data;

 2.  Find a decent way to tell the system about the data...

> Maybe we could do something like this: if there is a pg_hba.conf
> file present, then use it as now and ignore the access rights table
> - if someone does GRANT/REVOKE CONNECT while under pg_hba.conf then
> process it but issue a warning. Maybe there could also be an initdb
> switch that gave users a choice.

initdb is a terrible choice for that; that means you have to
re-initialize the database to change the option.

I think a better approach is to control this in postgresql.conf...

An option like...

  host_based_authentication_file = "/etc/postgresql/pg_hba.conf"
  host_based_authentication = "internal"  [looks at table pg_catalog.pg_hba, let's say]
   # options here are "internal", "file", "file, internal", "internal, file"

That way, if a problem arises that is locking the administrator out,
it can be resolved by a "pg_ctl reload".
-- 
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www3.sympatico.ca/cbbrowne/lisp.html
"When  I was a  boy of  fourteen, my  father was  so ignorant  I could
hardly  stand to  have  the  old man  around.  But when  I  got to  be
twenty-one, I  was astonished at how  much the old man  had learned in
seven years." -- Mark Twain