More flexible LDAP auth search filters?

On Thu, Jul 13, 2017 at 9:31 AM, Thomas Munro <thomas.munro@enterprisedb.com

wrote:

Hi hackers,

A customer asked how to use pg_hba.conf LDAP search+bind
authentication to restrict logins to users in one of a small number of
groups. ldapsearchattribute only lets you make filters like
"(foo=username)", so it couldn't be done. Is there any reason we
should allow a more general kind of search filter constructions?

A post on planet.postgresql.org today reminded me that a colleague had
asked me to post this POC patch here for discussion. It allows custom
filters with ldapsearchprefix and ldapsearchsuffix. Another approach
might be to take a filter pattern with "%USERNAME%" or whatever in it.
There's an existing precedent for the prefix and suffix approach, but
on the other hand a pattern approach would allow filters where the
username is inserted more than once.

Do we even need prefix/suffix? If we just make it "ldapsearchpattern", then
you could have something like:

ldapsearchattribute="uid"
ldapsearchfilter="|(memberof=cn=Paris DBA Team)(memberof=cn=Tokyo DBA Team)"

We could then always to substitution of the kind:
(&(attr=<uid>)(<filter>))

which would in this case give:
(&(uid=mha)(|(memberof=cn=Paris DBA Team)(memberof=cn=Tokyo DBA Team)))

Basically we'd always AND together the username lookup with the additional
filter.

Perhaps there are better ways to organise your LDAP servers so that

this sort of thing isn't necessary. I don't know. Thoughts?

I think something along this way is definitely wanted. We can argue the
syntax, but being able to filter like this is definitely useful.

(FWIW, a workaround I've applied more than once to this in AD environments
(where kerberos for one reason or other can't be done, sorry Stephen) is to
set up a RADIUS server and use that one as a "middle man". But it would be
much better if we could do it natively)

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On Fri, Jul 14, 2017 at 11:04 PM, Magnus Hagander <magnus@hagander.net> wrote:

On Thu, Jul 13, 2017 at 9:31 AM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

A post on planet.postgresql.org today reminded me that a colleague had
asked me to post this POC patch here for discussion. It allows custom
filters with ldapsearchprefix and ldapsearchsuffix. Another approach
might be to take a filter pattern with "%USERNAME%" or whatever in it.
There's an existing precedent for the prefix and suffix approach, but
on the other hand a pattern approach would allow filters where the
username is inserted more than once.

Do we even need prefix/suffix? If we just make it "ldapsearchpattern", then
you could have something like:

ldapsearchattribute="uid"
ldapsearchfilter="|(memberof=cn=Paris DBA Team)(memberof=cn=Tokyo DBA Team)"

We could then always to substitution of the kind:
(&(attr=<uid>)(<filter>))

which would in this case give:
(&(uid=mha)(|(memberof=cn=Paris DBA Team)(memberof=cn=Tokyo DBA Team)))

Basically we'd always AND together the username lookup with the additional
filter.

Ok, so we have 3 ideas put forward:

1. Wrap username with ldapsearchprefix ldapsearchsuffix to build
filter (as implemented by POC patch)
2. Optionally AND ldapsearchfilter with the existing
ldapsearchattribute-based filter (Magnus's proposal)
3. Pattern-based ldapsearchfilter so that %USER% is replaced with
username (my other suggestion)

The main argument for approach 1 is that it follows the style of the
bind-only mode.

With idea 2, I wonder if there are some more general kinds of things
that people might want to do that that wouldn't be possible because it
has to include (attribute=user)... perhaps something involving a
substring or other transformation functions (but I'm no LDAP expert,
that may not make sense).

With idea 3 it would allow "(|(foo=%USER%)(bar=%USER%))", though I
don't know if any such multiple-mention filters would ever make sense
in a sane LDAP configuration.

Any other views from LDAP-users?

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Jul 16, 2017 at 1:08 AM, Thomas Munro <thomas.munro@enterprisedb.com

wrote:

On Fri, Jul 14, 2017 at 11:04 PM, Magnus Hagander <magnus@hagander.net>
wrote:

On Thu, Jul 13, 2017 at 9:31 AM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

A post on planet.postgresql.org today reminded me that a colleague had
asked me to post this POC patch here for discussion. It allows custom
filters with ldapsearchprefix and ldapsearchsuffix. Another approach
might be to take a filter pattern with "%USERNAME%" or whatever in it.
There's an existing precedent for the prefix and suffix approach, but
on the other hand a pattern approach would allow filters where the
username is inserted more than once.

Do we even need prefix/suffix? If we just make it "ldapsearchpattern",

then

you could have something like:

ldapsearchattribute="uid"
ldapsearchfilter="|(memberof=cn=Paris DBA Team)(memberof=cn=Tokyo DBA

Team)"

We could then always to substitution of the kind:
(&(attr=<uid>)(<filter>))

which would in this case give:
(&(uid=mha)(|(memberof=cn=Paris DBA Team)(memberof=cn=Tokyo DBA Team)))

Basically we'd always AND together the username lookup with the

additional

filter.

Ok, so we have 3 ideas put forward:

1. Wrap username with ldapsearchprefix ldapsearchsuffix to build
filter (as implemented by POC patch)
2. Optionally AND ldapsearchfilter with the existing
ldapsearchattribute-based filter (Magnus's proposal)
3. Pattern-based ldapsearchfilter so that %USER% is replaced with
username (my other suggestion)

The main argument for approach 1 is that it follows the style of the
bind-only mode.

Agreed, but I'm not sure it's a good style to follow (and yes, I think I
may be the original author of it..). I'd rank option 3 over option 1.

With idea 2, I wonder if there are some more general kinds of things
that people might want to do that that wouldn't be possible because it
has to include (attribute=user)... perhaps something involving a
substring or other transformation functions (but I'm no LDAP expert,
that may not make sense).

Yeah, that's exactly what I'm wondering about it :)

With idea 3 it would allow "(|(foo=%USER%)(bar=%USER%))", though I
don't know if any such multiple-mention filters would ever make sense
in a sane LDAP configuration.

Any other views from LDAP-users?

+1 for some input from people who directly use it in larger LDAP
environments. If we're going to change how it works, let's make it right
this time!

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On 16/07/17 00:08, Thomas Munro wrote:

On Fri, Jul 14, 2017 at 11:04 PM, Magnus Hagander <magnus@hagander.net> wrote:

On Thu, Jul 13, 2017 at 9:31 AM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

A post on planet.postgresql.org today reminded me that a colleague had
asked me to post this POC patch here for discussion. It allows custom
filters with ldapsearchprefix and ldapsearchsuffix. Another approach
might be to take a filter pattern with "%USERNAME%" or whatever in it.
There's an existing precedent for the prefix and suffix approach, but
on the other hand a pattern approach would allow filters where the
username is inserted more than once.

Do we even need prefix/suffix? If we just make it "ldapsearchpattern", then
you could have something like:

ldapsearchattribute="uid"
ldapsearchfilter="|(memberof=cn=Paris DBA Team)(memberof=cn=Tokyo DBA Team)"

We could then always to substitution of the kind:
(&(attr=<uid>)(<filter>))

which would in this case give:
(&(uid=mha)(|(memberof=cn=Paris DBA Team)(memberof=cn=Tokyo DBA Team)))

Basically we'd always AND together the username lookup with the additional
filter.

Ok, so we have 3 ideas put forward:

1. Wrap username with ldapsearchprefix ldapsearchsuffix to build
filter (as implemented by POC patch)
2. Optionally AND ldapsearchfilter with the existing
ldapsearchattribute-based filter (Magnus's proposal)
3. Pattern-based ldapsearchfilter so that %USER% is replaced with
username (my other suggestion)

The main argument for approach 1 is that it follows the style of the
bind-only mode.

With idea 2, I wonder if there are some more general kinds of things
that people might want to do that that wouldn't be possible because it
has to include (attribute=user)... perhaps something involving a
substring or other transformation functions (but I'm no LDAP expert,
that may not make sense).

With idea 3 it would allow "(|(foo=%USER%)(bar=%USER%))", though I
don't know if any such multiple-mention filters would ever make sense
in a sane LDAP configuration.

Any other views from LDAP-users?

I've spent quite a bit of time integrating various bits of
non-PostgreSQL software to LDAP and in my experience option 3 tends to
be the standard.

Generally you find that you will be given the option to set the
attribute for the default search filter of the form
"(attribute=username)" which defaults to uid for UNIX-type systems and
sAMAccountName for AD. However there is always the ability to specify a
custom filter where the user is substituted via e.g. %u to cover all the
other use-cases.

As an example, I don't know if anyone would actually do this with
PostgreSQL but I've been asked on multiple occasions to configure
software so that users should be allowed to log in with either their
email address or username which is easily done with a custom LDAP filter
like "(|(mail=%u)(uid=%u))".

ATB,

Mark.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Magnus, all,

* Magnus Hagander (magnus@hagander.net) wrote:

(FWIW, a workaround I've applied more than once to this in AD environments
(where kerberos for one reason or other can't be done, sorry Stephen) is to
set up a RADIUS server and use that one as a "middle man". But it would be
much better if we could do it natively)

I'd suggest that we try to understand why Kerberos couldn't be used in
that environment. I suspect in at least some cases what users would
like is the ability to do Kerberos auth but then have LDAP checked to
see if a given user (who has now auth'd through Kerberos) is allowed to
connect. We don't currently have any way to do that, but if we were
looking for things to do, that's what I'd suggest working on rather than
adding more to our LDAP auth system and implying by doing so that it's
reasonable to use.

I find it particularly disappointing to see recommendations for using
LDAP auth, particularly in AD environments, that don't even mention
Kerberos or bother to explain how using LDAP sends the user's PW to the
server in cleartext.

Thanks!

Stephen

On Sun, Jul 16, 2017 at 11:05 PM, Stephen Frost <sfrost@snowman.net> wrote:

Magnus, all,

* Magnus Hagander (magnus@hagander.net) wrote:

(FWIW, a workaround I've applied more than once to this in AD

environments

(where kerberos for one reason or other can't be done, sorry Stephen) is

to

set up a RADIUS server and use that one as a "middle man". But it would

be

much better if we could do it natively)

I'd suggest that we try to understand why Kerberos couldn't be used in
that environment. I suspect in at least some cases what users would
like is the ability to do Kerberos auth but then have LDAP checked to
see if a given user (who has now auth'd through Kerberos) is allowed to
connect. We don't currently have any way to do that, but if we were
looking for things to do, that's what I'd suggest working on rather than
adding more to our LDAP auth system and implying by doing so that it's
reasonable to use.

I find it particularly disappointing to see recommendations for using
LDAP auth, particularly in AD environments, that don't even mention
Kerberos or bother to explain how using LDAP sends the user's PW to the
server in cleartext.

You do realize, I'm sure, that there are many LDAP servers out there that
are not AD, and that do not come with a Kerberos server attached to them...

I agree that Kerberos is usually the better choice *if it's available*.
It's several orders of magnitude more complicated to set up though, and
there are many environments that have ldap but don't have Kerberos.

Refusing to improve LDAP for the users who have no choice seems like a very
unfriendly thing to do.

(And you can actually reasonably solve the case of
kerberos-for-auth-ldap-for-priv by syncing the groups into postgres roles)

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On Mon, Jul 17, 2017 at 5:58 AM, Mark Cave-Ayland
<mark.cave-ayland@ilande.co.uk> wrote:

Any other views from LDAP-users?

I've spent quite a bit of time integrating various bits of
non-PostgreSQL software to LDAP and in my experience option 3 tends to
be the standard.

Generally you find that you will be given the option to set the
attribute for the default search filter of the form
"(attribute=username)" which defaults to uid for UNIX-type systems and
sAMAccountName for AD. However there is always the ability to specify a
custom filter where the user is substituted via e.g. %u to cover all the
other use-cases.

Cool. Here is a new version of the patch updated to do it exactly
like that. I tested it against OpenLDAP.

As an example, I don't know if anyone would actually do this with
PostgreSQL but I've been asked on multiple occasions to configure
software so that users should be allowed to log in with either their
email address or username which is easily done with a custom LDAP filter
like "(|(mail=%u)(uid=%u))".

Thank you very much for this feedback and example, which I used in the
documentation in the patch. I see similar examples in the
documentation for other things on the web.

I'll leave it up to Magnus and Stephen to duke it out over whether we
want to encourage LDAP usage, extend documentation to warn about
cleartext passwords with certain LDAP implementations or
configurations, etc etc. I'll add this patch to the commitfest and
get some popcorn.

--
Thomas Munro
http://www.enterprisedb.com

On 16/07/17 23:26, Thomas Munro wrote:

Thank you very much for this feedback and example, which I used in the
documentation in the patch. I see similar examples in the
documentation for other things on the web.

I'll leave it up to Magnus and Stephen to duke it out over whether we
want to encourage LDAP usage, extend documentation to warn about
cleartext passwords with certain LDAP implementations or
configurations, etc etc. I'll add this patch to the commitfest and
get some popcorn.

If it helps, we normally recommend that clients use ldaps for both AD
and UNIX environments, although this can be trickier from an
administrative perspective in AD environments because it can require
changes to the Windows firewall and certificate installation.

Whilst OpenLDAP will support ldap+starttls you can end up with some
clients with starttls either disabled or misconfigured sending plaintext
passwords over the wire regardless, so it's generally easiest to
firewall ldap port 389 at the edge of the trusted VLAN so that only
ldaps port 636 connections make it out onto the untrusted network
hosting the local AD/OpenLDAP server.

ATB,

Mark.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jul 17, 2017 at 10:26 AM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

ldap-search-filters-v2.patch

Gah, it would help if I could spell "occurrences" correctly. Fixed in
the attached.

--
Thomas Munro
http://www.enterprisedb.com

Mark,

* Mark Cave-Ayland (mark.cave-ayland@ilande.co.uk) wrote:

On 16/07/17 23:26, Thomas Munro wrote:

Thank you very much for this feedback and example, which I used in the
documentation in the patch. I see similar examples in the
documentation for other things on the web.

I'll leave it up to Magnus and Stephen to duke it out over whether we
want to encourage LDAP usage, extend documentation to warn about
cleartext passwords with certain LDAP implementations or
configurations, etc etc. I'll add this patch to the commitfest and
get some popcorn.

If it helps, we normally recommend that clients use ldaps for both AD
and UNIX environments, although this can be trickier from an
administrative perspective in AD environments because it can require
changes to the Windows firewall and certificate installation.

LDAPS is better than straight LDAP, of course, but it still doesn't
address the issue that the password is sent to the server, which both
SCRAM and Kerberos do and is why AD environments use Kerberos for
authentication, and why everything in an AD environment also should use
Kerberos.

Using Kerberos should also avoid the need to hack the Windows firewall
or deal with certificate installation. In an AD environment, it's
actually pretty straight-forward to add a PG server too. Further, in my
experience at least, there's been other changes recommended by Microsoft
that prevent using LDAP for auth because it's insecure.

Thanks!

Stephen

Magnus,

* Magnus Hagander (magnus@hagander.net) wrote:

On Sun, Jul 16, 2017 at 11:05 PM, Stephen Frost <sfrost@snowman.net> wrote:

I'd suggest that we try to understand why Kerberos couldn't be used in
that environment. I suspect in at least some cases what users would
like is the ability to do Kerberos auth but then have LDAP checked to
see if a given user (who has now auth'd through Kerberos) is allowed to
connect. We don't currently have any way to do that, but if we were
looking for things to do, that's what I'd suggest working on rather than
adding more to our LDAP auth system and implying by doing so that it's
reasonable to use.

I find it particularly disappointing to see recommendations for using
LDAP auth, particularly in AD environments, that don't even mention
Kerberos or bother to explain how using LDAP sends the user's PW to the
server in cleartext.

You do realize, I'm sure, that there are many LDAP servers out there that
are not AD, and that do not come with a Kerberos server attached to them...

I am aware that some exist, I've even contributed to their development
and packaging, but that doesn't make it a good idea to use them for
authentication.

I agree that Kerberos is usually the better choice *if it's available*.

Which is the case in an AD environment..

It's several orders of magnitude more complicated to set up though, and
there are many environments that have ldap but don't have Kerberos.

Frankly, I simply don't agree with this.

Refusing to improve LDAP for the users who have no choice seems like a very
unfriendly thing to do.

I'm fine with improving LDAP in general, but, as I tried to point out,
having a way to make it easier to integrate PG into an AD environment
would be better. It's not my intent to stop this patch but rather to
point out the issues with LDAP auth that far too frequently are not
properly understood.

(And you can actually reasonably solve the case of
kerberos-for-auth-ldap-for-priv by syncing the groups into postgres roles)

Yes, but sync'ing roles is a bit of a pain and it'd be nice if we could
avoid it, or perhaps make it easier.

Thanks!

Stephen

On 17/07/17 00:14, Stephen Frost wrote:

If it helps, we normally recommend that clients use ldaps for both AD
and UNIX environments, although this can be trickier from an
administrative perspective in AD environments because it can require
changes to the Windows firewall and certificate installation.

LDAPS is better than straight LDAP, of course, but it still doesn't
address the issue that the password is sent to the server, which both
SCRAM and Kerberos do and is why AD environments use Kerberos for
authentication, and why everything in an AD environment also should use
Kerberos.

Using Kerberos should also avoid the need to hack the Windows firewall
or deal with certificate installation. In an AD environment, it's
actually pretty straight-forward to add a PG server too. Further, in my
experience at least, there's been other changes recommended by Microsoft
that prevent using LDAP for auth because it's insecure.

Oh sure - I'm not questioning that Kerberos is a far better choice in
pure AD environments, it's just that I spend the majority of my time in
mixed-mode environments where Windows is very much in the minority.

In my experience LDAP is often implemented badly; for example the
majority of software still uses simple binds (i.e. plain logins) rather
than using SASL binds which support a whole range of better
authentication methods (e.g. GSSAPI, and even DIGEST-MD5 has been
mandatory for v3 and is implemented on AD).

And yes, while better authentication mechanisms do exist, I find that
all too often most software packages claim LDAP support rather than
Kerberos, and even then it is often limited to LDAP simple binds without
ldaps support.

ATB,

Mark.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Jul 16, 2017 at 7:58 PM, Mark Cave-Ayland <
mark.cave-ayland@ilande.co.uk> wrote:

On 16/07/17 00:08, Thomas Munro wrote:

On Fri, Jul 14, 2017 at 11:04 PM, Magnus Hagander <magnus@hagander.net>

wrote:

On Thu, Jul 13, 2017 at 9:31 AM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

A post on planet.postgresql.org today reminded me that a colleague had
asked me to post this POC patch here for discussion. It allows custom
filters with ldapsearchprefix and ldapsearchsuffix. Another approach
might be to take a filter pattern with "%USERNAME%" or whatever in it.
There's an existing precedent for the prefix and suffix approach, but
on the other hand a pattern approach would allow filters where the
username is inserted more than once.

Do we even need prefix/suffix? If we just make it "ldapsearchpattern",

then

you could have something like:

ldapsearchattribute="uid"
ldapsearchfilter="|(memberof=cn=Paris DBA Team)(memberof=cn=Tokyo DBA

Team)"

We could then always to substitution of the kind:
(&(attr=<uid>)(<filter>))

which would in this case give:
(&(uid=mha)(|(memberof=cn=Paris DBA Team)(memberof=cn=Tokyo DBA Team)))

Basically we'd always AND together the username lookup with the

additional

filter.

Ok, so we have 3 ideas put forward:

1. Wrap username with ldapsearchprefix ldapsearchsuffix to build
filter (as implemented by POC patch)
2. Optionally AND ldapsearchfilter with the existing
ldapsearchattribute-based filter (Magnus's proposal)
3. Pattern-based ldapsearchfilter so that %USER% is replaced with
username (my other suggestion)

The main argument for approach 1 is that it follows the style of the
bind-only mode.

With idea 2, I wonder if there are some more general kinds of things
that people might want to do that that wouldn't be possible because it
has to include (attribute=user)... perhaps something involving a
substring or other transformation functions (but I'm no LDAP expert,
that may not make sense).

With idea 3 it would allow "(|(foo=%USER%)(bar=%USER%))", though I
don't know if any such multiple-mention filters would ever make sense
in a sane LDAP configuration.

Any other views from LDAP-users?

I've spent quite a bit of time integrating various bits of
non-PostgreSQL software to LDAP and in my experience option 3 tends to
be the standard.

Generally you find that you will be given the option to set the
attribute for the default search filter of the form
"(attribute=username)" which defaults to uid for UNIX-type systems and
sAMAccountName for AD. However there is always the ability to specify a
custom filter where the user is substituted via e.g. %u to cover all the
other use-cases.

Right, but that's something we do already today. It just defaults to uid,
but it's easy to change.

As an example, I don't know if anyone would actually do this with
PostgreSQL but I've been asked on multiple occasions to configure
software so that users should be allowed to log in with either their
email address or username which is easily done with a custom LDAP filter
like "(|(mail=%u)(uid=%u))".

How would that actually work, though? Given the same user in ldap could now
potentially match multiple different users in PostgreSQL. Would you then
create two accounts for the user, one with the uid as name and one with
email as name? Wouldn't that actually cause more issues than it solves?

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On Mon, Jul 17, 2017 at 1:23 AM, Stephen Frost <sfrost@snowman.net> wrote:

* Magnus Hagander (magnus@hagander.net) wrote:

On Sun, Jul 16, 2017 at 11:05 PM, Stephen Frost <sfrost@snowman.net>

wrote:

I'd suggest that we try to understand why Kerberos couldn't be used in
that environment. I suspect in at least some cases what users would
like is the ability to do Kerberos auth but then have LDAP checked to
see if a given user (who has now auth'd through Kerberos) is allowed to
connect. We don't currently have any way to do that, but if we were
looking for things to do, that's what I'd suggest working on rather

than

adding more to our LDAP auth system and implying by doing so that it's
reasonable to use.

I find it particularly disappointing to see recommendations for using
LDAP auth, particularly in AD environments, that don't even mention
Kerberos or bother to explain how using LDAP sends the user's PW to the
server in cleartext.

You do realize, I'm sure, that there are many LDAP servers out there that
are not AD, and that do not come with a Kerberos server attached to

them...

I am aware that some exist, I've even contributed to their development
and packaging, but that doesn't make it a good idea to use them for
authentication.

Pretty sure that doesn't include any of the ones I'm talking about, but
sure :)

I agree that Kerberos is usually the better choice *if it's available*.

Which is the case in an AD environment..

Yes. But we shouldn't force everybody to use AD :P

It's several orders of magnitude more complicated to set up though, and
there are many environments that have ldap but don't have Kerberos.

Frankly, I simply don't agree with this.

Really?

For LDAP auth I don't need to do *anything* in preparation. For Kerberos
auth I need to create an account, set encryption type, export keys, etc.
You can't possibly claim this is the same level of complexity?

Refusing to improve LDAP for the users who have no choice seems like a

very

unfriendly thing to do.

I'm fine with improving LDAP in general, but, as I tried to point out,
having a way to make it easier to integrate PG into an AD environment
would be better. It's not my intent to stop this patch but rather to
point out the issues with LDAP auth that far too frequently are not
properly understood.

A documentation patch for that would certainly be a good place to start.
Perhaps with up to date instructions for how to actually set up Kerberos in
an AD environment including all steps required?

(And you can actually reasonably solve the case of
kerberos-for-auth-ldap-for-priv by syncing the groups into postgres

roles)

Yes, but sync'ing roles is a bit of a pain and it'd be nice if we could
avoid it, or perhaps make it easier.

Certainly.

//Magnus

On 17/07/17 13:09, Magnus Hagander wrote:

Hi Magnus,

Great to hear from you! It has definitely been a while...

Generally you find that you will be given the option to set the
attribute for the default search filter of the form
"(attribute=username)" which defaults to uid for UNIX-type systems and
sAMAccountName for AD. However there is always the ability to specify a
custom filter where the user is substituted via e.g. %u to cover all the
other use-cases.

Right, but that's something we do already today. It just defaults to
uid, but it's easy to change.

Yes, I think that bit is fine as long as the default can be overridden.
There's always a choice as to whether the defaults favour a POSIX-based
LDAP or AD environment but that happens with all installations so it's
not a big deal.

As an example, I don't know if anyone would actually do this with
PostgreSQL but I've been asked on multiple occasions to configure
software so that users should be allowed to log in with either their
email address or username which is easily done with a custom LDAP filter
like "(|(mail=%u)(uid=%u))".

How would that actually work, though? Given the same user in ldap could
now potentially match multiple different users in PostgreSQL. Would you
then create two accounts for the user, one with the uid as name and one
with email as name? Wouldn't that actually cause more issues than it solves?

Normally what happens for search+bind is that you execute the custom
filter with substitutions in order to find the entry for your bind DN,
but you also request the value of ldapsearchattribute (or equivalent) at
the same time. Say for example you had an entry like this:

dn: uid=mha,dc=users,dc=hagander,dc=net
uid: mha
mail: magnus@hagander.net

Using the filter "(|(mail=%u)(uid=%u))" would locate the same bind DN
"uid=mha,dc=users,dc=hagander,dc=net" with either one of your uid or
email address.

If the bind is successful then the current user identity should be set
to the value of the ldapsearchattribute retrieved from the bind DN
entry, which with the default of "uid" would be "mha". Hence you end up
with the same user role "mha" regardless of whether a uid or email
address was entered.

In terms of matching multiple users, all LDAP authentication routines
I've seen will fail if more than one DN matches the search filter, so
this nicely handles the cases where either the custom filter is
incorrect or more than one entry is accidentally matched in the directory.

ATB,

Mark.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jul 17, 2017 at 6:47 PM, Mark Cave-Ayland <
mark.cave-ayland@ilande.co.uk> wrote:

On 17/07/17 13:09, Magnus Hagander wrote:

Hi Magnus,

Great to hear from you! It has definitely been a while...

Indeed. You should spend more time on these lists :P

Generally you find that you will be given the option to set the
attribute for the default search filter of the form
"(attribute=username)" which defaults to uid for UNIX-type systems

and

sAMAccountName for AD. However there is always the ability to

specify a

custom filter where the user is substituted via e.g. %u to cover all

the

other use-cases.

Right, but that's something we do already today. It just defaults to
uid, but it's easy to change.

Yes, I think that bit is fine as long as the default can be overridden.
There's always a choice as to whether the defaults favour a POSIX-based
LDAP or AD environment but that happens with all installations so it's
not a big deal.

It's easy enough to change.

As an example, I don't know if anyone would actually do this with
PostgreSQL but I've been asked on multiple occasions to configure
software so that users should be allowed to log in with either their
email address or username which is easily done with a custom LDAP

filter

like "(|(mail=%u)(uid=%u))".

How would that actually work, though? Given the same user in ldap could
now potentially match multiple different users in PostgreSQL. Would you
then create two accounts for the user, one with the uid as name and one
with email as name? Wouldn't that actually cause more issues than it

solves?

Normally what happens for search+bind is that you execute the custom
filter with substitutions in order to find the entry for your bind DN,
but you also request the value of ldapsearchattribute (or equivalent) at
the same time. Say for example you had an entry like this:

dn: uid=mha,dc=users,dc=hagander,dc=net
uid: mha
mail: magnus@hagander.net

Using the filter "(|(mail=%u)(uid=%u))" would locate the same bind DN
"uid=mha,dc=users,dc=hagander,dc=net" with either one of your uid or
email address.

If the bind is successful then the current user identity should be set
to the value of the ldapsearchattribute retrieved from the bind DN
entry, which with the default of "uid" would be "mha". Hence you end up
with the same user role "mha" regardless of whether a uid or email
address was entered.

Right. This is the part that doesn't work for PostgreSQL. Because we have
already specified the username -- it goes in the startup packet in order to
pick the correct row from pg_hba.conf.

I guess in theory we could treat it like Kerberos or another one of the
systems where we get the username from an external entity. But then you'd
still have to specify the mapping again in pg_ident.conf, and it would be a
pretty strong break of backwards compatibility.

In terms of matching multiple users, all LDAP authentication routines
I've seen will fail if more than one DN matches the search filter, so
this nicely handles the cases where either the custom filter is
incorrect or more than one entry is accidentally matched in the directory.

So do we, in the current implementation. But it's a lot less likely to
happen in the current implementation, since we do a single equals lookup.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On 17/07/17 18:08, Magnus Hagander wrote:

On Mon, Jul 17, 2017 at 6:47 PM, Mark Cave-Ayland
<mark.cave-ayland@ilande.co.uk <mailto:mark.cave-ayland@ilande.co.uk>>
wrote:
Great to hear from you! It has definitely been a while...

Indeed. You should spend more time on these lists :P

Well I do get the emails, unfortunately it's trying to find the time to
read them all ;)

How would that actually work, though? Given the same user in ldap could
now potentially match multiple different users in PostgreSQL. Would you
then create two accounts for the user, one with the uid as name and one
with email as name? Wouldn't that actually cause more issues than it solves?

Normally what happens for search+bind is that you execute the custom
filter with substitutions in order to find the entry for your bind DN,
but you also request the value of ldapsearchattribute (or equivalent) at
the same time. Say for example you had an entry like this:

dn: uid=mha,dc=users,dc=hagander,dc=net
uid: mha
mail: magnus@hagander.net <mailto:magnus@hagander.net>

Using the filter "(|(mail=%u)(uid=%u))" would locate the same bind DN
"uid=mha,dc=users,dc=hagander,dc=net" with either one of your uid or
email address.

If the bind is successful then the current user identity should be set
to the value of the ldapsearchattribute retrieved from the bind DN
entry, which with the default of "uid" would be "mha". Hence you end up
with the same user role "mha" regardless of whether a uid or email
address was entered.

Right. This is the part that doesn't work for PostgreSQL. Because we
have already specified the username -- it goes in the startup packet in
order to pick the correct row from pg_hba.conf.

I don't think that's necessarily going to be an issue here because if
you're specifying a custom LDAP filter then there's a very good chance
that you're delegating access control to information held in the
directory anyway, e.g.

(&(memberOf=cn=pgusers,dc=groups,dc=hagander,dc=net)(uid=%u))

((&(uid=%u)(|(uid=mha)(uid=mark)(uid=thomas)))

In the mail example above when you're using more than one attribute, I
think it's fair enough to simply say in the documentation you must set
user to "all" in pg_hba.conf since it is impossible to know which
attribute is being used to identify the user role until after
authentication.

I should add that personally I don't recommend such setups where the
user can log in using more than one identifier, but there are clients
out there who absolutely will insist on it (think internal vs. external
users) so if the LDAP support is being updated then it's worth exploring
to see if these cases can be supported.

I guess in theory we could treat it like Kerberos or another one of the
systems where we get the username from an external entity. But then
you'd still have to specify the mapping again in pg_ident.conf, and it
would be a pretty strong break of backwards compatibility.

(goes and glances at the code)

Is there no reason why you couldn't just overwrite port->user_name based
upon ldapsearchattribute at the end of CheckLDAPAuth?

And if this were only enabled when a custom filter were specified then
it shouldn't cause any backwards compatibility issues being a new feature?

In terms of matching multiple users, all LDAP authentication routines
I've seen will fail if more than one DN matches the search filter, so
this nicely handles the cases where either the custom filter is
incorrect or more than one entry is accidentally matched in the
directory.

So do we, in the current implementation. But it's a lot less likely to
happen in the current implementation, since we do a single equals lookup.

Great, that's absolutely fine :)

ATB,

Mark.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Jul 16, 2017 at 7:23 PM, Stephen Frost <sfrost@snowman.net> wrote:

Refusing to improve LDAP for the users who have no choice seems like a very
unfriendly thing to do.

I'm fine with improving LDAP in general, but, as I tried to point out,
having a way to make it easier to integrate PG into an AD environment
would be better. It's not my intent to stop this patch but rather to
point out the issues with LDAP auth that far too frequently are not
properly understood.

Then it's off-topic for this thread.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/16/17 19:09, Thomas Munro wrote:

On Mon, Jul 17, 2017 at 10:26 AM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

ldap-search-filters-v2.patch

Gah, it would help if I could spell "occurrences" correctly. Fixed in
the attached.

Please also add the corresponding support for specifying search filters
in LDAP URLs. See RFC 4516 for the format and
https://linux.die.net/man/3/ldap_url_parse for the API. You might just
need to grab LDAPURLDesc.lud_filter and use it.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers