Thread: Re: Extending SET SESSION AUTHORIZATION

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

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

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

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