Denish, all, Moved over to -hackers to discuss specifics around addressing this.
* Denish Patel (den...@omniti.com) wrote: > Fair enough but they should be able to achieve their goal to avoid granting > SUPER to monitoring user. They have to tweak the grant/revoke as desired. That's correct, but the problem we currently have is that none of the monitoring systems will "just work" with this approach because they're hard-coded to what we actually provide by default. I'm hoping to fix that problem by having a C function which returns everything and then locking *that* down and only allowing it to be executed by a superuser by default. Administrators would then be able to grant access to those functions and the monitoring systems could be built on top of using those functions, and they'd work just fine if the monitoring user is a superuser but they'd also work if the monitoring user *isn't*, provided the correct GRANTs are done. What's getting tricky about all of this is making our existing views work against the C function but without depending on it to handle the filtering and instead doing it in SQL. That sounds simple until you look at the filtering we're actually doing and that functions defined in views run as the querier of the view, which means you have to have a security definer function involved to query the protected function underneath, and that function has to be callable by everyone, but it has to return values based on the permissions of the querier, which it doesn't know because we don't provide that information anywhere. I'm working on solving that problem by having a function which can return the value of "who called this function", a capability a *lot* of people have asked me about in the past, but that's pretty darn grotty given how GUCs work (we have "show_hook"s, but those operate against whatever the C variable is currently set to, and I really don't want to be playing with setting/resetting that just for this..). Rather than try to re-engineer how GUCs work, I'm looking at doing this specifically for this specific case of role information. I don't hear a lot of people asking for the value of other GUCs (except perhaps search_path, but that's easier since we don't have a show_hook for that..). Would certainly appreciate any thoughts from others on all of the above. Thanks! Stephen
signature.asc
Description: Digital signature