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?

One nice side benefit to this would be that you could effectively connect as
many users with Apache::DBI under mod perl without having an open connection
for every user.

Chris


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Chris Ochs wrote:[color=blue]
>
> 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?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Chris Ochs wrote:[color=blue]
>
> 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?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Bruce Momjian <pgman@candle.pha.pa.us> wrote:
[color=blue]
> Chris Ochs wrote:[color=green]
> >
> > 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?[/color]
>
> Well, the password would go over the wire unencrypted, causing a
> security problem.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Bruce Momjian <pgman@candle.pha.pa.us> wrote:
[color=blue]
> Chris Ochs wrote:[color=green]
> >
> > 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?[/color]
>
> Well, the password would go over the wire unencrypted, causing a
> security problem.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Bill Moran wrote:[color=blue]
> Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>[color=green]
> > Chris Ochs wrote:[color=darkred]
> > >
> > > 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?[/color]
> >
> > Well, the password would go over the wire unencrypted, causing a
> > security problem.[/color]
>
> 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 ...[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Bill Moran wrote:[color=blue]
> Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>[color=green]
> > Chris Ochs wrote:[color=darkred]
> > >
> > > 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?[/color]
> >
> > Well, the password would go over the wire unencrypted, causing a
> > security problem.[/color]
>
> 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 ...[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

[color=blue]
> Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>[color=green]
> > Chris Ochs wrote:[color=darkred]
> > >
> > > What if SET SESSION AUTHORIZATION could also accept a password so that[/color][/color][/color]
non[color=blue][color=green][color=darkred]
> > > superusers could switch to a different user? How difficult would this[/color][/color][/color]
be?[color=blue][color=green]
> >
> > Well, the password would go over the wire unencrypted, causing a
> > security problem.[/color]
>
> Only if encrypted transport is not enabled. With encrypted transport, it[/color]
would[color=blue]
> be as secure as anything else, right?
>
> Perhaps, it could only be available if transmission encryption is enabled?[/color]
Then[color=blue]
> again, there's a certain amount of "only the user can shoot his own foot"[/color]
that[color=blue]
> has to be accepted ...
>
> Just thinking out loud ...
>
> --
> Bill Moran
> Potential Technologies
> http://www.potentialtech.com
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

[color=blue]
> Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>[color=green]
> > Chris Ochs wrote:[color=darkred]
> > >
> > > What if SET SESSION AUTHORIZATION could also accept a password so that[/color][/color][/color]
non[color=blue][color=green][color=darkred]
> > > superusers could switch to a different user? How difficult would this[/color][/color][/color]
be?[color=blue][color=green]
> >
> > Well, the password would go over the wire unencrypted, causing a
> > security problem.[/color]
>
> Only if encrypted transport is not enabled. With encrypted transport, it[/color]
would[color=blue]
> be as secure as anything else, right?
>
> Perhaps, it could only be available if transmission encryption is enabled?[/color]
Then[color=blue]
> again, there's a certain amount of "only the user can shoot his own foot"[/color]
that[color=blue]
> has to be accepted ...
>
> Just thinking out loud ...
>
> --
> Bill Moran
> Potential Technologies
> http://www.potentialtech.com
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Bruce Momjian <pgman@candle.pha.pa.us> wrote:[color=blue]
> Bill Moran wrote:[color=green]
> > Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> >[color=darkred]
> > > 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.[/color]
> >
> > 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 ...[/color]
>
> Yes, if you use SSH it is secure, but do we want clauses that are only
> useful in SSH mode?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Bruce Momjian <pgman@candle.pha.pa.us> wrote:[color=blue]
> Bill Moran wrote:[color=green]
> > Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> >[color=darkred]
> > > 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.[/color]
> >
> > 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 ...[/color]
>
> Yes, if you use SSH it is secure, but do we want clauses that are only
> useful in SSH mode?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Bill Moran wrote:[color=blue]
> 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)[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Bill Moran wrote:[color=blue]
> 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)[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Tue, Jun 15, 2004 at 11:43:08AM -0400, Bill Moran wrote:[color=blue]
> Bruce Momjian <pgman@candle.pha.pa.us> wrote:[color=green]
> > Bill Moran wrote:[color=darkred]
> > > 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?[/color][/color]
>
> 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)[/color]

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[color=blue]
> GRANT SESSION TO user2[/color]
which would allow user2 to switch to user1


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

e.g.[color=blue]
> GRANT SESSION SELECT ON DATABASE foo TO user2
> GRANT SESSION UPDATE ON TABLE bar TO user2[/color]

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


-Mark

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Tue, Jun 15, 2004 at 11:43:08AM -0400, Bill Moran wrote:[color=blue]
> Bruce Momjian <pgman@candle.pha.pa.us> wrote:[color=green]
> > Bill Moran wrote:[color=darkred]
> > > 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?[/color][/color]
>
> 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)[/color]

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[color=blue]
> GRANT SESSION TO user2[/color]
which would allow user2 to switch to user1


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

e.g.[color=blue]
> GRANT SESSION SELECT ON DATABASE foo TO user2
> GRANT SESSION UPDATE ON TABLE bar TO user2[/color]

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


-Mark

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings