Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Tue, May 26, 2015 at 10:06:59PM -0400, Robert Haas wrote: On Sat, May 23, 2015 at 8:14 PM, Noah Misch n...@leadboat.com wrote: On Tue, May 19, 2015 at 04:49:26PM -0400, Robert Haas wrote: A protocol extension avoids all of that trouble, and can be target for 9.6 just like any other approach we might come up with. I actually suspect the protocol extension will be FAR easier to fully secure, and thus less work, not more. All true. Here's another idea. Have the pooler open one additional connection, for out-of-band signalling. Add a pair of functions: pg_userchange_grant(recipient_pid int, user oid) pg_userchange_accept(sender_pid int, user oid) To change the authenticated user of a pool connection, the pooler would call pg_userchange_grant in the signalling connection and pg_userchange_accept in the target connection. This requires no protocol change or confidential nonce. The inevitably-powerful signalling user is better insulated from other users, because the pool backends have no need to become that user at any point. Bugs in the pooler's protocol state machine are much less likely to enable privilege escalation. On the other hand, it can't be quite as fast as the other ideas on this thread. I'm sure this could be made to work, but it would require complex signalling in return for no obvious value. I don't see avoiding a protocol extension as particularly beneficial. New protocol messages that are sent by the server cause a hard compatibility break for clients, but new protocol messages that are client-initiated and late enough in the protocol flow that the client knows the server version have no such problem. I didn't realize a protocol addition could be that simple, but you're right. -- 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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Tue, May 19, 2015 at 04:49:26PM -0400, Robert Haas wrote: On Tue, May 19, 2015 at 3:00 PM, Simon Riggs si...@2ndquadrant.com wrote: As long as the cookie is randomly generated for each use, then I don't see a practical problem with that approach. If the client sets the cookie via an SQL command, that command would be written to the log, and displayed in pg_stat_activity. A malicious user might be able to get it from one of those places. A malicious user might also be able to just guess it. I don't really want to create a situation where any weakess in pgpool's random number generation becomes a privilege-escalation attack. A protocol extension avoids all of that trouble, and can be target for 9.6 just like any other approach we might come up with. I actually suspect the protocol extension will be FAR easier to fully secure, and thus less work, not more. All true. Here's another idea. Have the pooler open one additional connection, for out-of-band signalling. Add a pair of functions: pg_userchange_grant(recipient_pid int, user oid) pg_userchange_accept(sender_pid int, user oid) To change the authenticated user of a pool connection, the pooler would call pg_userchange_grant in the signalling connection and pg_userchange_accept in the target connection. This requires no protocol change or confidential nonce. The inevitably-powerful signalling user is better insulated from other users, because the pool backends have no need to become that user at any point. Bugs in the pooler's protocol state machine are much less likely to enable privilege escalation. On the other hand, it can't be quite as fast as the other ideas on this thread. -- 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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 5/20/15 9:38 PM, Robert Haas wrote: On Wed, May 20, 2015 at 8:22 PM, Jim Nasby jim.na...@bluetreble.com wrote: It might be a good idea to do something like this, but it's significantly more complicated than a protocol-level SET SESSION AUTHORIZATION. Right now, you can never go backwards from an authenticated state to an unauthenticated state, and there may be code in the backend that relies on that in subtle ways. The initial bootstrap sequence is pretty complicated, and I'm pretty sure that any naive attempt to redo that stuff is going to have unpleasant, probably security-relevant bugs. What about the middle-ground of not doing de-auth right now? That eliminates your concerns but still allows getting rid of ugly things like copies of the password file (FWIW, my understanding is pgBouncer was meant more to run on the database server where you'd just point it at the native password file). Uh, I don't have a clue what you mean when you say the middle ground of not doing de-auth right now. Don't allow a backend to move back into a de-authenticated state. Basically, allow a special connection mode that does nothing but provide user authentication back to the pooler. This would allow the pooler to defer all auth decisions to Postgres. Once the user was authenticated, the pooler could then figure out what pool connection to give to the user. I was thinking that if this authentication connection was never allowed to run SQL then it should eliminate fears of being in a de-authenticated state, but I see now that we've already started transaction machinery by the time PerformAuthentication happens, presumably for good reason. So maybe it's just as bad as trying to de-authenticate a full backend... -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 5/20/15 5:21 PM, Robert Haas wrote: On Tue, May 19, 2015 at 5:02 PM, Simon Riggs si...@2ndquadrant.com wrote: That's a reasonable argument. So +1 to protocol from me. To satisfy Tom, I think this would need to have two modes: one where the session can never be reset, for ultra security, and one where the session can be reset, which allows security and speed of pooling. I think the the second one is a lot more interesting, but I don't have a problem with having the first one, too, if somebody wants it. We can use one protocol message for both, with a 1-byte character field used to indicate which mode the client is requesting. Now that we're on the topic of interesting things, would it make sense to add protocol support for a sort of a re-authenticate? So a pooler could first say this user wants to log in from this host, then get back a message saying how to authenticate that user, which the pooler could then pass that on to the client. Once the client has passed its credentials, the pooler could (possibly in another backend) try to authenticate using those credentials, and only then set the session's authentication. This would allow for more transparent poolers while still, well, pooling connections. I haven't thought about this at all, so maybe it's a stupid idea (or the backends don't have all the information to do this), or whatever. .m -- 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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Wed, May 20, 2015 at 3:42 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas wrote: On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja ma...@joh.to wrote: Now that we're on the topic of interesting things, would it make sense to add protocol support for a sort of a re-authenticate? So a pooler could first say this user wants to log in from this host, then get back a message saying how to authenticate that user, which the pooler could then pass that on to the client. I don't think this will work, because the authentication dialogue is structured a series of challenges and responses. After mulling over this a bit, I think that if we're to do something to improve things here we should redesign the protocol so that it considers poolers explicitely. Right now I think a pooler is pretty limited in what it can do. If we were to have messages specifically for poolers, life would be simpler: pooler authenticates to main server, client authenticates to pooler. The pooler can change auth on the server connection to whatever the client has, and begin passthrough of protocol data; when client closes connection, pooler recycles connection and de-authenticates it with main server so that it can be reused for another client (re-auth). Client by itself cannot de-auth to steal the connection under somebody else's name. It might be a good idea to do something like this, but it's significantly more complicated than a protocol-level SET SESSION AUTHORIZATION. Right now, you can never go backwards from an authenticated state to an unauthenticated state, and there may be code in the backend that relies on that in subtle ways. The initial bootstrap sequence is pretty complicated, and I'm pretty sure that any naive attempt to redo that stuff is going to have unpleasant, probably security-relevant bugs. (In the current architecture, you also can't rebind to a new database; I'm not sure if your proposal would change things from that side, but if so, that adds a further level of complexity.) I would urge, rather strongly, that we keep the first version of this simple: let the pooler, via a protocol message, set the session authorization in a fashion that prevents it from being changed back except by another protocol message. If we want to do something like this after that, fine, but letting the pooler switch the authorization in a non-subvertable way is a whole lot simpler than what you are talking about. There's an issue that in order to authenticate a client, the pooler needs to have info from the server about auth data. Last I checked pgbouncer, you had to copy a list of username/passwords from the server to a pgbouncer config file, which is ugly and dangerous (not to mention tedious and error-prone). We could fix that sort of thing too, if we were to design something here with poolers in mind. I think this is fundamentally backwards. If the client is going to authenticate directly to the pooler, then the pooler should be the master source of the authentication information, and pooler should just log into the server as superuser. If you instead do what you're proposing and teach the server to send its authentication secrets to the pooler, you risk somebody evil using the same feature to extract those secrets for malicious purposes (think: DBA who is about to be fired). You're basically adding a server feature whereby it facilitates MITM attacks against itself. -- 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
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja ma...@joh.to wrote: On 5/20/15 5:21 PM, Robert Haas wrote: On Tue, May 19, 2015 at 5:02 PM, Simon Riggs si...@2ndquadrant.com wrote: That's a reasonable argument. So +1 to protocol from me. To satisfy Tom, I think this would need to have two modes: one where the session can never be reset, for ultra security, and one where the session can be reset, which allows security and speed of pooling. I think the the second one is a lot more interesting, but I don't have a problem with having the first one, too, if somebody wants it. We can use one protocol message for both, with a 1-byte character field used to indicate which mode the client is requesting. Now that we're on the topic of interesting things, would it make sense to add protocol support for a sort of a re-authenticate? So a pooler could first say this user wants to log in from this host, then get back a message saying how to authenticate that user, which the pooler could then pass that on to the client. Once the client has passed its credentials, the pooler could (possibly in another backend) try to authenticate using those credentials, and only then set the session's authentication. This would allow for more transparent poolers while still, well, pooling connections. I haven't thought about this at all, so maybe it's a stupid idea (or the backends don't have all the information to do this), or whatever. I don't think this will work, because the authentication dialogue is structured a series of challenges and responses. For many authentication methods, these are replay-resistant by design; if you could watch a Kerberos authentication sequence and then, based on having seen it, conduct an authentication dialog with somebody else successfully, that would be a very serious security flaw; it would amount to being able to steal the secret key by observing one authentication dialog. Even md5 authentication is intended to be replay-resistant, by using a different salt each time. Sure, the pooler COULD reuse the same salt over and over and just look for a matching response, but then md5 authentication via the pooler becomes much less secure than md5 authentication that goes directly to the server. That's bad. I suspect you're asking about this because you are concerned about the problem of authentication to the pooler being awkward and maybe insecure. I suspect that the only real solution to that problem is going to be to put the pooler into the database server itself, so that you just have one piece of software. That doesn't mean we shouldn't look for other methods of improving things between now and then, but I think it's going to be a hard problem to solve. -- 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
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
Robert Haas wrote: On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja ma...@joh.to wrote: Now that we're on the topic of interesting things, would it make sense to add protocol support for a sort of a re-authenticate? So a pooler could first say this user wants to log in from this host, then get back a message saying how to authenticate that user, which the pooler could then pass that on to the client. I don't think this will work, because the authentication dialogue is structured a series of challenges and responses. After mulling over this a bit, I think that if we're to do something to improve things here we should redesign the protocol so that it considers poolers explicitely. Right now I think a pooler is pretty limited in what it can do. If we were to have messages specifically for poolers, life would be simpler: pooler authenticates to main server, client authenticates to pooler. The pooler can change auth on the server connection to whatever the client has, and begin passthrough of protocol data; when client closes connection, pooler recycles connection and de-authenticates it with main server so that it can be reused for another client (re-auth). Client by itself cannot de-auth to steal the connection under somebody else's name. There's an issue that in order to authenticate a client, the pooler needs to have info from the server about auth data. Last I checked pgbouncer, you had to copy a list of username/passwords from the server to a pgbouncer config file, which is ugly and dangerous (not to mention tedious and error-prone). We could fix that sort of thing too, if we were to design something here with poolers in mind. -- Álvaro Herrerahttp://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
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 5/20/15 3:31 PM, Robert Haas wrote: On Wed, May 20, 2015 at 3:42 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas wrote: After mulling over this a bit, I think that if we're to do something to improve things here we should redesign the protocol so that it considers poolers explicitely. Right now I think a pooler is pretty limited in what it can do. If we were to have messages specifically for poolers, life would be simpler: pooler authenticates to main server, client authenticates to pooler. The pooler can change auth on the server connection to whatever the client has, and begin passthrough of protocol data; when client closes connection, pooler recycles connection and de-authenticates it with main server so that it can be reused for another client (re-auth). Client by itself cannot de-auth to steal the connection under somebody else's name. It might be a good idea to do something like this, but it's significantly more complicated than a protocol-level SET SESSION AUTHORIZATION. Right now, you can never go backwards from an authenticated state to an unauthenticated state, and there may be code in the backend that relies on that in subtle ways. The initial bootstrap sequence is pretty complicated, and I'm pretty sure that any naive attempt to redo that stuff is going to have unpleasant, probably security-relevant bugs. What about the middle-ground of not doing de-auth right now? That eliminates your concerns but still allows getting rid of ugly things like copies of the password file (FWIW, my understanding is pgBouncer was meant more to run on the database server where you'd just point it at the native password file). -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Wed, May 20, 2015 at 8:22 PM, Jim Nasby jim.na...@bluetreble.com wrote: It might be a good idea to do something like this, but it's significantly more complicated than a protocol-level SET SESSION AUTHORIZATION. Right now, you can never go backwards from an authenticated state to an unauthenticated state, and there may be code in the backend that relies on that in subtle ways. The initial bootstrap sequence is pretty complicated, and I'm pretty sure that any naive attempt to redo that stuff is going to have unpleasant, probably security-relevant bugs. What about the middle-ground of not doing de-auth right now? That eliminates your concerns but still allows getting rid of ugly things like copies of the password file (FWIW, my understanding is pgBouncer was meant more to run on the database server where you'd just point it at the native password file). Uh, I don't have a clue what you mean when you say the middle ground of not doing de-auth right now. -- 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
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Tue, May 19, 2015 at 5:02 PM, Simon Riggs si...@2ndquadrant.com wrote: That's a reasonable argument. So +1 to protocol from me. To satisfy Tom, I think this would need to have two modes: one where the session can never be reset, for ultra security, and one where the session can be reset, which allows security and speed of pooling. I think the the second one is a lot more interesting, but I don't have a problem with having the first one, too, if somebody wants it. We can use one protocol message for both, with a 1-byte character field used to indicate which mode the client is requesting. -- 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
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
* Simon Riggs (si...@2ndquadrant.com) wrote: On 19 May 2015 at 16:49, Robert Haas robertmh...@gmail.com wrote: On Tue, May 19, 2015 at 3:00 PM, Simon Riggs si...@2ndquadrant.com wrote: As long as the cookie is randomly generated for each use, then I don't see a practical problem with that approach. If the client sets the cookie via an SQL command, that command would be written to the log, and displayed in pg_stat_activity. A malicious user might be able to get it from one of those places. A malicious user might also be able to just guess it. I don't really want to create a situation where any weakess in pgpool's random number generation becomes a privilege-escalation attack. A protocol extension avoids all of that trouble, and can be target for 9.6 just like any other approach we might come up with. I actually suspect the protocol extension will be FAR easier to fully secure, and thus less work, not more. That's a reasonable argument. So +1 to protocol from me. To satisfy Tom, I think this would need to have two modes: one where the session can never be reset, for ultra security, and one where the session can be reset, which allows security and speed of pooling. For my 2c, I continue to agree with a protocol-based approach, but I don't think having two modes would actually satisfy concerns regarding the security- we're still going to have to fix any issues which are security related that come up from having the session able to be reset mode. That said, we know connection poolers are already using SET SESSION AUTH (which is clearly far worse than what we're proposing to do here..) and clearly we support SET ROLE, so any issues with those methods really should be getting addressed anyway. Perhaps we can continue to beg off in the SET SESSION AUTH case by hiding behind you're a superuser or you're using it wrong but that doesn't actually make anyone more secure and we clearly need to address the SET ROLE case, as that is absolutely expected to work correctly. As for the discussion regarding having a connection pooler built-in- that is absolutely something we need to do, in my view, because any external connection pooler isn't going to offer the same set of capabilities that core does and we continue to fight with the concerns around changing the wireline protocol which hamstrings our progress in this area. That isn't to say it's all roses if we just built it in, because clearly it's not and there's work to be done there, but a connection pooler which is tied closely to core and which is upgraded and deployed with it could be much more easily changed and improved. On the other hand, I'd really like to see improvement to our protocol too and perhaps this is a way to get those, though it hasn't been happening so far, unfortunately. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Mon, May 18, 2015 at 12:33 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Bruce Momjian wrote: On Sun, May 17, 2015 at 09:31:47PM +0200, José Luis Tallón wrote: On 05/17/2015 07:39 PM, Tom Lane wrote: =?windows-1252?Q?Jos=E9_Luis_Tall=F3n?= jltal...@adv-solutions.net writes: On the other hand, ISTM that what we all intend to achieve is some Postgres equivalent of the SUID bit... so why not just do something equivalent? --- LOGIN-- as user with the appropriate role membership / privilege? ... SET ROLE / SET SESSION AUTHORIZATION WITH COOKIE / IMPERSONATE ... do whatever ...-- unprivileged user can NOT do the impersonate thing DISCARD ALL-- implicitly restore previous authz --- Oh? What stops the unprivileged user from doing DISCARD ALL? Indeed. The pooler would need to block this. Or we would need to invent another (this time, privileged) verb in order to restore authz. What if you put the SQL in a function then call the function? I don't see how the pooler could block this. I think the idea of having SET SESSION AUTH pass a cookie, and only let RESET SESSION AUTH work when the same cookie is supplied, is pretty reasonable. That seems like a kludge to me. If the cookie leaks out somhow, which it will, then it'll be insecure. I think the way to do this is with a protocol extension that poolers can enable on request. Then they can just refuse to forward any reset authorization packets they get from their client. There's no backward-compatibility break because the pooler can know, from the server version, whether the server is new enough to support the new protocol messages. -- 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
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 2015-05-19 14:41:06 -0400, Robert Haas wrote: On Tue, May 19, 2015 at 12:29 PM, Andres Freund and...@anarazel.de wrote: On 2015-05-19 10:53:10 -0400, Robert Haas wrote: That seems like a kludge to me. If the cookie leaks out somhow, which it will, then it'll be insecure. I think the way to do this is with a protocol extension that poolers can enable on request. Then they can just refuse to forward any reset authorization packets they get from their client. There's no backward-compatibility break because the pooler can know, from the server version, whether the server is new enough to support the new protocol messages. That sounds like a worse approach to me. Don't you just need to hide the session authorization bit in a function serverside to circumvent that? I'm apparently confused. There's nothing you can do to maintain security against someone who can load C code into the server. I must be misunderstanding you. It very well might be me that's confused. But what's stopping a user from doing a RESET SESSION AUTHORIZATION; in a DO block or something? I guess you are intending that a RESET SESSION AUTHORIZATION is only allowed on a protocol level when the protocol extension is in use? Greetings, Andres Freund -- 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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 2015-05-19 10:53:10 -0400, Robert Haas wrote: That seems like a kludge to me. If the cookie leaks out somhow, which it will, then it'll be insecure. I think the way to do this is with a protocol extension that poolers can enable on request. Then they can just refuse to forward any reset authorization packets they get from their client. There's no backward-compatibility break because the pooler can know, from the server version, whether the server is new enough to support the new protocol messages. That sounds like a worse approach to me. Don't you just need to hide the session authorization bit in a function serverside to circumvent that? Greetings, Andres Freund -- 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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Tue, May 19, 2015 at 3:00 PM, Simon Riggs si...@2ndquadrant.com wrote: As long as the cookie is randomly generated for each use, then I don't see a practical problem with that approach. If the client sets the cookie via an SQL command, that command would be written to the log, and displayed in pg_stat_activity. A malicious user might be able to get it from one of those places. A malicious user might also be able to just guess it. I don't really want to create a situation where any weakess in pgpool's random number generation becomes a privilege-escalation attack. A protocol extension avoids all of that trouble, and can be target for 9.6 just like any other approach we might come up with. I actually suspect the protocol extension will be FAR easier to fully secure, and thus less work, not more. -- 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
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 05/19/2015 09:00 PM, Simon Riggs wrote: [snip] I think the idea of having SET SESSION AUTH pass a cookie, and only let RESET SESSION AUTH work when the same cookie is supplied, is pretty reasonable. As long as the cookie is randomly generated for each use, then I don't see a practical problem with that approach. Protocol level solution means we have to wait 1.5 years before anybody can begin using that. I'm also dubious that a small hole in the protocol arrangements could slam that door shut because we couldn't easily backpatch. Having an in-core pooler would be just wonderful because then we could more easily trust it and we wouldn't need to worry. Ufff Please don't do that. Postgres is just a database. And a very good one at that. Let us keep it that way and not try to re-implement everything within it --- We're not the big red company after all :) There are places where a pooler is badly needed and others where it is just overkill and counterproductive. Plus, scalability models / usage patterns are not nearly the same (nor even compatible sometimes!) between databases and poolers. There exist perfectly good solutions already (and they can certainly be improved), such as PgBouncer (or even PgPool-II) or others can be adopted. Just my .02€ / J.L.
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Tue, May 19, 2015 at 2:46 PM, Andres Freund and...@anarazel.de wrote: On 2015-05-19 14:41:06 -0400, Robert Haas wrote: On Tue, May 19, 2015 at 12:29 PM, Andres Freund and...@anarazel.de wrote: On 2015-05-19 10:53:10 -0400, Robert Haas wrote: That seems like a kludge to me. If the cookie leaks out somhow, which it will, then it'll be insecure. I think the way to do this is with a protocol extension that poolers can enable on request. Then they can just refuse to forward any reset authorization packets they get from their client. There's no backward-compatibility break because the pooler can know, from the server version, whether the server is new enough to support the new protocol messages. That sounds like a worse approach to me. Don't you just need to hide the session authorization bit in a function serverside to circumvent that? I'm apparently confused. There's nothing you can do to maintain security against someone who can load C code into the server. I must be misunderstanding you. It very well might be me that's confused. But what's stopping a user from doing a RESET SESSION AUTHORIZATION; in a DO block or something? I guess you are intending that a RESET SESSION AUTHORIZATION is only allowed on a protocol level when the protocol extension is in use? Yes, something like that. I'm not sure if we'd want to reuse the existing SESSION AUTHORIZATION concept or create something new, but either way the idea would be that the pooler would send a PoolerSetAuthorization message which could only be undone by another such message. -- 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
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 19 May 2015 at 16:49, Robert Haas robertmh...@gmail.com wrote: On Tue, May 19, 2015 at 3:00 PM, Simon Riggs si...@2ndquadrant.com wrote: As long as the cookie is randomly generated for each use, then I don't see a practical problem with that approach. If the client sets the cookie via an SQL command, that command would be written to the log, and displayed in pg_stat_activity. A malicious user might be able to get it from one of those places. A malicious user might also be able to just guess it. I don't really want to create a situation where any weakess in pgpool's random number generation becomes a privilege-escalation attack. A protocol extension avoids all of that trouble, and can be target for 9.6 just like any other approach we might come up with. I actually suspect the protocol extension will be FAR easier to fully secure, and thus less work, not more. That's a reasonable argument. So +1 to protocol from me. To satisfy Tom, I think this would need to have two modes: one where the session can never be reset, for ultra security, and one where the session can be reset, which allows security and speed of pooling. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 19/05/15 20:46, Andres Freund wrote: On 2015-05-19 14:41:06 -0400, Robert Haas wrote: On Tue, May 19, 2015 at 12:29 PM, Andres Freund and...@anarazel.de wrote: On 2015-05-19 10:53:10 -0400, Robert Haas wrote: That seems like a kludge to me. If the cookie leaks out somhow, which it will, then it'll be insecure. I think the way to do this is with a protocol extension that poolers can enable on request. Then they can just refuse to forward any reset authorization packets they get from their client. There's no backward-compatibility break because the pooler can know, from the server version, whether the server is new enough to support the new protocol messages. That sounds like a worse approach to me. Don't you just need to hide the session authorization bit in a function serverside to circumvent that? I'm apparently confused. There's nothing you can do to maintain security against someone who can load C code into the server. I must be misunderstanding you. It very well might be me that's confused. But what's stopping a user from doing a RESET SESSION AUTHORIZATION; in a DO block or something? I guess you are intending that a RESET SESSION AUTHORIZATION is only allowed on a protocol level when the protocol extension is in use? If I understand Robert correctly, he was talking about setting and resetting this on protocol level (with the assistance of pooler) so there is no way to circumvent that from SQL no matter how you mask the command. I think that idea is quite sound. -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, 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
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Sun, May 17, 2015 at 09:31:47PM +0200, José Luis Tallón wrote: On 05/17/2015 07:39 PM, Tom Lane wrote: =?windows-1252?Q?Jos=E9_Luis_Tall=F3n?= jltal...@adv-solutions.net writes: On the other hand, ISTM that what we all intend to achieve is some Postgres equivalent of the SUID bit... so why not just do something equivalent? --- LOGIN-- as user with the appropriate role membership / privilege? ... SET ROLE / SET SESSION AUTHORIZATION WITH COOKIE / IMPERSONATE ... do whatever ...-- unprivileged user can NOT do the impersonate thing DISCARD ALL-- implicitly restore previous authz --- Oh? What stops the unprivileged user from doing DISCARD ALL? Indeed. The pooler would need to block this. Or we would need to invent another (this time, privileged) verb in order to restore authz. What if you put the SQL in a function then call the function? I don't see how the pooler could block this. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] RFC: Non-user-resettable SET SESSION AUTHORISATION
Bruce Momjian wrote: On Sun, May 17, 2015 at 09:31:47PM +0200, José Luis Tallón wrote: On 05/17/2015 07:39 PM, Tom Lane wrote: =?windows-1252?Q?Jos=E9_Luis_Tall=F3n?= jltal...@adv-solutions.net writes: On the other hand, ISTM that what we all intend to achieve is some Postgres equivalent of the SUID bit... so why not just do something equivalent? --- LOGIN-- as user with the appropriate role membership / privilege? ... SET ROLE / SET SESSION AUTHORIZATION WITH COOKIE / IMPERSONATE ... do whatever ...-- unprivileged user can NOT do the impersonate thing DISCARD ALL-- implicitly restore previous authz --- Oh? What stops the unprivileged user from doing DISCARD ALL? Indeed. The pooler would need to block this. Or we would need to invent another (this time, privileged) verb in order to restore authz. What if you put the SQL in a function then call the function? I don't see how the pooler could block this. I think the idea of having SET SESSION AUTH pass a cookie, and only let RESET SESSION AUTH work when the same cookie is supplied, is pretty reasonable. -- Álvaro Herrerahttp://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
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
=?windows-1252?Q?Jos=E9_Luis_Tall=F3n?= jltal...@adv-solutions.net writes: On the other hand, ISTM that what we all intend to achieve is some Postgres equivalent of the SUID bit... so why not just do something equivalent? --- LOGIN-- as user with the appropriate role membership / privilege? ... SET ROLE / SET SESSION AUTHORIZATION WITH COOKIE / IMPERSONATE ... do whatever ...-- unprivileged user can NOT do the impersonate thing DISCARD ALL-- implicitly restore previous authz --- Oh? What stops the unprivileged user from doing DISCARD ALL? I think if we have something like this, it has to be non-resettable period: you can't get back the old session ID except by reconnecting and re-authorizing. Otherwise there's just too much risk of security holes. 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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 05/13/2015 06:03 AM, Alvaro Herrera wrote: Craig Ringer wrote: For some time I've wanted a way to SET SESSION AUTHORISATION or SET ROLE in a way that cannot simply be RESET, so that a connection may be handed to a less-trusted service or application to do some work with. Some years back, I checked the SQL standard for insight on how they handle this stuff (courtesy of Jim Nasby IIRC). It took me a while to figure out that the way they do it is not to have a RESET command in the first place! In their model, you enter a secure execution context (for example, an SQL function) by calling SET SESSION AUTHORIZATION; and once there, the only way to revert to the original session authorization is to exit the execution context -- and once that happens, the attacker no longer has control. Since they have reduced privileges, they can't call SET SESSION AUTHORIZATION themselves to elevate their access. In this model, you're automatically protected. I did address this same concern some four months ago, by suggesting to implement an IMPERSONATE command, as part of the rolesattributes rework. This thought was *precisely* oriented towards the sam goal as Craig's suggestion. Please keep in mind that SET ROLE and/or IMPERSONATE and/or SET SESSION AUTHORIZATION [WITH COOKIE] should be doable SQL-only, so no protocol changes whatsoever would be needed. On the other hand, ISTM that what we all intend to achieve is some Postgres equivalent of the SUID bit... so why not just do something equivalent? --- LOGIN-- as user with the appropriate role membership / privilege? ... SET ROLE / SET SESSION AUTHORIZATION WITH COOKIE / IMPERSONATE ... do whatever ...-- unprivileged user can NOT do the impersonate thing DISCARD ALL-- implicitly restore previous authz --- I mentioned this in some developer meeting; got blank stares back, IIRC. Let's hope something goes through this time. It seems to be a more pressing need now than it was then :) Thanks, / J.L. -- 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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 05/17/2015 07:39 PM, Tom Lane wrote: =?windows-1252?Q?Jos=E9_Luis_Tall=F3n?= jltal...@adv-solutions.net writes: On the other hand, ISTM that what we all intend to achieve is some Postgres equivalent of the SUID bit... so why not just do something equivalent? --- LOGIN-- as user with the appropriate role membership / privilege? ... SET ROLE / SET SESSION AUTHORIZATION WITH COOKIE / IMPERSONATE ... do whatever ...-- unprivileged user can NOT do the impersonate thing DISCARD ALL-- implicitly restore previous authz --- Oh? What stops the unprivileged user from doing DISCARD ALL? Indeed. The pooler would need to block this. Or we would need to invent another (this time, privileged) verb in order to restore authz. I think if we have something like this, it has to be non-resettable period: you can't get back the old session ID except by reconnecting and re-authorizing. Otherwise there's just too much risk of security holes. Yes. Thank you for your feedback, Tom. / J.L. -- 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] RFC: Non-user-resettable SET SESSION AUTHORISATION
* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Craig Ringer wrote: For some time I've wanted a way to SET SESSION AUTHORISATION or SET ROLE in a way that cannot simply be RESET, so that a connection may be handed to a less-trusted service or application to do some work with. Some years back, I checked the SQL standard for insight on how they handle this stuff (courtesy of Jim Nasby IIRC). It took me a while to figure out that the way they do it is not to have a RESET command in the first place! In their model, you enter a secure execution context (for example, an SQL function) by calling SET SESSION AUTHORIZATION; and once there, the only way to revert to the original session authorization is to exit the execution context -- and once that happens, the attacker no longer has control. Since they have reduced privileges, they can't call SET SESSION AUTHORIZATION themselves to elevate their access. In this model, you're automatically protected. I mentioned this in some developer meeting; got blank stares back, IIRC. I mentioned it to Stephen in hallway track, and as I recall he was in agreement with what I was proposing. Biggest problem is, I can't recall in detail what it was. The issue here ends up being that you don't get the pooling advantage because the connection pooler ends up having to drop the connection after using it. I'm not against a 'SET-and-never-return' concept, but I don't think it'd help what Craig's after. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
Craig, * Craig Ringer (cr...@2ndquadrant.com) wrote: On 12 May 2015 at 21:10, Stephen Frost sfr...@snowman.net wrote: This can be done without protocol-level changes and with no backward compatibility impact to existing applications. Any objections? I don't particularly object but I'm not entirely sure that it's that simple to clear out the cookie value- look at the previous discussion about ALTER ROLE / password resets and trying to keep them from ending up in the logs. In this case we don't have to care about the cookie values ending up in the logs. They're just single use tokens. If an app can read the PostgreSQL logs or access privileged pg_stat_activity then it's already privileged enough that it's outside the scope of something like this. Perhaps that would work, but the issue still exists about the connection on which it's set potentially being able to see the value if we don't scrub it, no? Perhaps people will feel differently about this since it's expected to be programatically used, but, personally, I'd favor trying to do something at the protocol level instead. I would also prefer a protocol level solution, but prior discussion has shown that Tom in particular sees it as unsafe to add new protocol messages in the v3 protocol. So I didn't think it was productive to start with a protocol-level approach when it can be done without protocol changes. I hope it wasn't quite so cut-and-dry as can't change anything.. Especially if it's a client initiated request which clearly indicates that the client supports the response then, hopefully, we'd be able to make a change. More specifics or a link to the prior discussion would help here. I'm very happy to do this at the protocol level, I just don't want to implement that and then have to do the SQL-level approach or implement a whole v4 protocol ;-) I certainly agree that we should discuss it first. As is currently the case, poolers will still have to use a superuser connection if they want to pool across users. That just needs to be fixed. :( Having poolers connect as superuser is *not* ok.. While I agree, that's existing common (and horrible) practice, and a separate problem. We need a way to say this pooler connection can become this set of users. Right now SET SESSION AUTHORIZATION is all or nothing. That's exactly what SET ROLE provides ... Is there a reason why we would need to support both? Clearly this is all post-9.5 work and seems like it might be reasonable to have completed for 9.6. Have you thought much about how Heikki's work on SCRAM might play into this? I haven't looked at the SCRAM work, and will take a look. Ok. If it can offer separation of authentication and authorization then it would be in a good position to handle letting SET SESSION AUTHORIZATION run as non-superuser, which would be great. I don't see how it could help with making it non-resettable though. It's not going to provide that right off the bat, but it is defining a new authentication mechanism with changes to the protocol.. Perhaps there's a way to work in the other things you're looking for as part of that change? That is to say, if the client says I support SCRAM and we authenticate that way then perhaps that can also mean I can do SCRAM to re-authenticate later too and we can feel comfortable about the protocol level changes to make what we're talking about here work, at least when SCRAM is involved. If that works, we could probably figure out a way to make the other auth methods able to work with this too. I guess I'm a bit confused at why SET ROLE being visible to apps is a problem..? If we used SET ROLE rather than SET SESSION AUTHORIZATION for this, then apps that use SET ROLE and RESET ROLE couldn't do so anymore, so a pooled connection wouldn't be quite the same as an unpooled connection. There's two pieces here- is it really a sensible use-case for the connection pooler to need to do SET ROLE and the app need to do it? I'm not convinced that actually makes sense or is a use-case we need to support. session_user would also report the pooler's user identity. So? I'm not sure that's an issue, though if it is, we could probably deal with it in some way. postgres= SET ROLE craig; SET postgres= SELECT session_user, current_user; session_user | current_user --+-- postgres | craig (1 row) ... and IIRC it affects some of the privilege and role membership tests, which distinguish between currently active role and role membership. Uhh, setting role had better change the privilege tests to operate against the role that you changed to. If it doesn't somewhere, then that's a bug we need to fix. I think if we want to SET the session authorization, SET SESSION AUTHORIZATION or a protocol level equivalent is more appropriate. It has the desired behaviour, barring a limit on being reset. What you're asking for above wrt
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 13 May 2015 at 09:55, Stephen Frost sfr...@snowman.net wrote: Craig, * Craig Ringer (cr...@2ndquadrant.com) wrote: On 12 May 2015 at 21:10, Stephen Frost sfr...@snowman.net wrote: This can be done without protocol-level changes and with no backward compatibility impact to existing applications. Any objections? I don't particularly object but I'm not entirely sure that it's that simple to clear out the cookie value- look at the previous discussion about ALTER ROLE / password resets and trying to keep them from ending up in the logs. In this case we don't have to care about the cookie values ending up in the logs. They're just single use tokens. If an app can read the PostgreSQL logs or access privileged pg_stat_activity then it's already privileged enough that it's outside the scope of something like this. Perhaps that would work, but the issue still exists about the connection on which it's set potentially being able to see the value if we don't scrub it, no? Yes, we must scrub it on that session, but that can be done with a dummy stats report if nothing else. Perhaps people will feel differently about this since it's expected to be programatically used, but, personally, I'd favor trying to do something at the protocol level instead. I would also prefer a protocol level solution, but prior discussion has shown that Tom in particular sees it as unsafe to add new protocol messages in the v3 protocol. So I didn't think it was productive to start with a protocol-level approach when it can be done without protocol changes. I hope it wasn't quite so cut-and-dry as can't change anything.. Especially if it's a client initiated request which clearly indicates that the client supports the response then, hopefully, we'd be able to make a change. More specifics or a link to the prior discussion would help here. It was with regards to returning the commit LSN. Thread begins here: http://www.postgresql.org/message-id/53e2d346.9030...@2ndquadrant.com, specific issue here: http://www.postgresql.org/message-id/25297.1407459...@sss.pgh.pa.us . It's WRT to using a GUC to enable/disable a protocol message, so it may simply not apply here, since we can have a client-initiated message without which the server behaviour isn't any different. As is currently the case, poolers will still have to use a superuser connection if they want to pool across users. That just needs to be fixed. :( Having poolers connect as superuser is *not* ok.. While I agree, that's existing common (and horrible) practice, and a separate problem. We need a way to say this pooler connection can become this set of users. Right now SET SESSION AUTHORIZATION is all or nothing. That's exactly what SET ROLE provides ... Is there a reason why we would need to support both? Clearly this is all post-9.5 work and seems like it might be reasonable to have completed for 9.6. Have you thought much about how Heikki's work on SCRAM might play into this? I haven't looked at the SCRAM work, and will take a look. Ok. If it can offer separation of authentication and authorization then it would be in a good position to handle letting SET SESSION AUTHORIZATION run as non-superuser, which would be great. I don't see how it could help with making it non-resettable though. It's not going to provide that right off the bat, but it is defining a new authentication mechanism with changes to the protocol.. Perhaps there's a way to work in the other things you're looking for as part of that change? That is to say, if the client says I support SCRAM and we authenticate that way then perhaps that can also mean I can do SCRAM to re-authenticate later too It shouldn't be necessary to re-authenticate. Just change the active authorizations to any that the current authentication permits. Arguably that's what SET ROLE does already though. There's two pieces here- is it really a sensible use-case for the connection pooler to need to do SET ROLE and the app need to do it? I'm not convinced that actually makes sense or is a use-case we need to support. Fair point. A protocol level SET ROLE that cannot be RESET may be enough, even if it's not totally transparent. If making it possible with S.S.A. later is necessary we could look at extending pg_authid but you're right that it's probably not truly needed. Uhh, setting role had better change the privilege tests to operate against the role that you changed to. If it doesn't somewhere, then that's a bug we need to fix. I'll see if I can turn vague memories into something more useful. What you're asking for above wrt saying a given user can become a set of users is what we've already *got* with roles. The only thing missing here is that the session can detect that you used set role, the reset issue that exists for both, and the how-to-auth issue which also exists for
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 12 May 2015 at 21:10, Stephen Frost sfr...@snowman.net wrote: This can be done without protocol-level changes and with no backward compatibility impact to existing applications. Any objections? I don't particularly object but I'm not entirely sure that it's that simple to clear out the cookie value- look at the previous discussion about ALTER ROLE / password resets and trying to keep them from ending up in the logs. In this case we don't have to care about the cookie values ending up in the logs. They're just single use tokens. If an app can read the PostgreSQL logs or access privileged pg_stat_activity then it's already privileged enough that it's outside the scope of something like this. Perhaps people will feel differently about this since it's expected to be programatically used, but, personally, I'd favor trying to do something at the protocol level instead. I would also prefer a protocol level solution, but prior discussion has shown that Tom in particular sees it as unsafe to add new protocol messages in the v3 protocol. So I didn't think it was productive to start with a protocol-level approach when it can be done without protocol changes. I'm very happy to do this at the protocol level, I just don't want to implement that and then have to do the SQL-level approach or implement a whole v4 protocol ;-) Is there a reason why they would need to be visible to the privileged user? Not really. As is currently the case, poolers will still have to use a superuser connection if they want to pool across users. That just needs to be fixed. :( Having poolers connect as superuser is *not* ok.. While I agree, that's existing common (and horrible) practice, and a separate problem. We need a way to say this pooler connection can become this set of users. Right now SET SESSION AUTHORIZATION is all or nothing. Is there a reason why we would need to support both? Clearly this is all post-9.5 work and seems like it might be reasonable to have completed for 9.6. Have you thought much about how Heikki's work on SCRAM might play into this? I haven't looked at the SCRAM work, and will take a look. If it can offer separation of authentication and authorization then it would be in a good position to handle letting SET SESSION AUTHORIZATION run as non-superuser, which would be great. I don't see how it could help with making it non-resettable though. I guess I'm a bit confused at why SET ROLE being visible to apps is a problem..? If we used SET ROLE rather than SET SESSION AUTHORIZATION for this, then apps that use SET ROLE and RESET ROLE couldn't do so anymore, so a pooled connection wouldn't be quite the same as an unpooled connection. session_user would also report the pooler's user identity. postgres= SET ROLE craig; SET postgres= SELECT session_user, current_user; session_user | current_user --+-- postgres | craig (1 row) ... and IIRC it affects some of the privilege and role membership tests, which distinguish between currently active role and role membership. I think if we want to SET the session authorization, SET SESSION AUTHORIZATION or a protocol level equivalent is more appropriate. It has the desired behaviour, barring a limit on being reset. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
Craig, All very interesting, but, to be honest, I don't really have time this week to chat about it. :( Apologies for that. A couple comments below. * Craig Ringer (cr...@2ndquadrant.com) wrote: For some time I've wanted a way to SET SESSION AUTHORISATION or SET ROLE in a way that cannot simply be RESET, so that a connection may be handed to a less-trusted service or application to do some work with. This is most useful for connection pools, where it's currently necessary to have a per-user pool, to trust users not to do anything naughty, or to filter the functions and commands they can run through a whitelist to stop them trying to escalate rights to the pooler user. In the short term I'd like to: * Add a WITH COOKIE option to SET SESSION AUTHORIZATION, which takes an app-generated code (much like PREPARE TRANSACTION does). * Make DISCARD ALL, RESET SESSION AUTHORIZATION, etc, also take WITH COOKIE. If session authorization was originally set with a cookie value, the same cookie value must be passed or an ERROR will be raised when RESET is attempted. * A second SET SESSION AUTHORIZATION without a prior RESET would be rejected with an ERROR if the first SET used a cookie value. This can be done without protocol-level changes and with no backward compatibility impact to existing applications. Any objections? I don't particularly object but I'm not entirely sure that it's that simple to clear out the cookie value- look at the previous discussion about ALTER ROLE / password resets and trying to keep them from ending up in the logs. Perhaps people will feel differently about this since it's expected to be programatically used, but, personally, I'd favor trying to do something at the protocol level instead. These commands will appear in the logs if log_statement = 'all', but the codes are transient cookie values, not passwords. They'll be visible in pg_stat_activity but only to the privileged user. It'll probably be necessary to clear the last command string when executing SET SESSION AUTHORIZATION so the new user can't snoop the cookie value from a concurrent connection, but that should be simple enough. Is there a reason why they would need to be visible to the privileged user? Just thinking about it from the perspective of it being a protocol change, if it's done that way, it wouldn't be in the SQL string; trying to understand if that's an actual problem. As is currently the case, poolers will still have to use a superuser connection if they want to pool across users. That just needs to be fixed. :( Having poolers connect as superuser is *not* ok.. In the longer term I want to add a protocol-level equivalent that lets a session switch session authorization or role, for efficiency and log-spam reasons. I'm all about this and have discussed it a few times before with people. This is a much better approach, imv, than what you're suggesting above. Is there a reason why we would need to support both? Clearly this is all post-9.5 work and seems like it might be reasonable to have completed for 9.6. Have you thought much about how Heikki's work on SCRAM might play into this? I'm also interested in a way to allow SET SESSION AUTHORIZATION to a list of permitted roles when run as a non-superuser, for connection pool use. SET ROLE might do, but it's more visible to apps, wheras SET SESSION AUTHORIZATION really makes the connection appear to become the target user. I guess I'm a bit confused at why SET ROLE being visible to apps is a problem..? That's later though - first, Hit send too quickly? Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
Craig Ringer wrote: Hi all For some time I've wanted a way to SET SESSION AUTHORISATION or SET ROLE in a way that cannot simply be RESET, so that a connection may be handed to a less-trusted service or application to do some work with. Some years back, I checked the SQL standard for insight on how they handle this stuff (courtesy of Jim Nasby IIRC). It took me a while to figure out that the way they do it is not to have a RESET command in the first place! In their model, you enter a secure execution context (for example, an SQL function) by calling SET SESSION AUTHORIZATION; and once there, the only way to revert to the original session authorization is to exit the execution context -- and once that happens, the attacker no longer has control. Since they have reduced privileges, they can't call SET SESSION AUTHORIZATION themselves to elevate their access. In this model, you're automatically protected. I mentioned this in some developer meeting; got blank stares back, IIRC. I mentioned it to Stephen in hallway track, and as I recall he was in agreement with what I was proposing. Biggest problem is, I can't recall in detail what it was. Not sure this helps you any ... Chilean $2 which are probably not worth much [currently], I guess. -- Álvaro Herrerahttp://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
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Tue, May 12, 2015 at 9:10 AM, Stephen Frost sfr...@snowman.net wrote: ... but, personally, I'd favor trying to do something at the protocol level instead. Me, too. -- 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
[HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
Hi all For some time I've wanted a way to SET SESSION AUTHORISATION or SET ROLE in a way that cannot simply be RESET, so that a connection may be handed to a less-trusted service or application to do some work with. This is most useful for connection pools, where it's currently necessary to have a per-user pool, to trust users not to do anything naughty, or to filter the functions and commands they can run through a whitelist to stop them trying to escalate rights to the pooler user. In the short term I'd like to: * Add a WITH COOKIE option to SET SESSION AUTHORIZATION, which takes an app-generated code (much like PREPARE TRANSACTION does). * Make DISCARD ALL, RESET SESSION AUTHORIZATION, etc, also take WITH COOKIE. If session authorization was originally set with a cookie value, the same cookie value must be passed or an ERROR will be raised when RESET is attempted. * A second SET SESSION AUTHORIZATION without a prior RESET would be rejected with an ERROR if the first SET used a cookie value. This can be done without protocol-level changes and with no backward compatibility impact to existing applications. Any objections? These commands will appear in the logs if log_statement = 'all', but the codes are transient cookie values, not passwords. They'll be visible in pg_stat_activity but only to the privileged user. It'll probably be necessary to clear the last command string when executing SET SESSION AUTHORIZATION so the new user can't snoop the cookie value from a concurrent connection, but that should be simple enough. As is currently the case, poolers will still have to use a superuser connection if they want to pool across users. In the longer term I want to add a protocol-level equivalent that lets a session switch session authorization or role, for efficiency and log-spam reasons. I'm also interested in a way to allow SET SESSION AUTHORIZATION to a list of permitted roles when run as a non-superuser, for connection pool use. SET ROLE might do, but it's more visible to apps, wheras SET SESSION AUTHORIZATION really makes the connection appear to become the target user. That's later though - first, -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
