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 (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

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

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 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

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:

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:
/messages/by-id/53E2D346.9030806@2ndquadrant.com,
specific issue here:
/messages/by-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 http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

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

* 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 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

=?windows-1252?Q?Jos=E9_Luis_Tall=F3n?= <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

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

On 05/17/2015 07:39 PM, Tom Lane wrote:

=?windows-1252?Q?Jos=E9_Luis_Tall=F3n?= <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.

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

On Sun, 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?= <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. +

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

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?= <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

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

On Mon, 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:

=?windows-1252?Q?Jos=E9_Luis_Tall=F3n?= <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

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

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

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

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

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

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

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:

=?windows-1252?Q?Jos=E9_Luis_Tall=F3n?= <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/
<http://www.2ndquadrant.com/&gt;
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

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

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.