Re: Extending SET SESSION AUTHORIZATION

Tom Lane wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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