Re: control pg_hba.conf via SQL

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