> -----Original Message----- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 27, 2004 10:46 AM > To: [EMAIL PROTECTED] > Cc: Bruce Momjian; PostgreSQL-development > Subject: Re: [HACKERS] Extending SET SESSION AUTHORIZATION > > > "Ezra Epstein" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster