Thread: Re: Extending SET SESSION AUTHORIZATION

Re: Extending SET SESSION AUTHORIZATION

From
Tom Lane
Date:
"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


Re: Extending SET SESSION AUTHORIZATION

From
"Ezra Epstein"
Date:
 -----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