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

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