Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
On Wed, Jul 22, 2009 at 8:57 AM, Magnus Hagander wrote: > On Wed, Jul 22, 2009 at 14:53, Tom Lane wrote: >> Magnus Hagander writes: Yup, you would need a protocol change that would allow the client to change its mind about what the username was after it got the auth challenge. And then what effects does that have on username-sensitive pg_hba.conf decisions? We go back and change our minds about the challenge type, perhaps? The whole thing seems like a nonstarter to me. >> >>> "challenge type"? Not sure I understand what you are referring to here. >> >> The point is that pg_hba.conf allows the selection of auth method to >> depend on username. What happens if, after being told auth method is >> (say) Kerberos, the client comes back and wants to use a different >> username that should have resulted in a different auth method according >> to pg_hba.conf? It's not hard to construct scenarios where that would >> be seen as a security breach. > > Oh. Now I get it. Good point. Forgot about the username being part of > that. Yeah, that basicalliy says it has to be a client-side > implementation only. I believe this means that this patch is rejected, so I am marking it as such on commitfest.postgresql.org. However, it sounds like there would be room for a client-side patch offering functionality in this area, if Lars or someone else wanted to develop such a thing for a future CommitFest. Hopefully I've understood the situation correctly... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
On Wed, Jul 22, 2009 at 14:53, Tom Lane wrote: > Magnus Hagander writes: >>> Yup, you would need a protocol change that would allow the client to >>> change its mind about what the username was after it got the auth >>> challenge. And then what effects does that have on username-sensitive >>> pg_hba.conf decisions? We go back and change our minds about the >>> challenge type, perhaps? The whole thing seems like a nonstarter to me. > >> "challenge type"? Not sure I understand what you are referring to here. > > The point is that pg_hba.conf allows the selection of auth method to > depend on username. What happens if, after being told auth method is > (say) Kerberos, the client comes back and wants to use a different > username that should have resulted in a different auth method according > to pg_hba.conf? It's not hard to construct scenarios where that would > be seen as a security breach. Oh. Now I get it. Good point. Forgot about the username being part of that. Yeah, that basicalliy says it has to be a client-side implementation only. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Magnus Hagander writes: >> Yup, you would need a protocol change that would allow the client to >> change its mind about what the username was after it got the auth >> challenge. And then what effects does that have on username-sensitive >> pg_hba.conf decisions? We go back and change our minds about the >> challenge type, perhaps? The whole thing seems like a nonstarter to me. > "challenge type"? Not sure I understand what you are referring to here. The point is that pg_hba.conf allows the selection of auth method to depend on username. What happens if, after being told auth method is (say) Kerberos, the client comes back and wants to use a different username that should have resulted in a different auth method according to pg_hba.conf? It's not hard to construct scenarios where that would be seen as a security breach. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
On Tue, Jul 21, 2009 at 16:06, Tom Lane wrote: > Magnus Hagander writes: >> On Tue, Jul 21, 2009 at 15:58, Tom Lane wrote: >>> Are you not describing a behavior that you yourself removed in 8.4, >>> ie the libpq code that looked aside at Kerberos for a username? > >> Yes, partially I am :-) > >> But it was not documented, and done in a fairly hackish way. If we >> want it, it should work the same for *all* external authentication >> methods (where it would be possible). > > Well, the problem with it of course was that it happened even when the > selected auth method was not Kerberos. That was the core problem, yes. IIRC there were some other minor issues with it as well. >> Doing it on the client presents a certain challenge > > Yup, you would need a protocol change that would allow the client to > change its mind about what the username was after it got the auth > challenge. And then what effects does that have on username-sensitive > pg_hba.conf decisions? We go back and change our minds about the > challenge type, perhaps? The whole thing seems like a nonstarter to me. "challenge type"? Not sure I understand what you are referring to here. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Am Dienstag, 21. Juli 2009 16:01:01 schrieben Sie: > Doing it on the client presents a certain challenge when it comes to > certificates for example - or really in any case where you need to map > the username to something else. It would be quite convenient to have > that ability controlled from the server side. We'd have to have some > way to communicate down that the username specified was the default > one and not a user-specified one (or we're back at overriding), but if > the actual mapping could be controlled server-side it would be a lot > more convenient. I thought about doing it on the client side too, but server side mapping seemed to me more flexible. In fact one could do a client side mapping (without knowledge of the auth method), by greping the username of the external system out of the error text from the server and doing a second auth with it. Just I don't like this ugly hack. There was another mail, where I described the use of the mapping patch: http://archives.postgresql.org/pgsql-hackers/2009-06/msg01496.php Please have a look on it. One can give different mappings for combinations of internal and external username. This way you could easy use different roles within the database with differnent applications, although the external auth system gives the same username. The "dummy"-user of the first mail was not the best example. I'm not the expert with PGs internals. So if you have a better way to get an usermapping based on the external auth, I could do a bit of research in this area, because I need something like this. regards Lars Kanis signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Magnus Hagander writes: > On Tue, Jul 21, 2009 at 15:58, Tom Lane wrote: >> Are you not describing a behavior that you yourself removed in 8.4, >> ie the libpq code that looked aside at Kerberos for a username? > Yes, partially I am :-) > But it was not documented, and done in a fairly hackish way. If we > want it, it should work the same for *all* external authentication > methods (where it would be possible). Well, the problem with it of course was that it happened even when the selected auth method was not Kerberos. > Doing it on the client presents a certain challenge Yup, you would need a protocol change that would allow the client to change its mind about what the username was after it got the auth challenge. And then what effects does that have on username-sensitive pg_hba.conf decisions? We go back and change our minds about the challenge type, perhaps? The whole thing seems like a nonstarter to me. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
On Tue, Jul 21, 2009 at 15:58, Tom Lane wrote: > Magnus Hagander writes: >> That said, if there is a username specified it should not be ignored. >> But if there is none specified, it should work. This works "reasonably >> well" today, in that we pick the username up from the environment. But >> I can see cases where it would be a lot more useful to have it instead >> pick up the username from the authentication system, since they may >> differ. > > Are you not describing a behavior that you yourself removed in 8.4, > ie the libpq code that looked aside at Kerberos for a username? Yes, partially I am :-) But it was not documented, and done in a fairly hackish way. If we want it, it should work the same for *all* external authentication methods (where it would be possible). Doing it on the client presents a certain challenge when it comes to certificates for example - or really in any case where you need to map the username to something else. It would be quite convenient to have that ability controlled from the server side. We'd have to have some way to communicate down that the username specified was the default one and not a user-specified one (or we're back at overriding), but if the actual mapping could be controlled server-side it would be a lot more convenient. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Magnus Hagander writes: > That said, if there is a username specified it should not be ignored. > But if there is none specified, it should work. This works "reasonably > well" today, in that we pick the username up from the environment. But > I can see cases where it would be a lot more useful to have it instead > pick up the username from the authentication system, since they may > differ. Are you not describing a behavior that you yourself removed in 8.4, ie the libpq code that looked aside at Kerberos for a username? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Sorry for jumping late into this discussion, but I've been out sailing for a couple of weeks and stayed away from email :-) On Mon, Jun 29, 2009 at 21:01, Stephen Frost wrote: > * Lars Kanis (ka...@comcard.de) wrote: >> The problem I have, is that I want to use an ordinary windows application, >> which connects to an arbitrary ODBC data source. This application stores a >> fixed username und password for the connection within it's own binary data >> file. It doesn't know anything about TLS-connection nor smartcard based >> authentication. All this is done in the libpg.dll. > > To be honest, I think the problem as described above makes it a no-go. > What you're asking for is essentially "I want the server to ignore the > username passed in" which just doesn't make sense. It's unfortunate > that this Windows application using ODBC doesn't allow for the username > to be changed, but that's not something PG is going to solve for you. I > would recommend you work on fixing the application. I think it's a reasonable expectation for there to be a way to pick up the username from an external authentication system, such as Kerberos, SSPI or certificate. Other databases that support them do - at least MSSQL. That said, if there is a username specified it should not be ignored. But if there is none specified, it should work. This works "reasonably well" today, in that we pick the username up from the environment. But I can see cases where it would be a lot more useful to have it instead pick up the username from the authentication system, since they may differ. But in no case should this override what the user put in to the system - but it could change the default. >> It works fine so far, as long as I want to work with the sigle role given by >> the fixed username. I could map any cn-contents to this one user by writing: >> # MAPNAME SYSTEM-USERNAME PG-USERNAME >> ssl-user /.* fixed_user > > What you really want is: > ssl-user user1_cn user1 > ssl-user user2_cn user2 > ssl-user user3_cn user3 > > or so.. Well, you'd write that too with a regexp map with a back reference. That's the original reason I implemented regexps for pg_ident :-) > And then have your application accept and use a username provided to it > when connecting to ODBC. Having your application pass some hard-coded > username to PG all the time just isn't going to work. I think the issue is "provided to it". There should be no need for the user to know his username since he's already logged in. I agree that the hard-coded-username method is broken. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
> # MAPNAME SYSTEM-USERNAMEPG-USERNAME EFFECTIVE-USERNAME > gssapi-user/^(.*)@domain\.com$simple-role \1 > gssapi-user/^use...@domain\.com$ super-roleuser_a > gssapi-user/^use...@domain\.com$ super-roleuser_c My fault. The lower lines should be: gssapi-user/^use...@domain\.com$ super-rolesuper_user_a gssapi-user/^use...@domain\.com$ super-rolesuper_user_c regards Lars Kanis signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Am Montag, 29. Juni 2009 18:12:13 schrieben Sie: > Lars Kanis writes: > > The problem I have, is that I want to use an ordinary windows > > application, which connects to an arbitrary ODBC data source. This > > application stores a fixed username und password for the connection > > within it's own binary data file. It doesn't know anything about > > TLS-connection nor smartcard based authentication. All this is done in > > the libpg.dll. > > This seems to boil down to "I'm willing to damage PG's authentication > mechanisms to an unlimited extent to work around one broken proprietary > windows app". I'm not excited about it. Oh no! You totally misunderstood me. So please give me another try to explain the addition: It's NOT about one stupid or broken (it isn't) application that has to be corrected. It's NOT about a specific problem with ODBC connections. It's a much more general thing, regardless which application/programming language/connection-library you use. I wouldn't post a quick an dirty hack to work around one broken proprietary windows app! The situation with any external single sign-on authentication method (GSSAPI, SSPI, Kerberos, Certificate authentication) is that you always have to provide a username. It is differentiated between the SYSTEM-USERNAME and the PG-USERNAME. The SYSTEM-USERNAME is the name the user is identified to by the external authentication. It is usually done with cryptographic validation of some tickets or certificates. So this value is the important one in terms of security. The PG-USERNAME is what the application tells the server the user wants to act as, in the database. It has no value at all in terms of security - it's just a wish. It is verified through the mapping of pg_ident.conf whether the wish can be granted with the given SYSTEM-USERNAME. The problem arises when you want to work with 1:1 mapping of SYSTEM-USERNAME to PG-USERNAME. You don't want to let the user type in the PG-USERNAME he wants to act as (I have never seen an kerberos-enabled application to do so). So the application has to take into account which authentication method you actually use, to extract the exact username from credentials/certificate. That means you have to open a second channel to your SSO-System to provide the correct username to the PG-server. And it means you have to do some sort of duplicated code in the application, which is already realised within libpq. Currently you can not say: "Dear libpq, please identify the current user and authenticate to the PG-server (by whatever authentication methods you're allowed to by pg_hba.conf), and log in to the own account of the user." My addition allows to abstract the application from the used authentication method. It adds an level of indirection between PG-USERNAME (given by the application) and the EFFECTIVE-USERNAME (for actual db-role) based on the SYSTEM-USERNAME (given by the SSO-system). And it adds it in a way that fits nicely into the already existend mapping-file pg_ident.conf. Let's show the following example-table in pg_ident.conf: # MAPNAME SYSTEM-USERNAMEPG-USERNAME EFFECTIVE-USERNAME gssapi-user/^(.*)@domain\.com$simple-role \1 gssapi-user/^use...@domain\.com$ super-roleuser_a gssapi-user/^use...@domain\.com$ super-roleuser_c The first line says: Any user with an account on the PG-server and valid GSSAPI-credentials can log in to it's own PG-account. The application doesn't need to care about which user is actually working with it. It simply needs to say to server to use the users 'simple-role'. You can write the application completely independent to the authentication method to use. For whatever security relevant action in this or another application, you can log in to the 'super-role' of the user. But only user_a and user_c are able to do so. Likewise the application don't need to care about the actually used SSO-method or system. Also to mention: The addition is fully backward compatible. > Have you even considered the > potential for security problems arising from this? Yes, of course. The primary goal of the addition is to get more security. Some questions I considered: Can I go around permission checks which are carried out by PG? That's the most important question, I think. As I stated above, the PG-USERNAME always has an informative nature. It's just a wish with no value in terms of security. So it is safe to change the name, as long as all usual permission checks are done on the changed one. In fact they are done. The identification and validation of users credentials are completed, so we can trust the SYSTEM-USERNAME. Then the pg_ident.conf comes in action and determines the EFFECTIVE-USERNAME. Any further permission checks are done afterwards. So the EFFECTIVE-USERNAME is checked against login-permission and so on. Does it get more security to give every application user it's own account within the database? Y
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Lars, * Lars Kanis (ka...@comcard.de) wrote: > The problem I have, is that I want to use an ordinary windows application, > which connects to an arbitrary ODBC data source. This application stores a > fixed username und password for the connection within it's own binary data > file. It doesn't know anything about TLS-connection nor smartcard based > authentication. All this is done in the libpg.dll. To be honest, I think the problem as described above makes it a no-go. What you're asking for is essentially "I want the server to ignore the username passed in" which just doesn't make sense. It's unfortunate that this Windows application using ODBC doesn't allow for the username to be changed, but that's not something PG is going to solve for you. I would recommend you work on fixing the application. > It works fine so far, as long as I want to work with the sigle role given by > the fixed username. I could map any cn-contents to this one user by writing: > # MAPNAME SYSTEM-USERNAMEPG-USERNAME > ssl-user /.*fixed_user What you really want is: ssl-user user1_cn user1 ssl-user user2_cn user2 ssl-user user3_cn user3 or so.. And then have your application accept and use a username provided to it when connecting to ODBC. Having your application pass some hard-coded username to PG all the time just isn't going to work. > I think the same problem occurs with kerberos authentication. You can't get > the role based on your kerberos ticket, when the username is not set > likewise. With Kerberos you do pass in the username (or possibly the username is "figured out" from the principal, but it's the same to the server side) you actually want to be in the database as part of the connection and then the validation is done against the Kerberos credentials. Perhaps your Windows application also wouldn't work with Kerberos, but in the general case people are logging in with their username, not with some fixed username. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Lars Kanis writes: > The problem I have, is that I want to use an ordinary windows application, > which connects to an arbitrary ODBC data source. This application stores a > fixed username und password for the connection within it's own binary data > file. It doesn't know anything about TLS-connection nor smartcard based > authentication. All this is done in the libpg.dll. This seems to boil down to "I'm willing to damage PG's authentication mechanisms to an unlimited extent to work around one broken proprietary windows app". I'm not excited about it. Have you even considered the potential for security problems arising from this? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Am Montag, 29. Juni 2009 16:26:56 schrieben Sie: > Lars Kanis writes: > > this patch adds the possibility to map the login-rolename to a different > > rolename actually used for permissions. > > This seems like an ugly addition with a very narrow use case. Can't > you accomplish what you want with the existing usermap facility? You're right, my description is a bit incomplete. The problem I have, is that I want to use an ordinary windows application, which connects to an arbitrary ODBC data source. This application stores a fixed username und password for the connection within it's own binary data file. It doesn't know anything about TLS-connection nor smartcard based authentication. All this is done in the libpg.dll. It works fine so far, as long as I want to work with the sigle role given by the fixed username. I could map any cn-contents to this one user by writing: # MAPNAME SYSTEM-USERNAMEPG-USERNAME ssl-user /.*fixed_user The db internal role is always that given by the application. But I need to work with the role of the certificate-cn of the current smartcard, the application doesn't know about. Because the username is stored within the applications own binary data file I'm not able to change it according to the pluged in card. I think the same problem occurs with kerberos authentication. You can't get the role based on your kerberos ticket, when the username is not set likewise. So it seemed to me quite useful, to not just set which external name matches which login-username, but also to set which userrole is actually used for granted privilegs. This is done by an additional column with the same characteristic as column PG-USERNAME. Another way could be to add an parameter to the hba line which tells the server to not care about the login username, but to only use the external (CN) name. But this wouldn't have the flexibility of regexps like in pg_ident.conf. Hope this clarifies a bit. regards Lars Kanis signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Lars Kanis writes: > this patch adds the possibility to map the login-rolename to a different > rolename actually used for permissions. This seems like an ugly addition with a very narrow use case. Can't you accomplish what you want with the existing usermap facility? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers