Re: LDAP service lookup

Hello!

I've just accidently stumbled upon
http://www.postgresql.org/docs/8.2/static/libpq-ldap.html
and thought "hey, this is what my friend, a huge BigRDBMS fan, was
telling me about.

Now that I've read it, I think it could be very useful in an
enterpisish sort of way
(addressing databases as "services" not as "host+port+database name"), and
it could be used as an advocacy lever ("you think LDAP directory with
DB-services
is neat? PostgreSQL already has it").

Then again, apart from libpq I don't see it mentioned anywhere. I would like
to have [1] a Setting-up-LDAP-for-PgSQL-HOWTO. I would like to use it
from DBD::Pg (I _guess_ its a matter of DBI->connect('dbi:Pg',
'service=foo', ...);,
but its a wild guess). And I would like to use it from psql (this is a tricky
part, since \c expects DBNAME, not a service name, and using both in
this context would introduce ambiguity).

Could anyone of you tell me about your setups if you use LDAP for
this? How do you feel about it? Which LDAP server do you use?

Regards,
Dawid

[1]: I may get to writing one, as it intrigues me.

> I've just accidently stumbled upon
> http://www.postgresql.org/docs/8.2/static/libpq-ldap.html
> and thought "hey, this is what my friend, a huge BigRDBMS fan, was
> telling me about.
>
> Now that I've read it, I think it could be very useful in an
> enterpisish sort of way
> (addressing databases as "services" not as
> "host+port+database name"), and
> it could be used as an advocacy lever ("you think LDAP directory with
> DB-services
> is neat? PostgreSQL already has it").

I'm glad that *somebody* else appreciates it :^)

> Then again, apart from libpq I don't see it mentioned
> anywhere. I would like
> to have [1] a Setting-up-LDAP-for-PgSQL-HOWTO.

Being the author of the code, I'd be willing to compose one if
there is a demand.
I thought that the documentation you quoted above would be enough -
it has samples and everything.
What information is missing in your opinion?

> I would like to use it
> from DBD::Pg (I _guess_ its a matter of DBI->connect('dbi:Pg',
> 'service=foo', ...);,
> but its a wild guess). And I would like to use it from psql
> (this is a tricky
> part, since \c expects DBNAME, not a service name, and using both in
> this context would introduce ambiguity).

Good news - you can do all this.
Every client API and interface that uses libpq will be able to do
LDAP service lookup.

In 8.3 you will be able to say "psql service=myname", in 8.2 you
have to resort to "PGSERVICE=myname psql" because there is no
support for the service parameter.

Your DBD::Pg sample should work.

> Could anyone of you tell me about your setups if you use LDAP for
> this? How do you feel about it? Which LDAP server do you use?

We use Oracle's Internet Directory, but any LDAP server should do.
It's just a matter of organizing your databases in LDAP entries and
writing the corresponding LDAP searches into pg_service.conf.

The sample in the documentation works with standard LDAP objectclasses,
but you may want to use your own with more reasonable names.

Yours,
Laurenz Albe

On 8/27/07, Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at> wrote:
> > it could be used as an advocacy lever ("you think LDAP directory with
> > DB-services
> > is neat? PostgreSQL already has it").
> I'm glad that *somebody* else appreciates it :^)

Oh, I do, I do. :)

> > Then again, apart from libpq I don't see it mentioned
> > anywhere. I would like
> > to have [1] a Setting-up-LDAP-for-PgSQL-HOWTO.
> Being the author of the code, I'd be willing to compose one if
> there is a demand.
> I thought that the documentation you quoted above would be enough -
> it has samples and everything.
> What information is missing in your opinion?

Looking at the 8.3devel documentation...

I think it should be mentioned in 18. Server Configuration. probably
somewhere in 18.3 Connections and Authentication, that there is
a possibility of using Service names instead of traditional connect
strings -- and a link pointing to libpq-ldap documentation.
This would make people much less likely to miss this point, especially
if they don't plan to code in libpq C library. :-)
(personally I think it would fit in more places, like Managing Databases
(though LSAP is more generic in scope) or even HA (makes it much
easier to promote slave to master, just one update in one place), though
I wouldn't want to overpromote it ;)).

[...]
> In 8.3 you will be able to say "psql service=myname", in 8.2 you
> have to resort to "PGSERVICE=myname psql" because there is no
> support for the service parameter.

Somehow I've missed it while skimming through 8.2 docs. I think
http://www.postgresql.org/docs/8.2/static/libpq-pgservice.html
could use this specific example to send a subliminal message:
PGSERVICE=myname psql

> > Could anyone of you tell me about your setups if you use LDAP for
> > this? How do you feel about it? Which LDAP server do you use?
> We use Oracle's Internet Directory, but any LDAP server should do.
> It's just a matter of organizing your databases in LDAP entries and
> writing the corresponding LDAP searches into pg_service.conf.

And, while not belonging to PostgreSQL documentation, but defenately
belonging iin techdocs, whould be a step-by-step guide of setting up
pg_services in OID and other LDAP servers. Funny thing, I think I'll
be looking at putting pg_services in OID as well.

Regards,
Dawid

Dawid Kuroczko wrote:
>>> Then again, apart from libpq I don't see it mentioned anywhere.
[...]
> Looking at the 8.3devel documentation...
>
> I think it should be mentioned in 18. Server Configuration. probably
> somewhere in 18.3 Connections and Authentication, that there is
> a possibility of using Service names instead of traditional connect
> strings -- and a link pointing to libpq-ldap documentation.
> This would make people much less likely to miss this point, especially
> if they don't plan to code in libpq C library. :-)

The server config options are not a good place.

But it could be mentioned in the 'psql' man page, under
'Connection to a database':

$ psql "service=myservice sslmode=require"

<proposed addition>
This way you can also use LDAP for connection parameter lookup (See
Section 30.15).
</proposed addition>
See Section 30.1 for more information on all the available connection
options.

If there are no objections, I'd create a documentation patch for this.

> And, while not belonging to PostgreSQL documentation, but defenately
> belonging iin techdocs, whould be a step-by-step guide of setting up
> pg_services in OID and other LDAP servers. Funny thing, I think I'll
> be looking at putting pg_services in OID as well.

Hmm, a tutorial for configuring LDAP servers would be quite off topic.
I think that the examples in Section 30.15 are sufficient for somebody
who is familiar with LDAP.

Yours,
Laurenz Albe

On 8/27/07, Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at> wrote:
> Dawid Kuroczko wrote:
> >>> Then again, apart from libpq I don't see it mentioned anywhere.
> [...]
> > Looking at the 8.3devel documentation...
> >
> > I think it should be mentioned in 18. Server Configuration. probably
> > somewhere in 18.3 Connections and Authentication, that there is
> > a possibility of using Service names instead of traditional connect
> > strings -- and a link pointing to libpq-ldap documentation.
> > This would make people much less likely to miss this point, especially
> > if they don't plan to code in libpq C library. :-)
> The server config options are not a good place.

Which I do know, but it's just "if it were there, I would have spotted it
much earlier" sort of argument.

Incidentally, this is a question for people who have access to www
access logs. Which sections of documentation have highest "hit rate"?
My guess would be: Server Configuration and SQL Reference, but it
would be interesting to see one.

> But it could be mentioned in the 'psql' man page, under
> 'Connection to a database':
>
> $ psql "service=myservice sslmode=require"

I think it defenately should.

> If there are no objections, I'd create a documentation patch for this.
>
> > And, while not belonging to PostgreSQL documentation, but defenately
> > belonging iin techdocs, whould be a step-by-step guide of setting up
> > pg_services in OID and other LDAP servers. Funny thing, I think I'll
> > be looking at putting pg_services in OID as well.
>
> Hmm, a tutorial for configuring LDAP servers would be quite off topic.
> I think that the examples in Section 30.15 are sufficient for somebody
> who is familiar with LDAP.

I have been playing with it for a few moments now. i think there should
be mentioned in the documentation that pg_service.conf can also
contain static "service definitions", and it also would be valuable to
add into pg_service.conf.sample an example ldap:// stanza, so if
person opens the file, she will be enlightened.

And a missing feature. Or rather treat it as feature request. :-)
A "wildcard entry". I would like to set my environment that,
on each client I would put pg_service.conf having two and only
two LDAP servers in it (second one for failover. I think the entry
might look like:
### wildcard entry:
[%] # or [*] ?
ldap://ldap1.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=%s)
ldap://ldap2.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=%s)

Which, when given:
psql "service = foobarbaz"
..would query ...?(cn=foobarbaz)

Deploying a new database would be as simple as adding it into LDAP.

Regards,
Dawid

Dawid Kuroczko wrote:
> [...] and it also would be valuable to
> add into pg_service.conf.sample an example ldap:// stanza, so if
> person opens the file, she will be enlightened.

I like that idea.

> And a missing feature. Or rather treat it as feature request. :-)
> A "wildcard entry". I would like to set my environment that,
> on each client I would put pg_service.conf having two and only
> two LDAP servers in it (second one for failover. I think the entry
> might look like:
> ### wildcard entry:
> [%] # or [*] ?
>
ldap://ldap1.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=%s)
>
ldap://ldap2.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=%s)
>
> Which, when given:
> psql "service = foobarbaz"
> ..would query ...?(cn=foobarbaz)

This is also worth thinking about ... it would make the code and
the pg_service.conf file more complicated, but would definitely be
useful
if you only use the service file for LDAP lookup.

Yours,
Laurenz Albe

Since nobody seemed to have objections to

http://archives.postgresql.org/pgsql-hackers/2007-08/msg00960.php

I submit a patch that adds reference to LDAP connection parameter
lookup in two places:

- one line in the psql man page after the example that uses a
"service" parameter to establish a connection.

- one line in the comments of pg_service.conf.sample

I have also added a short paragraph to the libpq-ldap
section that gives an example of a pg_service.conf entry
with LDAP URL.

Yours,
Laurenz Albe

Patch applied. Thanks. Your documentation changes can be viewed in
five minutes using links on the developer's page,
http://www.postgresql.org/developer/testing.

---------------------------------------------------------------------------

Albe Laurenz wrote:
> Since nobody seemed to have objections to
>
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg00960.php
>
> I submit a patch that adds reference to LDAP connection parameter
> lookup in two places:
>
> - one line in the psql man page after the example that uses a
> "service" parameter to establish a connection.
>
> - one line in the comments of pg_service.conf.sample
>
> I have also added a short paragraph to the libpq-ldap
> section that gives an example of a pg_service.conf entry
> with LDAP URL.
>
> Yours,
> Laurenz Albe

Content-Description: ldap-doc.patch

[ Attachment, skipping... ]

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

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +