Обсуждение: ldap connection parameter lookup

On Fri, 2021-01-15 at 14:09 +0000, Zwettler Markus (OIZ) wrote: 
> I want to use ldap to lookup the connection parameters:
> https://www.postgresql.org/docs/12/libpq-ldap.html
>  
>  
> Do I have to create one static entry per database within pg_service.conf like:
>  
> [mydatabase]
> ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=mydatabase)
>  
>  
> or is there also some kind of generic variant like this (meaning lookup connection parameters for the database name I
tellyou somehow):
 
>  
> [${PGDATABASE}]
> ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=${PGDATABASE})

I proposed something like that a while ago:
https://postgr.es/m/D960CB61B694CF459DCFB4B0128514C2F3442B%40exadv11.host.magwien.gv.at
but it was rejected.

Perhaps you could come up with a better version.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe <laurenz.albe@cybertec.at>
> Gesendet: Freitag, 15. Januar 2021 17:21
> An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>; pgsql-
> general@postgresql.org
> Betreff: Re: ldap connection parameter lookup
> 
> On Fri, 2021-01-15 at 14:09 +0000, Zwettler Markus (OIZ) wrote:
> > I want to use ldap to lookup the connection parameters:
> > https://www.postgresql.org/docs/12/libpq-ldap.html
> >
> >
> > Do I have to create one static entry per database within pg_service.conf like:
> >
> > [mydatabase]
> >
> ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=mydat
> abase)
> >
> >
> > or is there also some kind of generic variant like this (meaning lookup connection
> parameters for the database name I tell you somehow):
> >
> > [${PGDATABASE}]
> >
> ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=${PGD
> ATABASE})
> 
> I proposed something like that a while ago:
> https://postgr.es/m/D960CB61B694CF459DCFB4B0128514C2F3442B%40exadv11
> .host.magwien.gv.at
> but it was rejected.
> 
> Perhaps you could come up with a better version.
> 
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com



I'm afraid not. My proposal is as close to yours.

Anyway. PostgreSQL needs some kind of generic central name resolution service.

It is not feasible to do static entries per database in a large environment with hundreds of clients.

It's also not feasible to have ordinary endusers have to handle this static entry by themselves.

So Tom, lets give it a try?

Regards,
Markus

On 2021-01-22 10:22, Zwettler Markus (OIZ) wrote:
>>
>> On Fri, 2021-01-15 at 14:09 +0000, Zwettler Markus (OIZ) wrote:
>>> I want to use ldap to lookup the connection parameters:
>>> https://www.postgresql.org/docs/12/libpq-ldap.html
>>>
>>> or is there also some kind of generic variant like this (meaning lookup connection
>>> parameters for the database name I tell you somehow):
>>>
>>> [${PGDATABASE}]
>>> ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=${PGD
>>> ATABASE})
>>
>> I proposed something like that a while ago:
>> https://postgr.es/m/D960CB61B694CF459DCFB4B0128514C2F3442B%40exadv11
>> .host.magwien.gv.at
>> but it was rejected.
>>
>> Perhaps you could come up with a better version.
>>
> I'm afraid not. My proposal is as close to yours.
> 
> Anyway. PostgreSQL needs some kind of generic central name resolution service.
> 
> It is not feasible to do static entries per database in a large environment with hundreds of clients.
> 
> It's also not feasible to have ordinary endusers have to handle this static entry by themselves.

Hi,

I think the error here is trying to pass parameters defined in the block
key to the ldap parameter. That's rather hackish and requires proper
understanding of all possible implications, and the uses is rather
limited (what if you need multiple LDAP parameters?)


What if we have something like:

[ldap]
ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=${PGDATABASE})

Basically, allow any environment variable in the ldap queries, including
both coming from the user/application and setting some standard ones
like PGUSER, PGHOST, PGDATABASE for parameters passed on the command
line/api call (whichever takes precedence).

You then connect with the ldap service, pass any required option as env
or parameters (here the database name), and let postgresql interpolate
it in the query (of course we also need to properly escape the string in
ldap uri...). Unset env variables would be considered an error.

The only possible caveat I see is that we could have to allow escaping
the $, even though I don't think there's any realistic possibility for
it to be used on the ldap url (and can it be url-encoded? in which case
it would be already solved...)


Regards,

--
Thomas