Обсуждение: 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@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.

* 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

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

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

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

Thanks to everybody who answered.

Maybe it is really the best thing to use a tool like postgresql-relay or
pgpool - I will investigate these.
I'm not eager to reinvent the wheel.

We have considered relocating DNS entries, but the problem is that a
changed
DNS entry takes long to propagate; in particular Windows has a caching
problem there.

Thank you also for drawing my attention to pg_service.conf - I have not
been aware of it.
There are two 'shortcomings':
- It still means that you have to change the config file on every
client.
- This feature cannot be used with psql or ecpg, right?

Do you think that it is worth the effort for me to look into extending
the
pg_service.conf/PGSYSCONFDIR approach to LDAP (and changing psql to use
it)?

Yours,
Laurenz Albe

On Tue, Feb 21, 2006 at 10:02:58AM +0100, Albe Laurenz wrote:
> Thank you also for drawing my attention to pg_service.conf - I have not
> been aware of it.
> There are two 'shortcomings':
> - It still means that you have to change the config file on every
> client.

Well yes. However, you could generate the config file automatically
from another source, either LDAP or something else.

> - This feature cannot be used with psql or ecpg, right?

Well it can since it works with any library that uses libpq, though
it's not immediately obvious. Unfortunatly it appears ecpg doesn't
appear to allow you to specify a connection string, so you have to do
it using the environment variable.

Instead of using a commandline argument to psql you have to do it like
this:

PGSERVICE=test psql

For an ecpg application, setting the PGSERVICE environment variable and
then connecting as DEFAULT should work.

For Perl CBI applications where you specify the connect string
yourself, you can just use "service=test".

> Do you think that it is worth the effort for me to look into extending
> the
> pg_service.conf/PGSYSCONFDIR approach to LDAP (and changing psql to use
> it)?

Given that pg_service.conf is handled by libpq, it would be better to
change it there so all applications connecting to postgres can use it,
not just psql.

Have a nice day,
--
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.

Albe Laurenz wrote:
> Thanks to everybody who answered.
> 
> Maybe it is really the best thing to use a tool like postgresql-relay or
> pgpool - I will investigate these.
> I'm not eager to reinvent the wheel.
> 
> We have considered relocating DNS entries, but the problem is that a
> changed
> DNS entry takes long to propagate; in particular Windows has a caching
> problem there.

So even if you specify the TTL of the DNS records to be 60 seconds for 
the front end labels you put on your servers, Windows will not refresh 
after that period of time, even though it should?

[snip]

> Yours,
> Laurenz Albe
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 
> 

On 21 Feb 2006, at 10:42, Martijn van Oosterhout wrote:

> On Tue, Feb 21, 2006 at 10:02:58AM +0100, Albe Laurenz wrote:
>> Thank you also for drawing my attention to pg_service.conf - I
>> have not
>> been aware of it.
>> There are two 'shortcomings':
>> - It still means that you have to change the config file on every
>> client.
>
> Well yes. However, you could generate the config file automatically
> from another source, either LDAP or something else.


this is definitely the best way of doing it. in fact some folks out
there use similar configurations to manager large scale systems
efficiently.
best regards,
    hans-jürgen schönig

--
cybertec geschwinde & schönig gmbh
schöngrabern 134, a-2020 hollabrunn
www.cybertec.at

I am now in the process of writing a patch against CVS HEAD that
changes fe-connect.c as follows:

- If there is a 'service' option or PGSERVICE is set, AND the
environment PGLDAPSERVERS is set to a comma separated list of LDAP server URIs, LDAP name resolution cuts in.
- Before pg_services.conf is examined, the LDAP servers are contacted in order until a connection can be established.
- The server is queried for an entry whose distinguished name is the value of 'service'. A certain attribute is
retrieved.
- The resulting string is parsed for options.
- If that fails, pg_services.conf is read as fallback.

I have added a configure option --with-openldap to enable the code.

Does that make sense to you?

Should I try to polish and test the code and submit it as a patch
or is this a lost effort?

Do you have ideas for improvement?

>>> Thank you also for drawing my attention to pg_service.conf - I have
not
>>> been aware of it.
>>> There are two 'shortcomings':
>>> - It still means that you have to change the config file on every
client.
>>
>> Well yes. However, you could generate the config file automatically
>> from another source, either LDAP or something else.
>
> this is definitely the best way of doing it. in fact some folks out
> there use similar configurations to manager large scale systems
> efficiently.

Having to update configuration files on all clients is always a hassle.
Of course it can be done, but isn't it much nicer to have the client
query a configuration server at connection time?

Yours,
Laurenz Albe

Albe Laurenz wrote:
> I am now in the process of writing a patch against CVS HEAD that
> changes fe-connect.c as follows:
> 
> - If there is a 'service' option or PGSERVICE is set,


A little off-topic, but related: PeterE recently complained about an 
option in pgAdmin which is called "service". Its name originates from 
Windows where service is commonly a process running under control of the  service control manager (SCM), comparable to
theinit process; so for 
 
win32 the naming is accurate. On *ix, pgAdmin accepts a path to pg_ctl 
or a script used to control the local postmaster as in /etc/init.d.

It's probably a Good Thing (tm) if new features try to avoid possible 
name conflicts with common terms.

Regards,
Andreas

Albe Laurenz wrote:

>I am now in the process of writing a patch against CVS HEAD that
>changes fe-connect.c as follows:
>
>- If there is a 'service' option or PGSERVICE is set, AND the
>environment
>  PGLDAPSERVERS is set to a comma separated list of LDAP server URIs,
>  LDAP name resolution cuts in.
>- Before pg_services.conf is examined, the LDAP servers are contacted
>  in order until a connection can be established.
>- The server is queried for an entry whose distinguished name is
>  the value of 'service'. A certain attribute is retrieved.
>- The resulting string is parsed for options.
>- If that fails, pg_services.conf is read as fallback.
>
>I have added a configure option --with-openldap to enable the code.
>
>Does that make sense to you?
>
>Should I try to polish and test the code and submit it as a patch
>or is this a lost effort?
>
>Do you have ideas for improvement?
>  
>


I would still much prefer to see remote config fetching done in a more 
general way, using say libcurl (which handles ldap just fine if openldap 
is available). Then we could fetch the config from a variety of sources, 
not just ldap. Libcurl uses a modified MIT license, so we should not 
have any problems on that score. And with luck it would involve less 
postgres code maintenance.

The blurb on the libcurl page at http://curl.haxx.se/libcurl/ says:
   libcurl is a free <http://curl.haxx.se/docs/copyright.html> and   easy-to-use client-side URL transfer library,
supportingFTP, FTPS,   TFTP, HTTP, HTTPS, TELNET, DICT, FILE and LDAP. libcurl supports   HTTPS certificates, HTTP
POST,HTTP PUT, FTP uploading, HTTP form   based upload, proxies, cookies, user+password authentication (Basic,
Digest,NTLM, Negotiate, Kerberos4), file transfer resume, http   proxy tunneling and more! 
 
   libcurl is highly portable, it builds and works identically on   numerous platforms, including Solaris, NetBSD,
FreeBSD,OpenBSD,   Darwin, HPUX, IRIX, AIX, Tru64, Linux, UnixWare, HURD, Windows,   Amiga, OS/2, BeOs, Mac OS X,
Ultrix,QNX, OpenVMS, RISC OS, Novell   NetWare, DOS and more...
 


cheers

andrew

>>I have added a configure option --with-openldap to enable the code.
>>
>>Does that make sense to you?
>>
>>Should I try to polish and test the code and submit it as a patch
>
> I would still much prefer to see remote config fetching done in a more

> general way, using say libcurl (which handles ldap just fine if
openldap
> is available). Then we could fetch the config from a variety of
sources,
> not just ldap. Libcurl uses a modified MIT license, so we should not
> have any problems on that score. And with luck it would involve less
> postgres code maintenance.

So if I make the effort to modify the LDAP support to curl support,
would the community be interested?

Since we here use only LDAP, I would only want to embark on this if
you want to add it to PostgreSQL (if my code is good enough,
of course).

Yours,
Laurenz Albe

"Albe Laurenz" <all@adv.magwien.gv.at> writes:
> I am now in the process of writing a patch against CVS HEAD that
> changes fe-connect.c as follows:

> - If there is a 'service' option or PGSERVICE is set, AND the
> environment
>   PGLDAPSERVERS is set to a comma separated list of LDAP server URIs,
>   LDAP name resolution cuts in.
> - Before pg_services.conf is examined, the LDAP servers are contacted
>   in order until a connection can be established.
> - The server is queried for an entry whose distinguished name is
>   the value of 'service'. A certain attribute is retrieved.
> - The resulting string is parsed for options.
> - If that fails, pg_services.conf is read as fallback.

Uh, why is it a good idea to overload the "service" option like that?
ISTM it'd be less confusing to use a separate option.  Further I suggest
that pg_service ought to be handled first, ie, it makes sense to me to
be able to put both the LDAP name and the LDAP server address(es) into a
pg_service.conf entry.  The other way (LDAP pointing to pg_service.conf)
is clearly nonsensical, but that doesn't mean that they aren't useful
together.
        regards, tom lane

> Uh, why is it a good idea to overload the "service" option like that?
> ISTM it'd be less confusing to use a separate option.  Further I
suggest
> that pg_service ought to be handled first, ie, it makes sense to me to
> be able to put both the LDAP name and the LDAP server address(es) into
a
> pg_service.conf entry.  The other way (LDAP pointing to
pg_service.conf)
> is clearly nonsensical, but that doesn't mean that they aren't useful
> together.

That idea is much better than my original one.

There could be a pg_service.conf entry like this:

[servicename]
ldap://server.domain/dn?filter?scope?attribute

or similar that retrieves a string to be used as connection options.

Would that satisfy everybody (if I use curl instead of openldap)?

Yours,
Laurenz Albe

Andrew Dunstan <andrew@dunslane.net> writes:
> I would still much prefer to see remote config fetching done in a more 
> general way, using say libcurl (which handles ldap just fine if openldap 
> is available). Then we could fetch the config from a variety of sources, 
> not just ldap.

What other cases are actually interesting?  How much code do we save
if we use libcurl instead of homegrown LDAP-accessing code?  Does
libcurl bring in any secondary dependencies, or have limitations of
its own (thread safety is one obvious point to ask about)?

Depending on an outside library isn't free, so I think the tradeoff
has to be considered carefully.
        regards, tom lane

Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>  
>
>>I would still much prefer to see remote config fetching done in a more 
>>general way, using say libcurl (which handles ldap just fine if openldap 
>>is available). Then we could fetch the config from a variety of sources, 
>>not just ldap.
>>    
>>
>
>What other cases are actually interesting?  How much code do we save
>if we use libcurl instead of homegrown LDAP-accessing code?  Does
>libcurl bring in any secondary dependencies, or have limitations of
>its own (thread safety is one obvious point to ask about)?
>
>Depending on an outside library isn't free, so I think the tradeoff
>has to be considered carefully.
>
>            
>  
>

It claims to be thread-safe. It has both synch and asynch APIs - 
fetching something synchronously involves literally a handful of lines 
of code.

There are no dependencies that should bother us - for all our uses they 
would be things we normally use anyway, like openssl and zlib. Plus for 
this purpose openldap, of course. These are all optional.

As for uses, I could well imagine hosting a service map on an internal 
web server, for example. If you want it by property it could even be 
done with a CGI script that gives you just the bit you want.

I'm not hugely dogmatic about it, but it seemed to me that for about the 
same amount of trouble we could provide a much more general mechanism.

cheers

andrew