Thread: PostgreSQL Service Name Enhancement - Wildcard support for LDAP/DNS lookup

<div class="WordSection1"><p class="MsoPlainText">Hi,<p class="MsoPlainText"> <p class="MsoPlainText">I am looking to
patchthe LDAP Service Name feature and would like some feedback prior to doing so. In general, while I personally view
thisas a bug fix, it could also easily be considered to simply be an enhancement to current functionality and therefore
thebelow is written as a proposed enhancement.<p class="MsoPlainText"> <p class="MsoPlainText">Note: This will also
potentiallyserve as my first patch submission, so this will be a "smoke test" of my direct interaction with the mailing
listas a Goldman Sachs Employee for proposal/feedback as well as following up with a potential patch/participation in
commitprocesses, etc. As such my replies and related code submissions may be delayed for processes to happen on my end,
withmy associated apologies in advance. I hope to have this process completed for contribution to the 9.5 release.<p
class="MsoPlainText"> <pclass="MsoPlainText">################## Currently:<br /><br /><p class="MsoPlainText">* Service
namesare provided as an available abstraction mechanism for a host:port (and user, dbname, ssl requirements, etc.)
informationfor connection parameters via libpq.<p class="MsoPlainText">* If service names are used, they must each
individuallyhave an entry in pg_service.conf.<p class="MsoPlainText">* In a service name entry, host:port entries,
hostscan either be directly provided or their values may be referenced via an LDAP location that provides equivalent
name/valuepairs for connection information.<p class="MsoPlainText"> <p class="MsoPlainText">The service name feature
facilitatesthe use of dynamic/managed host entries or allows for active connection configuration management with the
goalin either case of separating such overhead from application or other process configuration.<p
class="MsoPlainText"> <pclass="MsoPlainText">Therefore:<p class="MsoPlainText">* Dynamic DNS entries require a fixed
portor active management of the file (DNS resolution can happen elsewhere)<p class="MsoPlainText">* Non-Dynamic DNS
entriesrequire active management of pg_service.conf, but may still be desirable for configuration management reasons -
versioncontrol?<p class="MsoPlainText">* LDAP entries are largely static, but do require active management of
pg_service.confif new data servers are to be added (or old ones decommissioned)<p class="MsoPlainText"> <p
class="MsoPlainText">Inall cases described above, a newly provisioned data server requires a new service name entry,
whichrequires the file to be actively managed. This mitigates the value of using LDAP or Dynamic DNS entries (with
fixedports in the DNS case) since an active distribution mechanism must still be in place, especially for deployments
ofmany servers and perhaps this useful mechanism is used less often as a result.<p class="MsoPlainText"> <p
class="MsoPlainText">##################Proposal:<p class="MsoPlainText"> <p class="MsoPlainText">* Have a wildcard
entrythat allows string substitution of the service name into either the host field or an LDAP record.<p
class="MsoPlainText"> <pclass="MsoPlainText">I do not want to over complicate the existing configuration file or
parsinglogic as it is pretty lightweight currently. As such, I am looking for feedback related to if it would be
acceptableto have a "wildcard" entry for service names as well as what that wildcard should be. I do *not* propose that
complexstring substitution, including regular expressions, etc. be utilized for service name matching at this time.<p
class="MsoPlainText"> <pclass="MsoPlainText">Per 31.16 in the 9.4 Documentation, a valid service entry is of the
followingformat:<p class="MsoPlainText"> <p class="MsoPlainText">    # comment<p class="MsoPlainText">    [mydb]<p
class="MsoPlainText">   host=somehost.domain.com<p class="MsoPlainText">    port=5433<p class="MsoPlainText">   
user=admin<pclass="MsoPlainText"> <p class="MsoPlainText">Would specifying a special value for the service name,
perhaps[%], be an acceptable implementation of this enhancement/fix to my above concerns?<p class="MsoPlainText"> <p
class="MsoPlainText">Example:<pclass="MsoPlainText"> <p class="MsoPlainText">    # comment<p class="MsoPlainText">   
[%]<pclass="MsoPlainText">    host=%.domain.com<p class="MsoPlainText">    port=5433<p class="MsoPlainText">   
user=admin<pclass="MsoPlainText"> <p class="MsoPlainText">...or more interestingly, per 31.17:<p
class="MsoPlainText"> <pclass="MsoPlainText">    # comment<p class="MsoPlainText">    [%]<p class="MsoPlainText">   
ldap=ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=%)<pclass="MsoPlainText"> <p
class="MsoPlainText">Thelocation of [%] and therefore order of the service names would still be honored. I believe this
allowsfor useful functionality:<p class="MsoPlainText">* LDAP wildcard listed first - libpq can always try an LDAP
lookupand proceed with further service names if one is not found in LDAP location<p class="MsoPlainText">* LDAP
wildcardlisted last - only look up LDAP entries if a service name doesn’t match prior service names<p
class="MsoPlainText">*LDAP wildcard in the middle - combination of first 2 behaviors<p class="MsoPlainText">* DNS
wildcardlisted last - try connecting with service name in well-formed dns-based host entry (this likely can't be first
asit would always match)<p class="MsoPlainText">* Combination of the LDAP/keyword = value entries per 31.17: <p
class="MsoPlainText"> <pclass="MsoPlainText">From the existing documentation:  "Processing of pg_service.conf is
terminatedafter a successful LDAP lookup, but is continued if the LDAP server cannot be contacted. This is to provide a
fallbackwith further LDAP URL lines that point to different LDAP servers, classical keyword = value pairs, or default
connectionoptions. If you would rather get an error message in this case, add a syntactically incorrect line after the
LDAPURL."<p class="MsoPlainText"> <p class="MsoPlainText">The "%" in the ldap, host entry would replace with the
servicename in use - also possible to make it less likely to match a future valid "%" by providing it as [%] in the
string,though I don’t know why a % would reasonably show up in an ldap or host record.<p class="MsoPlainText"> <p
class="MsoPlainText">Ihave not made the patch yet, but it looks like this change is isolated to the parseServiceFile
subroutine,currently starting on line 3867 in <a
href="https://github.com/postgres/postgres/blob/master/src/interfaces/libpq/fe-connect.c">https://github.com/postgres/postgres/blob/master/src/interfaces/libpq/fe-connect.c</a>
 (andassociated documentation).<p class="MsoPlainText"> <p class="MsoPlainText">################## Scope:<p
class="MsoPlainText"> <pclass="MsoPlainText">Note that this discussion is scoped only for LDAP directory services and
notfor LDAP authentication/authorization. Again, I do *not* propose that complex string substitution, including regular
expressions,etc. be utilized for service name matching at this time. Just looking for a simple [%] entry, which could
beextended for flexibility in a consistent way, if people find this general case useful.<p class="MsoPlainText"> <p
class="MsoPlainText">Iam looking forward to feedback and our first interaction on the distribution list.<p
class="MsoPlainText"> <pclass="MsoPlainText">Thanks,<p class="MsoPlainText"> <p class="MsoPlainText">Bryan</div> 

"Doyle, Bryan" <Bryan.Doyle@gs.com> writes:
> Would specifying a special value for the service name, perhaps [%], be an acceptable implementation of this
enhancement/fixto my above concerns?
 

> Example:
>     # comment
>     [%]
>     host=%.domain.com
>     port=5433
>     user=admin

This doesn't seem like a terribly good idea, because such an entry would
capture *any* service name whatsoever.  And, since we check service names
before other possibilities such as host/database names, the entry would
then proceed to capture every possible connection request.

I follow what you're trying to do, but it needs to be a more constrained
syntax.  One possibility is to insist that the wildcard be only a part
of the name string, eg
   [myservers-%]   host=%.domain.com   port=5433   user=admin
        regards, tom lane

Tom,

I believe there are two main concerns that you raise, addressed below:

First:

> It needs to be a more constrained syntax.
> One possibility is to insist that the wildcard be only a part
> of the name string, eg
>
>     [myservers-%]
>     host=%.domain.com
>     port=5433
>     user=admin

* This counter-proposal is getting closer to the more complex matching requirements that I was attempting to de-scope,
butit should certainly be considered. I can see where someone may want to have a different LDAP/DNS domain or something
likethat through prefix convention, though we would likely want to restrict to one "%" symbol in the service
definition,yes? I am fine with including this capability in the patch, provided the general [%] case is still supported
(seebelow for expanded reasons). 

Second:

> since we check service names
> before other possibilities such as host/database names, the entry would
> then proceed to capture every possible connection request

* I should have explicitly covered the case where no service name is provided at connection time - If a service name is
notspecified in the connection string/connection parameters at all, I would propose that this wildcard entry not match
(evenif service names are processed first) and normal processing proceed. As a comparable, the '%' in a like statement
doesn'tmatch a NULL after all. I don't think having a blank replacement value would make much sense either. Please
informme if I am not addressing some part of your concern with this mindset. 


Some additional comments:

* It is in fact is desirable for us (and likely others) to capture all service names in one entry; I expect to utilize
itexclusively once implemented. I would like to look up all service name entries from a single LDAP location if a
previousentry in the file does not short circuit the match. To do this, I am explicitly looking to use a [%] entry in
ourservice name file - the prefix requirement is not consistent with our environmental requirements. The service name
fileis a client construct that can be overridden by the caller if they desire, but keep in mind that this type of
featureis targeted for controlled/managed environments. 

* Adding additional processing logic for 'myserver-%' would only make this more flexible for other use cases and would
certainlymeet the goals of this proposal, so I am fine including it in scope if the [%] is also allowed per above. When
theseother wildcard w/ prefix entries can match above the general [%] entry, it could be compelling (see my first email
regardingentry order considerations). Those not wanting the [%] could choose to not implement it and stick to something
closerto the prefix approach you have in mind. 


Summary of Open Questions:

* (From Above) For prefix wildcards, OK to restrict to one % replacement?

* Do the above points address initial concerns regarding service names being processed before host/db names?

* If both prefix/non-prefix are allowed, what should be the behavior for cases where [prefix-%] matches and fails to
connect/lookupand then [%] is also located further down? 
** Without additional discussion, I would assume that it would attempt a connection for consistency. Again, people can
chooseto not use both features together. Another option would be to somehow introduce a "stop processing" flag in the
serviceentries on connection/lookup failures, which may be generally useful even when wild cards are not in use. 


Thanks for the reply and your thoughts on this proposal so far. I am looking forward to the continued conversation.

Bryan

I have suggested a similar feature before and met with little enthusiasm:
http://www.postgresql.org/message-id/D960CB61B694CF459DCFB4B0128514C2F3442B@exadv11.host.magwien.gv.at

I still think it would be a nice feature and would make pg_service.conf
more useful than it is now.

Yours,
Laurenz Albe