Feature idea

Chris Ochs wrote:

What if SET SESSION AUTHORIZATION could also accept a password so that non
superusers could switch to a different user? How difficult would this be?

Well, the password would go over the wire unencrypted, causing a
security problem.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Chris Ochs wrote:

What if SET SESSION AUTHORIZATION could also accept a password so that non
superusers could switch to a different user? How difficult would this be?

Well, the password would go over the wire unencrypted, causing a
security problem.

Only if encrypted transport is not enabled. With encrypted transport, it would
be as secure as anything else, right?

Perhaps, it could only be available if transmission encryption is enabled? Then
again, there's a certain amount of "only the user can shoot his own foot" that
has to be accepted ...

Just thinking out loud ...

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

As much as I hate to say it, if it would be insecure when not using ssl,
this is a feature that people would definitely use insecurely and one day it
would be labeled as a 'security hole' in postgresql.

Chris
----- Original Message -----
From: "Bill Moran" <wmoran@potentialtech.com>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: <chris@paymentonline.com>; <pgsql-general@postgresql.org>
Sent: Tuesday, June 15, 2004 8:13 AM
Subject: Re: [GENERAL] Feature idea

Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Chris Ochs wrote:

What if SET SESSION AUTHORIZATION could also accept a password so that

non

superusers could switch to a different user? How difficult would this

be?

Well, the password would go over the wire unencrypted, causing a
security problem.

Only if encrypted transport is not enabled. With encrypted transport, it

would

be as secure as anything else, right?

Perhaps, it could only be available if transmission encryption is enabled?

Then

again, there's a certain amount of "only the user can shoot his own foot"

that

Bill Moran wrote:

Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Chris Ochs wrote:

What if SET SESSION AUTHORIZATION could also accept a password so that non
superusers could switch to a different user? How difficult would this be?

Well, the password would go over the wire unencrypted, causing a
security problem.

Only if encrypted transport is not enabled. With encrypted transport, it would
be as secure as anything else, right?

Perhaps, it could only be available if transmission encryption is enabled? Then
again, there's a certain amount of "only the user can shoot his own foot" that
has to be accepted ...

Just thinking out loud ...

Yes, if you use SSH it is secure, but do we want clauses that are only
useful in SSH mode?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Bill Moran wrote:

Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Chris Ochs wrote:

What if SET SESSION AUTHORIZATION could also accept a password so that non
superusers could switch to a different user? How difficult would this be?

Well, the password would go over the wire unencrypted, causing a
security problem.

Only if encrypted transport is not enabled. With encrypted transport, it would
be as secure as anything else, right?

Perhaps, it could only be available if transmission encryption is enabled? Then
again, there's a certain amount of "only the user can shoot his own foot" that
has to be accepted ...

Just thinking out loud ...

Yes, if you use SSH it is secure, but do we want clauses that are only
useful in SSH mode?

Not to start an argument, but you could reverse that logic and say "Do you want
to hurt the smart, ssl users by not including helpful functionality that could
be dangerous to uneducated non-ssl users?"

IMHO, it really depends on the design philosophy that PostgreSQL follows. I'm
familiar with the strong push for stability, and I approve. But I'm not as
sure I have a feel for what developers think about this kind of thing.

If you made it a compile-time option, or made it disabled by default and
requires a special setting in postgresql.conf to enable. Would that be secure?
Not really, as stupid users would still enable it without understanding, and
there's always the possibility that a some packager would build it with
dangerous settings and distribute it widely.

(As a side note, I seem to remember a program that had a --shoot-my-own-foot
option to ./configure ... but I can't remember what it was ...)

So, the question becomes one of design philosophy (at least, I'm basing this on
the concept that actual implementation would not be too hard, correct me if I'm
wrong)

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Bill Moran wrote:

Not to start an argument, but you could reverse that logic and say "Do you want
to hurt the smart, ssl users by not including helpful functionality that could
be dangerous to uneducated non-ssl users?"

IMHO, it really depends on the design philosophy that PostgreSQL follows. I'm
familiar with the strong push for stability, and I approve. But I'm not as
sure I have a feel for what developers think about this kind of thing.

If you made it a compile-time option, or made it disabled by default and
requires a special setting in postgresql.conf to enable. Would that be secure?
Not really, as stupid users would still enable it without understanding, and
there's always the possibility that a some packager would build it with
dangerous settings and distribute it widely.

(As a side note, I seem to remember a program that had a --shoot-my-own-foot
option to ./configure ... but I can't remember what it was ...)

So, the question becomes one of design philosophy (at least, I'm basing this on
the concept that actual implementation would not be too hard, correct me if I'm
wrong)

You are correct. The question is whether it is worth adding that level
of complexity into the system --- in the past, we have decided it isn't.
We have the $HOME/.pgpass file to store username/password combinations
that is probably best, though it works only with libpq-based interfaces.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Tue, Jun 15, 2004 at 11:43:08AM -0400, Bill Moran wrote:

Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Bill Moran wrote:

Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Chris Ochs wrote:

What if SET SESSION AUTHORIZATION could also accept a password so that non
superusers could switch to a different user? How difficult would this be?

Not to start an argument, but you could reverse that logic and say "Do you want
to hurt the smart, ssl users by not including helpful functionality that could
be dangerous to uneducated non-ssl users?"
...
So, the question becomes one of design philosophy
(at least, I'm basing this on the concept that actual implementation
would not be too hard, correct me if I'm wrong)

How about each user can set up a list of authorised users that
are allowed to switch to their username.

This would then follow the same model as authorized_keys/.rhosts in
ssh/rsh

user1 could then call something like

GRANT SESSION TO user2

which would allow user2 to switch to user1

Would it also be possible to restrict the grants when doing this?

e.g.

GRANT SESSION SELECT ON DATABASE foo TO user2
GRANT SESSION UPDATE ON TABLE bar TO user2

Which would allow updates to be made to table bar after the switch.

-Mark