Re: Extending SET SESSION AUTHORIZATION

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Tuesday, January 27, 2004 10:46 AM
> To: eepstein(at)prajnait(dot)com
> Cc: Bruce Momjian; PostgreSQL-development
> Subject: Re: [HACKERS] Extending SET SESSION AUTHORIZATION
>
>
> "Ezra Epstein" <eepstein(at)prajnait(dot)com> writes:
> >>> I'd like to extend SET SESSION AUTHORIZATION to support a form
> >>> which takes a password.
> >>
> > Uh, a password? What purpose would that serve?
>
> > 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.
>
> 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?

> 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

Also, in web apps you get to do post-response clean-up. I'd put the RESET
SESSION AUTH code there -- all by itself, outside of any transaction. So,
on 2 counts I would say the approach I would like to take will result in a
more secure application overall.

> Another objection to doing things this way is that it would just about
> force people to embed passwords into their SQL scripts, creating another
> serious source of insecurity.
>

Au contraire! Go do a security audit of most production web system. While
the password might not be in SQL it is usually in a config file. E.g., in
the server.xml file for a J2EE servlet container as part of the declaration
of the jdbc DataSource. And the user is highly priv'd (union of all privs
for every user of the application). So what I'd like is a default user that
has NO privs. The user logs in, but the credentials are not validated
against an internal application-specific (or LDAP/Identity-server provided)
authentication but against the database's authentication itself! (Then I'd
add password synchronization for an enterprise client to keep their
Directory servers and the DB aligned.) In other words: no password or user
login is stored at all. It is provided by the user during log in! A much
better and much *more* secure approach.

== Ezra Epstein