Re: [ADMIN] Permission select pg_stat_replication

Denish, all,

Moved over to -hackers to discuss specifics around addressing this.

* Denish Patel (denish(at)omniti(dot)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