Hi all,

i would like to work on control setting pg_hba.conf via SQL....( cf TODO
List ), and i would know if the pg_hda.conf must be replaced by pg_hba
table ?

Emmanuel BERTHOULE

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

I'd like to expand this idea with this TODO item. If this is already
on the TODO, please disregard. I'm thinking something like the
following:

Enable the alteration and persistence of postgresql.conf and
pg_hba.conf configuration parameters via SQL.


On 3/29/06, BERTHOULE Emmanuel <pgdev@manberth.homeip.net> wrote:
> Hi all,
>
> i would like to work on control setting pg_hba.conf via SQL....( cf TODO
> List ), and i would know if the pg_hda.conf must be replaced by pg_hba
> table ?
>
> Emmanuel BERTHOULE
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>


--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

ISTM that the first requirement is for a sane API that will handle the
fact that HBA lines are ordered. Persistence in itself shouldn't be a
big problem - we already do that with some shared tables, iirc.

so we might have some functions like:

insert_hba_rule(at_position int, connection_type text, username text,
dbname text, cidr_host text, method text)
move_hba_rule(from_position int, to_position int)
delete_hba_rule(at_position int)

Inventing new SQL syntax might make things a bit tougher.

cheers

andrew


Jonah H. Harris wrote:
> I'd like to expand this idea with this TODO item. If this is already
> on the TODO, please disregard. I'm thinking something like the
> following:
>
> Enable the alteration and persistence of postgresql.conf and
> pg_hba.conf configuration parameters via SQL.
>
>
> On 3/29/06, BERTHOULE Emmanuel <pgdev@manberth.homeip.net> wrote:
>
>> Hi all,
>>
>> i would like to work on control setting pg_hba.conf via SQL....( cf TODO
>> List ), and i would know if the pg_hda.conf must be replaced by pg_hba
>> table ?
>>
>>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 3/29/06, Andrew Dunstan <andrew@dunslane.net> wrote:
> ISTM that the first requirement is for a sane API that will handle the
> fact that HBA lines are ordered. Persistence in itself shouldn't be a
> big problem - we already do that with some shared tables, iirc.

I agree.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Andrew Dunstan <andrew@dunslane.net> writes:
> ISTM that the first requirement is for a sane API that will handle the
> fact that HBA lines are ordered. Persistence in itself shouldn't be a
> big problem - we already do that with some shared tables, iirc.

I'm a bit suspicious of proposals that we move either hba or conf into
SQL tables --- one of the main reasons why they are flat files is so
you can still edit them after you've hosed them to the point that the
database won't start or won't let you in. If you don't have a non-kluge
solution to the DBA-mistake-recovery scenario, this is not going to be
an improvement.

Pushing postgresql.conf into a SQL table will also destroy all the work
that was done recently to allow config sharing across multiple
installations (eg the recent commit to support "include" goes out the
window again). If we no longer care about that, why not?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Wednesday 29 March 2006 17:04, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
> > ISTM that the first requirement is for a sane API that will handle the
> > fact that HBA lines are ordered. Persistence in itself shouldn't be a
> > big problem - we already do that with some shared tables, iirc.
>
> I'm a bit suspicious of proposals that we move either hba or conf into
> SQL tables --- one of the main reasons why they are flat files is so
> you can still edit them after you've hosed them to the point that the
> database won't start or won't let you in. If you don't have a non-kluge
> solution to the DBA-mistake-recovery scenario, this is not going to be
> an improvement.
>

I've often thought that a GUC in postgresql.conf could control whether to use the hba file or an hba table. Most likely you would need to restart the db to toggle control, but if your at the point where you've locked yourself out thisdoesn't seem onerous. If pushing postgresql.conf into the db would negate this plan, we could either allow a command line flag to override the conf/hba behavior, or force postgresql to use files if started in single operator mode. In any case, I don't think this restriction is insurmountable.

> Pushing postgresql.conf into a SQL table will also destroy all the work
> that was done recently to allow config sharing across multiple
> installations (eg the recent commit to support "include" goes out the
> window again). If we no longer care about that, why not?
>

Honestly I never cared much about that, and I run several machines that contain 3+ versions of the db on them. Certainly not as much as I would like to enhance remote administration between machines.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>
>
>>ISTM that the first requirement is for a sane API that will handle the
>>fact that HBA lines are ordered. Persistence in itself shouldn't be a
>>big problem - we already do that with some shared tables, iirc.
>>
>>
>
>I'm a bit suspicious of proposals that we move either hba or conf into
>SQL tables --- one of the main reasons why they are flat files is so
>you can still edit them after you've hosed them to the point that the
>database won't start or won't let you in. If you don't have a non-kluge
>solution to the DBA-mistake-recovery scenario, this is not going to be
>an improvement.
>
>Pushing postgresql.conf into a SQL table will also destroy all the work
>that was done recently to allow config sharing across multiple
>installations (eg the recent commit to support "include" goes out the
>window again). If we no longer care about that, why not?
>
>
>

I think we should treat pg_hba.conf and postgresql.conf as separate
cases. The proposal was only for pg_hba.conf.

There are several possible ways around the "settings hosed" issue,
including Robert's suggestion of a flag to say "don't read the table,
read this file instead".

I agree about the value of "include" for postgresql.conf.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Andrew Dunstan wrote:
>
> ISTM that the first requirement is for a sane API that will handle the
> fact that HBA lines are ordered. Persistence in itself shouldn't be a
> big problem - we already do that with some shared tables, iirc.
>
> so we might have some functions like:
>
> insert_hba_rule(at_position int, connection_type text, username text,
> dbname text, cidr_host text, method text)
> move_hba_rule(from_position int, to_position int)
> delete_hba_rule(at_position int)
>
> Inventing new SQL syntax might make things a bit tougher.
>

I dont think it has to be ordered preliminary. Since we are
dealing with subnets and stuff - the ordering already lays
in the data - just like routing tables work: most specific
matches first.

I could think of a solution where pg_hba.conf just
overrides the database table (so you have a starting
point with empty table and/or reentry in case of a
mistake)

regards
Tino

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Wed, 2006-03-29 at 16:20 -0500, Jonah H. Harris wrote:

> Enable the alteration and persistence of postgresql.conf and
> pg_hba.conf configuration parameters via SQL.

Agreed.

I'd also add the seemingly obvious caveat that parameters should be
unique. A common problem is for one person to edit a postgresql.conf by
editing in place, then another person to add a line at the bottom. The
second entry overrides the first.

I would also like these settings to be grouped together as Profiles.
It's fairly common to have multiple postgresql.conf files for different
modes of processing: Normal Running, Bulkload, TuningMode so we would
want to be able to retain that capability in the future.

Best Regards, Simon Riggs


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tino Wildenhain wrote:
> Andrew Dunstan wrote:
>
>> ISTM that the first requirement is for a sane API that will handle the
>> fact that HBA lines are ordered. Persistence in itself shouldn't be a
>> big problem - we already do that with some shared tables, iirc.
>>
>> so we might have some functions like:
>>
>> insert_hba_rule(at_position int, connection_type text, username text,
>> dbname text, cidr_host text, method text)
>> move_hba_rule(from_position int, to_position int)
>> delete_hba_rule(at_position int)
>>
>> Inventing new SQL syntax might make things a bit tougher.
>>
>>
>
> I dont think it has to be ordered preliminary. Since we are
> dealing with subnets and stuff - the ordering already lays
> in the data - just like routing tables work: most specific
> matches first.
>
> I could think of a solution where pg_hba.conf just
> overrides the database table (so you have a starting
> point with empty table and/or reentry in case of a
> mistake)
>
>
>

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.

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.

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.

cheers

andrew


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match