Thread: RFC: Non-user-resettable SET SESSION AUTHORISATION
Hi all
* Add a WITH COOKIE option to "SET SESSION AUTHORIZATION", which takes an app-generated code (much like PREPARE TRANSACTION does).
--
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,
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 (craig@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
On Tue, May 12, 2015 at 9:10 AM, Stephen Frost <sfrost@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
On 12 May 2015 at 21:10, Stephen Frost <sfrost@snowman.net> wrote:
> I guess I'm a bit confused at why SET ROLE being visible to apps is a
> problem..?
> 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,
* Craig Ringer (craig@2ndquadrant.com) wrote:
> On 12 May 2015 at 21:10, Stephen Frost <sfrost@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 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 both. If it's really an issue that the session could detect that a
set role was used then perhaps we work out a way to deal with that.
Seems like it'd be easier to do than reinventing pg_authid independently
for set-session-authorization.
Thanks!
Stephen
On 13 May 2015 at 09:55, Stephen Frost <sfrost@snowman.net> wrote:
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.
Craig,
* Craig Ringer (craig@2ndquadrant.com) wrote:
> On 12 May 2015 at 21:10, Stephen Frost <sfrost@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.9030806@2ndquadrant.com, specific issue here: http://www.postgresql.org/message-id/25297.1407459774@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 both. If it's really an issue that the session could detect that a
set role was used then perhaps we work out a way to deal with that.
Seems like it'd be easier to do than reinventing pg_authid independently
for set-session-authorization.
Yeah, fair.
So ... a protocol level SET ROLE that can't be reset by SQL-level SET or RESET role then?
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 Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
* Alvaro Herrera (alvherre@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
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 roles&attributes 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
AUTHORIZATIONWITH 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.
José Luis Tallón <jltallon@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
On 05/17/2015 07:39 PM, Tom Lane wrote:
> José Luis Tallón <jltallon@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.
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: > >José Luis Tallón <jltallon@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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
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: > > >José Luis Tallón <jltallon@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 Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, May 18, 2015 at 12:33 PM, Alvaro Herrera <alvherre@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: >> > >José Luis Tallón <jltallon@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
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
On Tue, May 19, 2015 at 12:29 PM, Andres Freund <andres@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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2015-05-19 14:41:06 -0400, Robert Haas wrote: > On Tue, May 19, 2015 at 12:29 PM, Andres Freund <andres@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
On 18 May 2015 at 12:33, Alvaro Herrera <alvherre@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:
> > >José Luis Tallón <jltallon@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.
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.
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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 <andres@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
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.
On Tue, May 19, 2015 at 2:46 PM, Andres Freund <andres@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 <andres@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
On Tue, May 19, 2015 at 3:00 PM, Simon Riggs <simon@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
On 19 May 2015 at 16:49, Robert Haas <robertmhaas@gmail.com> wrote:
--
On Tue, May 19, 2015 at 3:00 PM, Simon Riggs <simon@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 Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
* Simon Riggs (simon@2ndQuadrant.com) wrote:
> On 19 May 2015 at 16:49, Robert Haas <robertmhaas@gmail.com> wrote:
>
> > On Tue, May 19, 2015 at 3:00 PM, Simon Riggs <simon@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
On Tue, May 19, 2015 at 5:02 PM, Simon Riggs <simon@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
On 5/20/15 5:21 PM, Robert Haas wrote: > On Tue, May 19, 2015 at 5:02 PM, Simon Riggs <simon@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
On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja <marko@joh.to> wrote: > On 5/20/15 5:21 PM, Robert Haas wrote: >> On Tue, May 19, 2015 at 5:02 PM, Simon Riggs <simon@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
Robert Haas wrote: > On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja <marko@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 Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Wed, May 20, 2015 at 3:42 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Robert Haas wrote: >> On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja <marko@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
On 5/20/15 3:31 PM, Robert Haas wrote: > On Wed, May 20, 2015 at 3:42 PM, Alvaro Herrera > <alvherre@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
On Wed, May 20, 2015 at 8:22 PM, Jim Nasby <Jim.Nasby@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
On 5/20/15 9:38 PM, Robert Haas wrote: > On Wed, May 20, 2015 at 8:22 PM, Jim Nasby <Jim.Nasby@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
On Tue, May 19, 2015 at 04:49:26PM -0400, Robert Haas wrote: > On Tue, May 19, 2015 at 3:00 PM, Simon Riggs <simon@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.
On Fri, May 22, 2015 at 2:11 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: >> 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. Such a mode might be useful, but again, it's a lot more complicated than the proposed protocol-level approach to change session authorization, and it's really solving a different problem. I still think we should do the simple thing first. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Sat, May 23, 2015 at 8:14 PM, Noah Misch <noah@leadboat.com> wrote: > On Tue, May 19, 2015 at 04:49:26PM -0400, Robert Haas wrote: >> On Tue, May 19, 2015 at 3:00 PM, Simon Riggs <simon@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. 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, May 26, 2015 at 10:06:59PM -0400, Robert Haas wrote: > On Sat, May 23, 2015 at 8:14 PM, Noah Misch <noah@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.