Thread: Using more than one LDAP?
Hi, can I use more than one LDAP server entry in pg_hba.conf? My tests show that only the first one is used. Example: ldap1.domain contains user1, user2 ldap2.another.domain contains user3, user4 All 4 users have an account in a PostgreSQL cluster. ldap1 is openLDAP and ldap2 is Windows AD. Both naturally have different parameters/options and as such require two differentlines in pg_hba.conf. If I connect as user1 or user2, it works. If I try to connect as user3 or user4, it fails because ldap1 reports the useras non-existent, which is correct for ldap1. But in this case, ldap2 is never asked. How can I solve this dilemma? Cheers, Paul
On Wed, Jan 6, 2021 at 3:38 PM Paul Förster <paul.foerster@gmail.com> wrote: > > Hi, > > can I use more than one LDAP server entry in pg_hba.conf? My tests show that only the first one is used. > > Example: > > ldap1.domain contains user1, user2 > ldap2.another.domain contains user3, user4 > > All 4 users have an account in a PostgreSQL cluster. > > ldap1 is openLDAP and ldap2 is Windows AD. Both naturally have different parameters/options and as such require two differentlines in pg_hba.conf. > > If I connect as user1 or user2, it works. If I try to connect as user3 or user4, it fails because ldap1 reports the useras non-existent, which is correct for ldap1. But in this case, ldap2 is never asked. > > How can I solve this dilemma? Only if you can create rules in your pg_hba.conf file that knows where the users are. You can specify multiple servers on one line, but that only balances across servers that don't work. If a server replies "no" to a response, PostgreSQL will not move on to the next one. So you have to make it initially pick the correct rule. And what would you do if user5 exists in both the two ldap servers? One hacky way you could do it is create a group role for each server, maintained by some cron job, that indicates with LDAP server the user is on. You can then use group matching to pick the correct rule in pg_hba. It's kind of an ugly hack though.. You'd probably be better off to have a federated ldap server that has a view of both servers, and use that. Or even better, since one of your nodes is AD, it speaks Kerberos. Setting up a Kerberos trust between the two environments would make it possible to do things like regexp matching on the realm in pg_ident.conf, and as a bonus you get Kerberos which is a lot more secure than ldap for auth.. It might have a slightly higher barrier of entry, but could probably pay off well in a case like this. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
Hi Magnus, > On 06. Jan, 2021, at 15:48, Magnus Hagander <magnus@hagander.net> wrote: > > Only if you can create rules in your pg_hba.conf file that knows where > the users are. You can specify multiple servers on one line, but that > only balances across servers that don't work. If a server replies "no" > to a response, PostgreSQL will not move on to the next one. So you > have to make it initially pick the correct rule. that unfortunately is not an option, partly because LDAP and AD use different options and also, as you already mentionedit, if one server says no, it's no. > And what would you do if user5 exists in both the two ldap servers? that wouldn't matter as long as user5 exists on the database and can be authenticated by either LDAP. > One hacky way you could do it is create a group role for each server, > maintained by some cron job, that indicates with LDAP server the user > is on. You can then use group matching to pick the correct rule in > pg_hba. It's kind of an ugly hack though.. that sounds really hacky. ;-) > You'd probably be better off to have a federated ldap server that has > a view of both servers, and use that. can't do that either. I have no control over both LDAP services. PostgreSQL is just a consumer and I can't make any of thetwo LDAPs to sync onto each other. > Or even better, since one of your nodes is AD, it speaks Kerberos. > Setting up a Kerberos trust between the two environments would make it > possible to do things like regexp matching on the realm in > pg_ident.conf, and as a bonus you get Kerberos which is a lot more > secure than ldap for auth.. It might have a slightly higher barrier > of entry, but could probably pay off well in a case like this. that'd require me to recompile and redistribute the PostgreSQL software. I only have openLDAP compiled into it but no GSSAPI.While this could be possible, it would also mean service interruption, almost not possible in a 24x7 environment.Also, and I'm no expert on this, it would require me to get certificates and configure them, and so on, right? I thought of a pg_ident.conf configuration. In fact, it's more of a prefix change. The complete situation is like this: ldap1 knows aaa-u1, aaa-u2, and so on ldap2 knows bbb-u1, bbb-u2, and so on So, I thought, I could create a pg_ident.conf like this: mymap /^aaa-(.*)$ bbb-\1 Then pg_ctl reload of course. But that doesn't seem to work. Maybe I'm trying something wrong here. Cheers, Paul
On Wed, Jan 6, 2021 at 4:39 PM Paul Förster <paul.foerster@gmail.com> wrote: > > Hi Magnus, > > > On 06. Jan, 2021, at 15:48, Magnus Hagander <magnus@hagander.net> wrote: > > > > Only if you can create rules in your pg_hba.conf file that knows where > > the users are. You can specify multiple servers on one line, but that > > only balances across servers that don't work. If a server replies "no" > > to a response, PostgreSQL will not move on to the next one. So you > > have to make it initially pick the correct rule. > > that unfortunately is not an option, partly because LDAP and AD use different options and also, as you already mentionedit, if one server says no, it's no. > > > And what would you do if user5 exists in both the two ldap servers? > > that wouldn't matter as long as user5 exists on the database and can be authenticated by either LDAP. > > > One hacky way you could do it is create a group role for each server, > > maintained by some cron job, that indicates with LDAP server the user > > is on. You can then use group matching to pick the correct rule in > > pg_hba. It's kind of an ugly hack though.. > > that sounds really hacky. ;-) Yes. But you have a really hacky environment :P > > You'd probably be better off to have a federated ldap server that has > > a view of both servers, and use that. > > can't do that either. I have no control over both LDAP services. PostgreSQL is just a consumer and I can't make any ofthe two LDAPs to sync onto each other. You could have a third LDAP instance that federates the other two. Another option could be to proxy it through something like FreeRADIUS. I'm fairly certain it can also move on to a secondary server if the first one reports login failure. > > Or even better, since one of your nodes is AD, it speaks Kerberos. > > Setting up a Kerberos trust between the two environments would make it > > possible to do things like regexp matching on the realm in > > pg_ident.conf, and as a bonus you get Kerberos which is a lot more > > secure than ldap for auth.. It might have a slightly higher barrier > > of entry, but could probably pay off well in a case like this. > > that'd require me to recompile and redistribute the PostgreSQL software. I only have openLDAP compiled into it but no GSSAPI.While this could be possible, it would also mean service interruption, almost not possible in a 24x7 environment.Also, and I'm no expert on this, it would require me to get certificates and configure them, and so on, right? I assume you're not using any of the standard packagings then, as I believe they all come with support for GSSAPI. Yet another reason why it's a good idea to use that :) And no, gssapi does not use certificates. > I thought of a pg_ident.conf configuration. In fact, it's more of a prefix change. The complete situation is like this: > > ldap1 knows aaa-u1, aaa-u2, and so on > ldap2 knows bbb-u1, bbb-u2, and so on > > So, I thought, I could create a pg_ident.conf like this: > > mymap /^aaa-(.*)$ bbb-\1 > > Then pg_ctl reload of course. But that doesn't seem to work. Maybe I'm trying something wrong here. pg_ident only works for authentication methods where the username comes from the other system, such as with Kerberos. It does not work for LDAP, where the username is specified in PostgreSQL. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
Hi Magnus, > On 06. Jan, 2021, at 16:57, Magnus Hagander <magnus@hagander.net> wrote: > > Yes. But you have a really hacky environment :P actually not. We have an old LDAP which we want to retire this year. And we also have Windows AD, which offers LDAP. So theidea is to switch the LDAP environments in PostgreSQL. The old LDAP uses aaa-u1, aaa-u2, etc. which are also accountsin the database. But our Windows AD has bbb-u1, bbb-u2, etc. So just switching LDAPs doesn't work. I'd also haveto rename all users. Though it's just a one-liner, it would mean that users have to use their new names from one secondto the next. But we want a transition phase if that's possible. > You could have a third LDAP instance that federates the other two. > > Another option could be to proxy it through something like FreeRADIUS. > I'm fairly certain it can also move on to a secondary server if the > first one reports login failure. I can't. I'm no sysadmin and have no rights on systems to install anything except the PostgreSQL software. Also, the networkguys wouldn't be too happy. And then, there is a problem introducing new software, which is possible, but can takemonths for us to get the necessary permissions. > I assume you're not using any of the standard packagings then, as I > believe they all come with support for GSSAPI. Yet another reason why > it's a good idea to use that :) no, we always compile from source and only what we need. I can build packages with GSSAPI compiled into it but it does requireme do have a small service interruption if I install packages with the same PostgreSQL version number, a situation,which I'd like to avoid, if possible. > And no, gssapi does not use certificates. that's good news as I'm not really happy about all that certificate stuff. ;-) > pg_ident only works for authentication methods where the username > comes from the other system, such as with Kerberos. It does not work > for LDAP, where the username is specified in PostgreSQL. I don' understand that. The doc says it should work for all external authentication services. Maybe I misread something?... Cheers, Paul
On Thu, Jan 7, 2021 at 10:40 AM Paul Förster <paul.foerster@gmail.com> wrote: > > Hi Magnus, > > > On 06. Jan, 2021, at 16:57, Magnus Hagander <magnus@hagander.net> wrote: > > > > Yes. But you have a really hacky environment :P > > actually not. We have an old LDAP which we want to retire this year. And we also have Windows AD, which offers LDAP. Sothe idea is to switch the LDAP environments in PostgreSQL. The old LDAP uses aaa-u1, aaa-u2, etc. which are also accountsin the database. But our Windows AD has bbb-u1, bbb-u2, etc. So just switching LDAPs doesn't work. I'd also haveto rename all users. Though it's just a one-liner, it would mean that users have to use their new names from one secondto the next. But we want a transition phase if that's possible. > > > You could have a third LDAP instance that federates the other two. > > > > Another option could be to proxy it through something like FreeRADIUS. > > I'm fairly certain it can also move on to a secondary server if the > > first one reports login failure. > > I can't. I'm no sysadmin and have no rights on systems to install anything except the PostgreSQL software. Also, the networkguys wouldn't be too happy. And then, there is a problem introducing new software, which is possible, but can takemonths for us to get the necessary permissions. This would be the hacky part of the environment: ) > > I assume you're not using any of the standard packagings then, as I > > believe they all come with support for GSSAPI. Yet another reason why > > it's a good idea to use that :) > > no, we always compile from source and only what we need. I can build packages with GSSAPI compiled into it but it doesrequire me do have a small service interruption if I install packages with the same PostgreSQL version number, a situation,which I'd like to avoid, if possible. And this would be the second hacky part of the environment :) > > And no, gssapi does not use certificates. > > that's good news as I'm not really happy about all that certificate stuff. ;-) > > > pg_ident only works for authentication methods where the username > > comes from the other system, such as with Kerberos. It does not work > > for LDAP, where the username is specified in PostgreSQL. > > I don' understand that. The doc says it should work for all external authentication services. Maybe I misread something?... The docs say "When using an external authentication system such as Ident or GSSAPI, the name of the operating system user that initiated the connection might not be the same as the database user (role) that is to be used." I think that's a bit of a left-over to when it was really just ident. First of all it should probably say peer rather than ident, and it's not actually operating systems that are relevant here. So I can understand you getting ab it confused by that. but the property that matter is where the username comes from. In GSSAPI, or peer, or certificate, etc, the username is provided by the external system, and the mapping is applied *after* that. With LDAP authentication, the username is provided by the client, and is then passed to the external system. Mapping applies *after* the authentication, which inthe case of LDAP would be too late to make any difference. The references to "unix user" and "operating system users" are probably a leftover from the old days and actually contribute to some of the confusion I think. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
Hi Magnus, > On 07. Jan, 2021, at 12:43, Magnus Hagander <magnus@hagander.net> wrote: > > The docs say "When using an external authentication system such as > Ident or GSSAPI, the name of the operating system user that initiated > the connection might not be the same as the database user (role) that > is to be used." > > I think that's a bit of a left-over to when it was really just ident. > First of all it should probably say peer rather than ident, and it's > not actually operating systems that are relevant here. > > So I can understand you getting ab it confused by that. but the > property that matter is where the username comes from. In GSSAPI, or > peer, or certificate, etc, the username is provided by the external > system, and the mapping is applied *after* that. > > With LDAP authentication, the username is provided by the client, and > is then passed to the external system. > > Mapping applies *after* the authentication, which inthe case of LDAP > would be too late to make any difference. > > The references to "unix user" and "operating system users" are > probably a leftover from the old days and actually contribute to some > of the confusion I think. that explains it. The use case in our company is: Developers connect with tools like DbVisualizer or SQL Developer (Oracle using the nastyPostgreSQL Hack :-( ) providing their username via JDBC to the database. Developers work on Windows, the databases runon Linux (SLES) and the AD obviously runs on Windows. Ok, since LDAP doesn't work that way, I either need to build GSSAPI packages and have the AD admins to provide me with thekeytab file or make the transition a "hard" one, i.e. no transition phase. Though I'd rather have liked to see a transitionphase where either account could have been used I personally can live with that. It's the developers who will haveto change quickly, not me. ;-) Cheers, Paul
Greetings, * Paul Förster (paul.foerster@gmail.com) wrote: > Ok, since LDAP doesn't work that way, I either need to build GSSAPI packages and have the AD admins to provide me withthe keytab file or make the transition a "hard" one, i.e. no transition phase. Though I'd rather have liked to see atransition phase where either account could have been used I personally can live with that. It's the developers who willhave to change quickly, not me. ;-) Done correctly, the developers will hopefully be going from "this stupid thing prompts me to provide a username/password in order to log in" to "no more prompt for logging in, it just *works*". Further, as Magnus explained, you could actually have the mapping to allow user X to log in by providing GSSAPI credentials Y, if they are actually still going to be including some username in their connection request to PG (even though they shouldn't need to, since it'll be the same between their local Windows/AD login and the GSSAPI user that PG will see). You should be able to make both work concurrently thanks to pg_ident.conf. Thanks, Stephen
Attachment
Hi Stephen, > On 08. Jan, 2021, at 22:59, Stephen Frost <sfrost@snowman.net> wrote: > > Done correctly, the developers will hopefully be going from "this stupid > thing prompts me to provide a username/password in order to log in" to > "no more prompt for logging in, it just *works*". Further, as Magnus > explained, you could actually have the mapping to allow user X to log in > by providing GSSAPI credentials Y, if they are actually still going to > be including some username in their connection request to PG (even > though they shouldn't need to, since it'll be the same between their > local Windows/AD login and the GSSAPI user that PG will see). You > should be able to make both work concurrently thanks to pg_ident.conf. I agree. But the company policy is to have users being asked each time they want to login somewhere, no matter where. Weneed to use an RSA tamagotchi at least twice to even get somewhere close to being able to launch a tool like DbVisualizeror SQL Developer. If we want a shell on a server, we need to use the tamagotchi even one more time. And then, for such tools, or in fact anything, "no more prompt" unfortunately is just no option. Some call that security,I call that paranoia. This is why I don't care whether GSSAPI is more secure than LDAPS. The whole environment isstuffed inside some network zone which is stuffed into another network zone, then divided into again some other networkzones inside, etc. LDAP and AD are in separate zones than the databases, developer's and admin's machines are againin some other network zone. Even some databases have their own network zones. You get the picture... The best thingis: they still call this single sign on because you get to use the same username everywhere. rotfl From the network perspective, Magnus is right. We have a hacky environment. But architecture is not something I have an influenceon. Cheers, Paul