Обсуждение: Roles with passwords; SET ROLE ... WITH PASSWORD ?
At present, a role may have a password but unless it's a login role the docs say that password doesn't get used. I currently have an app where it'd be handy to be able to: SET ROLE rolename WITH PASSWORD 'blah'; to switch to role `rolename' only if the password `blah' is correct for that role. `rolename' might or might not be a login role, though in most of the cases I'm looking at it'd make more sense for it to be a login role. Why would this be useful? The app relies on PostgreSQL for basic user management and authentication. User rights are controlled by role assignments, and are enforced at the database level by appropriate table, column and function permissions plus selective use of SECURITY DEFINER functions and triggers. The app only uses role memberships to decide what UI to hide to avoid confusing the user with permission errors from the DB. This works extremely well, _except_ that occasionally it's desirable to override a user's rights by intervention of a supervisor user. Having a way to do this by switching to a role by providing a password to confirm access would be nice, especially if the user didn't have to be a member of that role already. Right now I'm having to spawn a new connection with the supplied supervisor username & password, then do the work in that connection. This works OK, but: (a) The switch can't be done mid-transaction so that priveleges are held for the minimum time possible. I'm looking at using `SET ROLE' to drop down to lower rights in the supervisor connection instead, but this could be clumsy when the main user has several roles significant for the operation(s) being performed. (b) The new connection doesn't hold the advisory locks the first connection had, which is occasionally problematic. (c) It's a PITA when working through an ORM like Hibernate, whereas a `SET ROLE' would be trivial and convenient. (d) I'd rather not spawn the extra backend, though I guess it doesn't matter much with an event of this rarity. Anyway ... I'm curious about whether `SET ROLE rolename WITH PASSWORD' is something that's technically practical to implement in PostgreSQL and what people think about the idea. I don't see any obvious, gaping security issues with doing this, since anybody who can 'SET ROLE ... WITH PASSWORD ...' should be able to log in with those credentials too. However, there may be issues interacting with external auth systems like ldap or kerberos. Thoughts? Also: I'm currently thinking of writing a `SECURITY DEFINER' function that tests a supplied password against a named role by direct access to pg_shadow, and if the password matches invokes SET ROLE with that role. Crazy? -- Craig Ringer
Craig Ringer <craig@postnewspapers.com.au> writes: > Anyway ... I'm curious about whether `SET ROLE rolename WITH PASSWORD' > is something that's technically practical to implement in PostgreSQL and > what people think about the idea. Seems like it would have all the standard problems with cleartext passwords being exposed in pg_stat_activity, system logs, etc. Also, what about people who are using more-secure-than-password auth methods, like Kerberos? I'm not really for it. regards, tom lane
One other topic that is related to this is that we now have a expire date but it would be nice to have a number of days also. This would make it easy to force the user to change their passwords every X days if internal security is being used instead of something like Kerberos or LDAP. Best Regards Michael Gould >Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Craig Ringer <craig@postnewspapers.com.au> writes: >> Anyway ... I'm curious about whether `SET ROLE rolename WITH PASSWORD' >> is something that's technically practical to implement in PostgreSQL and >> what people think about the idea. > > Seems like it would have all the standard problems with cleartext > passwords being exposed in pg_stat_activity, system logs, etc. > Also, what about people who are using more-secure-than-password > auth methods, like Kerberos? > > I'm not really for it. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Michael Gould <mgould@intermodalsoftwaresolutions.net> writes: > One other topic that is related to this is that we now have a expire date > but it would be nice to have a number of days also. This would make it easy > to force the user to change their passwords every X days if internal > security is being used instead of something like Kerberos or LDAP. There's already a solution to that in CVS HEAD: you can add a plug-in module to enforce password policy, including a limit on how far away the expiration date is. regards, tom lane
On 2/12/2009 11:04 PM, Tom Lane wrote: > Craig Ringer<craig@postnewspapers.com.au> writes: >> Anyway ... I'm curious about whether `SET ROLE rolename WITH PASSWORD' >> is something that's technically practical to implement in PostgreSQL and >> what people think about the idea. > > Seems like it would have all the standard problems with cleartext > passwords being exposed in pg_stat_activity, system logs, etc. Yeah, I was a bit concerned about that, but it can be worked around with careful use of parameterised queries (depending, admittedly, on client library/driver). It's still not pretty. And, of course, you'd have to run over SSL or some other encrypted channel to make it even faintly OK. > Also, what about people who are using more-secure-than-password > auth methods, like Kerberos? That's more what I was worried about. I thought it might be useful anwyay, though, as it seems that a *lot* of people use Pg's built-in user management. Between the two, though, as I look at it more I'm inclined to agree that it's probably not worth it. Doing it right would need protocol-level support for re-running authentication, which I imagine would be major server- and client-surgery as well as a protocol version change ... hardly worth it for a niche capability. I'll probably achieve roughly the same thing for my particular needs with a function that does a manual lookup in pg_shadow. It's ugly, but should do what I need. I can hide the password in a query parameter, so long as I make sure the params aren't interpolated on the client end. I force the use of SSL to permit users to log in at all, so network sniffing shouldn't be an issue. It's frustrating that there's no way to change priveleges on the fly (without a new connection and potential locking issues) ... but it's not the end of the world. If the above approach doesn't work I can always go back to spawning new connections and living with the issues. Thanks for looking at the notion, though - I thought it *might* be worth a glance. -- Craig Ringer
Craig Ringer <craig@postnewspapers.com.au> writes: > On 2/12/2009 11:04 PM, Tom Lane wrote: >> Seems like it would have all the standard problems with cleartext >> passwords being exposed in pg_stat_activity, system logs, etc. > Yeah, I was a bit concerned about that, but it can be worked around with > careful use of parameterised queries (depending, admittedly, on client > library/driver). No, not really, because we don't support parameters in utility commands. Even if we did, parameter values get logged, so the leak to the postmaster log is still there. regards, tom lane