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