Re: [HACKERS] Extending SET SESSION AUTHORIZATION
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, January 26, 2004 7:56 PM To: Bruce Momjian Cc: [EMAIL PROTECTED]; PostgreSQL-development Subject: Re: [HACKERS] Extending SET SESSION AUTHORIZATION Bruce Momjian [EMAIL PROTECTED] writes: Ezra Epstein wrote: I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a password. Uh, a password? What purpose would that serve? Indeed. SET SESSION AUTH is already allowed only to superusers --- a superuser hardly needs any additional privileges to become whoever he wants. regards, tom lane 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. E.g., I could then have a user who has only connection privs for the DB and then use a SET SESSION AUTH as a means of logging in as a specific user. What this buys me: Connection pooling (critical for volume web apps) Postgres (DB) level enforcement of privileges via GRANT and REVOKE : so that my priv scheme is consistent across db access methods and I don't have to be too concerned about replicating the authorization logic out in the app layer. == Ezra Epstein. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Extending SET SESSION AUTHORIZATION
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Ezra Epstein wrote: I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a password. Uh, a password? What purpose would that serve? Indeed. SET SESSION AUTH is already allowed only to superusers --- a superuser hardly needs any additional privileges to become whoever he wants. It is very helpful for connection pooling/persistent connections. Say I have 10 connections opened as superuser. I can switch the connection authorization per query and let database enforce the rules and access control. For authentication, I can keep a dummy connection. There could be multiple ways to improve this behaviour. 1. If a non super-user attempts set session authorization, let him do so with proper password. 2. Add password to set session authorization as suggested above. I would prefer this actually. In case the application is breached, with option 2, the database is left wide open. With option 1, that may not be the case if initial connection is with a sufficiently unprivilaged user. But then I need to cache the actual password, which is another can of worms..:-( Additionally it would be great if libpq could just authenticate a user without forking a backend. I think some kind of PAM voodoo can be substituted for that but having a libpq frontend is great. I did suggest this earlier as well. Just reiterating.. Shridhar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Extending SET SESSION AUTHORIZATION
-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
Re: [HACKERS] Extending SET SESSION AUTHORIZATION
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: Tuesday, January 27, 2004 1:35 PM Ezra Epstein [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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 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? 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. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Extending SET SESSION AUTHORIZATION
Ezra Epstein [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Extending SET SESSION AUTHORIZATION
Ezra Epstein wrote: I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a password. Looking at the source it seems, other than changes to the parser, there are only 2 relevant functions in 2 files that would be affected. Each function is quite small and its function is clear. I did not find this functionality on the current to-do list: http://developer.postgresql.org/todo.php And I'm quite new to the PG backend. I don't want to code something up that is unwelcome by the developers. On the other hand, if appropriate/accepted, I'd be glad to write this little addition to the current functionality. [ CC to hackers added.] Uh, a password? What purpose would that serve? Isn't that something you control when attaching to the database? Is this for prompting for a username password? The problem is that the SQL query passing isn't secure like the way we send passwords using libpq, so I don't think this would be secure or wise to hardcode a password in the SQL. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Extending SET SESSION AUTHORIZATION
Bruce Momjian [EMAIL PROTECTED] writes: Ezra Epstein wrote: I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a password. Uh, a password? What purpose would that serve? Indeed. SET SESSION AUTH is already allowed only to superusers --- a superuser hardly needs any additional privileges to become whoever he wants. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
