new feature: LDAP database name resolution

Dear developers of PostgreSQL!

We (City of Vienna) have many Oracle databases, but plan to use
PostgreSQL for our future database installations as much as possible.
The following idea is inspired by Oracle.

We sometimes need to move a database from one machine to another
(hardware old/broken, upgrades, etc.). Now whenever a database is
moved to a different computer, all the clients have to address the
database at the new hostname.

This is the idea:
Hostname, port number and database name are stored on an LDAP server,
and (e.g.) instead of accessing a database with
$ psql -h host -p port -d database .....
you'd use something like
$ psql -N dbalias ...
and the client would perform a lookup on the LDAP server and
retrieve host name, port and database name.

The advantage is that when you relocate a database, all you have
to change is an entry on the LDAP server.

I am aware that adding such a feature requires changes at many
levels: libpq, psql, libecpg, ecpg, a --with-ldap flag in configure
and probably some more.

I would be willing to try and implement this if you think that
it is a good thing and not a superfluous feature.
If you don't think that this should make its way into the
PostgreSQL source tree, I could also just write a little standalone
program that does the lookup and keep the stuff out of PostgreSQL.

However, if that sounds interesting, how should I go about it?
Just check out the latest development code, hack it and post a diff
somewhere in the hope that it finds acceptance?
I'll probably not be able to port and test it on all platforms supported
by PostgreSQL - is that a problem?

I'd be grateful for all hints and bits of advice you could give me.

Yours,
Laurenz Albe

On Mon, Feb 20, 2006 at 02:36:42PM +0100, Albe Laurenz wrote:
> Dear developers of PostgreSQL!
>
> We (City of Vienna) have many Oracle databases, but plan to use
> PostgreSQL for our future database installations as much as possible.
> The following idea is inspired by Oracle.
>
> We sometimes need to move a database from one machine to another
> (hardware old/broken, upgrades, etc.). Now whenever a database is
> moved to a different computer, all the clients have to address the
> database at the new hostname.

Perhaps you should look into pg_service.conf. It's a configuration file
such that in your connect line you can say:

PQconnect("service=production");

and in the config file you say:

[production]
host=db1
port=5434
dbname=main

It's not quite as nice as LDAP to be sure, but it might work well
enough for your config. For example, you could write a program to
generate that file from an LDAP database.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

* Martijn van Oosterhout (kleptog(at)svana(dot)org) wrote:
> Perhaps you should look into pg_service.conf. It's a configuration file
> such that in your connect line you can say:
>
> PQconnect("service=production");
>
> and in the config file you say:
>
> [production]
> host=db1
> port=5434
> dbname=main
>
> It's not quite as nice as LDAP to be sure, but it might work well
> enough for your config. For example, you could write a program to
> generate that file from an LDAP database.

If LDAP is really what you're after, though, it seems to me one approach
that would involve changing only libpq (I think...) would be to support
having the 'pg_service.conf' in LDAP, essentially. Perhaps with a
seperate namespace (ie: "service=ldap:production"), or maybe as a
fall-back if the name isn't found in a local pg_service.conf.

Just some thoughts,

Stephen

On Mon, Feb 20, 2006 at 02:36:42PM +0100, Albe Laurenz wrote:
> $ psql -h host -p port -d database .....
> you'd use something like
> $ psql -N dbalias ...
> and the client would perform a lookup on the LDAP server and
> retrieve host name, port and database name.

I suggest you look at pgpool, which sort of does this for you
(possibly automatically).

> I am aware that adding such a feature requires changes at many
> levels: libpq, psql, libecpg, ecpg, a --with-ldap flag in configure
> and probably some more.

I doubt it. You need a proxy; you don't need this built into the
code. Think of the way UNIX does things: small tools that each do
one job, piped together. A connection dispatcher should be pretty
cheap, and those who have reported success with pgpool have remarked
on how lightweight it is.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler

Stephen Frost wrote:

>* Martijn van Oosterhout (kleptog(at)svana(dot)org) wrote:
>
>
>>Perhaps you should look into pg_service.conf. It's a configuration file
>>such that in your connect line you can say:
>>
>>PQconnect("service=production");
>>
>>and in the config file you say:
>>
>>[production]
>>host=db1
>>port=5434
>>dbname=main
>>
>>It's not quite as nice as LDAP to be sure, but it might work well
>>enough for your config. For example, you could write a program to
>>generate that file from an LDAP database.
>>
>>
>
>If LDAP is really what you're after, though, it seems to me one approach
>that would involve changing only libpq (I think...) would be to support
>having the 'pg_service.conf' in LDAP, essentially. Perhaps with a
>seperate namespace (ie: "service=ldap:production"), or maybe as a
>fall-back if the name isn't found in a local pg_service.conf.
>
>
>

The location of pg_service.conf is governed by the environment variable
PGSYSCONFDIR. Maybe there should be a facility allow it to point to a
URL which is fetched via libcurl. Then you could have it point to LDAP,
a web server, a tftp server ... lots of places.

cheers

andrew

Albe Laurenz wrote:
> We sometimes need to move a database from one machine to another
> (hardware old/broken, upgrades, etc.). Now whenever a database is
> moved to a different computer, all the clients have to address the
> database at the new hostname.
>
A lower-tech solution is:

http://freshmeat.net/projects/postgresql-relay/

It sits on top of PG and intercepts the connection protocol, as I
understand it. Seems to work...