new feature: LDAP database name resolution

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/

* 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/

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]

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 the init 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&gt; and
easy-to-use client-side URL transfer library, supporting FTP, 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