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