Re: Extending SET SESSION AUTHORIZATION - Mailing list pgsql-hackers

"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