Thread

1. new feature: LDAP database name resolution

   Albe Laurenz <all@adv.magwien.gv.at> — 2006-02-20T13:36:42Z

   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
   
   

2. Re: new feature: LDAP database name resolution

   Martijn van Oosterhout <kleptog@svana.org> — 2006-02-20T14:02:54Z

   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@svana.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.
   

3. Re: new feature: LDAP database name resolution

   Stephen Frost <sfrost@snowman.net> — 2006-02-20T14:11:52Z

   * Martijn van Oosterhout (kleptog@svana.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
   

4. Re: new feature: LDAP database name resolution

   Andrew Sullivan <ajs@crankycanuck.ca> — 2006-02-20T14:33:24Z

   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@crankycanuck.ca
   "The year's penultimate month" is not in truth a good way of saying
   November.
   		--H.W. Fowler
   
   

5. Re: new feature: LDAP database name resolution

   Andrew Dunstan <andrew@dunslane.net> — 2006-02-20T14:54:16Z

   Stephen Frost wrote:
   
   >* Martijn van Oosterhout (kleptog@svana.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
   
   

6. Re: new feature: LDAP database name resolution

   Philip Warner <pjw@rhyme.com.au> — 2006-02-20T15:34:40Z

   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...