Ezra Epstein wrote:
> 
> I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a
> password.  Looking at the source it seems, other than changes to the parser,
> there are only 2 relevant functions in 2 files that would be affected.  Each
> function is quite small and its function is clear.
> 
> I did not find this functionality on the current to-do list:
> 	http://developer.postgresql.org/todo.php
> And I'm quite new to the PG backend.  I don't want to code something up that
> is unwelcome by the developers.  On the other hand, if appropriate/accepted,
> I'd be glad to write this little addition to the current functionality.

[ CC to hackers added.]

Uh, a password?  What purpose would that serve?  Isn't that something
you control when attaching to the database?  Is this for prompting for
a username password?  The problem is that the SQL query passing isn't
secure like the way we send passwords using libpq, so I don't think this
would be secure or wise to hardcode a password in the SQL.

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

Mon, 26 Jan 2004 21:42:14 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Extending SET SESSION AUTHORIZATION
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Ezra Epstein wrote:
>> I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a
>> password.

> Uh, a password?  What purpose would that serve?

Indeed.  SET SESSION AUTH is already allowed only to superusers --- a
superuser hardly needs any additional privileges to become whoever he
wants.

			regards, tom lane

Mon, 26 Jan 2004 22:56:24 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Extending SET SESSION AUTHORIZATION 
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Monday, January 26, 2004 7:56 PM
> To: Bruce Momjian
> Cc: eepstein@prajnait.com; PostgreSQL-development
> Subject: Re: [HACKERS] Extending SET SESSION AUTHORIZATION
>
>
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Ezra Epstein wrote:
> >> I'd like to extend SET SESSION AUTHORIZATION to support a form
> which takes a
> >> password.
>
> > Uh, a password?  What purpose would that serve?
>
> Indeed.  SET SESSION AUTH is already allowed only to superusers --- a
> superuser hardly needs any additional privileges to become whoever he
> wants.
>
> 			regards, tom lane
>

For exactly the opposite usage: allowing a non-privileged user to take on a
different authorization IFF a password is also supplied.  This allows a user
to use an existing connection (so, for example, connection pooling works)
and not require a high priv'd account to then act as a specific (and
specifically priv'd) user of the system.

E.g., I could then have a user who has only connection privs for the DB and
then use a SET SESSION AUTH as a means of "logging in" as a specific user.
What this buys me:
     Connection pooling (critical for volume web apps)
     Postgres (DB) level enforcement of privileges via GRANT and REVOKE : so
that my priv scheme is consistent across db access methods and I don't have
to be too concerned about replicating the authorization logic out in the app
layer.

== Ezra Epstein.



Mon, 26 Jan 2004 22:00:17 -0800
"Ezra Epstein" <eepstein@prajnait.com>
Re: Extending SET SESSION AUTHORIZATION 
Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
>>Ezra Epstein wrote:
>>
>>>I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a
>>>password.
> 
> 
>>Uh, a password?  What purpose would that serve?
> 
> 
> Indeed.  SET SESSION AUTH is already allowed only to superusers --- a
> superuser hardly needs any additional privileges to become whoever he
> wants.

It is very helpful for connection pooling/persistent connections. Say I have 10 
connections opened as superuser. I can switch the connection authorization per 
query and let database enforce the rules and access control.

For authentication, I can keep a dummy connection.

There could be multiple ways to improve this behaviour.

1. If a non super-user attempts set session authorization, let him do so with 
proper password.

2. Add password to set session authorization as suggested above.

I would prefer this actually. In case the application is breached, with option 
2, the database is left wide open. With option 1, that may not be the case if 
initial connection is with a sufficiently unprivilaged user. But then I need to 
cache the actual password, which is another can of worms..:-(

Additionally it would be great if libpq could just authenticate a user without 
forking a backend. I think some kind of PAM voodoo can be substituted for that 
but having a libpq frontend is great.

I did suggest this earlier as well. Just reiterating..

  Shridhar


Tue, 27 Jan 2004 12:40:51 +0530
Shridhar Daithankar <shridhar@frodo.hserus.net>
Re: Extending SET SESSION AUTHORIZATION
"Ezra Epstein" <eepstein@prajnait.com> writes:
>>> I'd like to extend SET SESSION AUTHORIZATION to support a form
>>> which takes a password.
>> 
> Uh, a password?  What purpose would that serve?

> For exactly the opposite usage: allowing a non-privileged user to take on a
> different authorization IFF a password is also supplied.  This allows a user
> to use an existing connection (so, for example, connection pooling works)
> and not require a high priv'd account to then act as a specific (and
> specifically priv'd) user of the system.

I do not think SET SESSION AUTH is a suitable replacement for logging
in.  For one thing, it doesn't apply per-user GUC settings.  For
another, using it this way in a pooling environment would be completely
insecure --- what if you forget to "log out", or your attempt to do so
is dropped because it was inside a failed transaction block?

Another objection to doing things this way is that it would just about
force people to embed passwords into their SQL scripts, creating another
serious source of insecurity.

			regards, tom lane

Tue, 27 Jan 2004 13:46:06 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Extending SET SESSION AUTHORIZATION 
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, January 27, 2004 10:46 AM
> To: eepstein@prajnait.com
> Cc: Bruce Momjian; PostgreSQL-development
> Subject: Re: [HACKERS] Extending SET SESSION AUTHORIZATION
>
>
> "Ezra Epstein" <eepstein@prajnait.com> writes:
> >>> I'd like to extend SET SESSION AUTHORIZATION to support a form
> >>> which takes a password.
> >>
> > Uh, a password?  What purpose would that serve?
>
> > For exactly the opposite usage: allowing a non-privileged user
> to take on a
> > different authorization IFF a password is also supplied.  This
> allows a user
> > to use an existing connection (so, for example, connection
> pooling works)
> > and not require a high priv'd account to then act as a specific (and
> > specifically priv'd) user of the system.
>
> I do not think SET SESSION AUTH is a suitable replacement for logging
> in.  For one thing, it doesn't apply per-user GUC settings.  For

OK, what are GUC settings.  Can SET SESSION AUTH be extended to do this as
needed?

> another, using it this way in a pooling environment would be completely
> insecure --- what if you forget to "log out", or your attempt to do so
> is dropped because it was inside a failed transaction block?

Well, consider the alternative.  A web user logs in to the web app, not to
the DB.  The web app connects to the DB as a user which has the union of ALL
privs of each of the web users!  This is the default mode of ALL production
web apps.  In other words, the alternative is an even bigger security hole

Also, in web apps you get to do post-response clean-up.  I'd put the RESET
SESSION AUTH code there -- all by itself, outside of any transaction.  So,
on 2 counts I would say the approach I would like to take will result in a
more secure application overall.

> Another objection to doing things this way is that it would just about
> force people to embed passwords into their SQL scripts, creating another
> serious source of insecurity.
>

Au contraire!  Go do a security audit of most production web system.  While
the password might not be in SQL it is usually in a config file.  E.g., in
the server.xml file for a J2EE servlet container as part of the declaration
of the jdbc DataSource.  And the user is highly priv'd  (union of all privs
for every user of the application).  So what I'd like is a default user that
has NO privs.  The user logs in, but the credentials are not validated
against an internal application-specific (or LDAP/Identity-server provided)
authentication but against the database's authentication itself!  (Then I'd
add password synchronization for an enterprise client to keep their
Directory servers and the DB aligned.)  In other words: no password or user
login is stored at all.  It is provided by the user during log in!  A much
better and much *more* secure approach.

== Ezra Epstein






Tue, 27 Jan 2004 13:06:47 -0800
"Ezra Epstein" <eepstein@prajnait.com>
Re: Extending SET SESSION AUTHORIZATION 
"Ezra Epstein" <eepstein@prajnait.com> writes:
>> I do not think SET SESSION AUTH is a suitable replacement for logging
>> in.  For one thing, it doesn't apply per-user GUC settings.  For

> OK, what are GUC settings.  Can SET SESSION AUTH be extended to do this as
> needed?

Not very easily; it's not clear to me how you undo the previous settings
taken from the other user, nor how you go back at RESET SESSION AUTH.
(It's not so much that you don't know what settings are specified in
pg_shadow, as that you don't know what might have been adopted if they'd
not been there.)  I am also concerned about whether layering such
semantics onto SET SESSION AUTH wouldn't break its existing uses.

Maybe you could declare by fiat that you don't care and users in this
sort of environment don't get to have per-user GUC settings.  If they
are sharing a webapp front end then maybe they don't need 'em.  I dunno
how important it really is, but we'd have to think about the implications.

>> another, using it this way in a pooling environment would be completely
>> insecure --- what if you forget to "log out", or your attempt to do so
>> is dropped because it was inside a failed transaction block?

> Well, consider the alternative.  A web user logs in to the web app, not to
> the DB.  The web app connects to the DB as a user which has the union of ALL
> privs of each of the web users!  This is the default mode of ALL production
> web apps.  In other words, the alternative is an even bigger security hole

No, the alternative is that the web app is responsible for managing
security, which I think is the only reasonable place to put the
responsibility if you intend to use shared connections.  I find it
simply illusory to think that a shared-connection setup is going to be
secure if you don't have complete confidence in the front end.
Basically what you're saying is that you're willing to trust the front
end to ensure that user A can never do anything over user B's
connection, but you're not willing to trust it to enforce security
otherwise.  That doesn't seem to hold water to me.

Another issue with a SET SESSION AUTH extension of this kind is that it
would force every multi-user installation to maintain password security
whether they want it or not.  In an environment where users do not
normally use database passwords (perhaps they use IDENT auth instead)
it's entirely likely that they'd not bother to select good passwords or
guard them.  In that case the option to get into someone else's account
via SET SESSION AUTH becomes a security hole that people are unlikely to
think to plug --- the old "out of sight, out of mind" problem.

			regards, tom lane

Tue, 27 Jan 2004 16:35:21 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Extending SET SESSION AUTHORIZATION 
 -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Tuesday, January 27, 2004 1:35 PM
>
> "Ezra Epstein" <eepstein@prajnait.com> writes:
> >> I do not think SET SESSION AUTH is a suitable replacement for logging
> >> in.  For one thing, it doesn't apply per-user GUC settings.  For
>
> > OK, what are GUC settings.  Can SET SESSION AUTH be extended to
> do this as
> > needed?
>
> Not very easily; it's not clear to me how you undo the previous settings
> taken from the other user, nor how you go back at RESET SESSION AUTH.
> (It's not so much that you don't know what settings are specified in
> pg_shadow, as that you don't know what might have been adopted if they'd
> not been there.)  I am also concerned about whether layering such
> semantics onto SET SESSION AUTH wouldn't break its existing uses.
>
> Maybe you could declare by fiat that you don't care and users in this
> sort of environment don't get to have per-user GUC settings.  If they
> are sharing a webapp front end then maybe they don't need 'em.  I dunno
> how important it really is, but we'd have to think about the implications.
>

Since I still don't know what "GUC" even stands for, I'll just take the
entirely naive approach and assume it doesn't matter for these purposes.

> >> another, using it this way in a pooling environment would be completely
> >> insecure --- what if you forget to "log out", or your attempt to do so
> >> is dropped because it was inside a failed transaction block?
>
> > Well, consider the alternative.  A web user logs in to the web
> app, not to
> > the DB.  The web app connects to the DB as a user which has the
> union of ALL
> > privs of each of the web users!  This is the default mode of
> ALL production
> > web apps.  In other words, the alternative is an even bigger
> security hole
>
> No, the alternative is that the web app is responsible for managing
> security, which I think is the only reasonable place to put the
> responsibility if you intend to use shared connections.  I find it

Yes and if you've already had the DBA configure the DB to have group-based
security at the rather fine level of granularity  that SQL gives, why not
have a means of leveraging that -- and thereby simplifying the applications,
oh and getting security consistency across all such apps free of charge --
rather than replicate it all in a different tier?

> simply illusory to think that a shared-connection setup is going to be
> secure if you don't have complete confidence in the front end.
> Basically what you're saying is that you're willing to trust the front
> end to ensure that user A can never do anything over user B's
> connection, but you're not willing to trust it to enforce security
> otherwise.  That doesn't seem to hold water to me.
>
> Another issue with a SET SESSION AUTH extension of this kind is that it
> would force every multi-user installation to maintain password security
> whether they want it or not.  In an environment where users do not
> normally use database passwords (perhaps they use IDENT auth instead)
> it's entirely likely that they'd not bother to select good passwords or
> guard them.  In that case the option to get into someone else's account
> via SET SESSION AUTH becomes a security hole that people are unlikely to
> think to plug --- the old "out of sight, out of mind" problem.

This last one is the only concern raised that I can see being one I'd worry
over.  It makes me think that enabling the alternate mode of SET SESSION
AUTH could itself be subject to a DB parameter (settable at startup or via
the SET mechanism) and turned off by default.

== Ezra Epstein


Wed, 28 Jan 2004 14:27:37 -0800
"Ezra Epstein" <eepstein@prajnait.com>
Re: Extending SET SESSION AUTHORIZATION